Toggle absolute and relative references
Windows shortcut
F4
Mac shortcut
⌘T
While editing a formula, this shortcut toggles cell references from relative to absolute, to partially absolute, back to relative again:
A1 --> $A$1 --> A$1-- > $A1-- > A1
It's much faster and easier than typing $ characters manually.
To convert an existing formula, enter cell edit mode, place the cursor in or next to the reference you'd like to convert, then use the shortcut.
Note: in Excel 2016 for the Mac, you can also use fn + F4.
Related videos
In this video, we run through more than 20 tips you can use to save time with Excel formulas today. We believe in making videos that are quick and to the point, so this is a rapid-fire list. We trust that you can rewind the video as needed :)
Shortcuts are a great way to save time and energy in Excel, but there are more than 200, so the list can seem overwhelming. In this rapid fire video, we demonstrate more than 30 popular Excel shortcuts you can use to save time every day in Excel. We built this list of shortcuts based on a survey of over 800 Excel users.
One of the biggest mistakes you can make is to hard code assumptions into your formulas. Instead, if a value might change, put it in a cell and refer to it with a reference, as explained in this video.
A mixed reference is part relative, and part absolute. Mixed references are useful when you want only part of a cell reference to change when you copy a formula.
In this video, we use COUNTIF to compare lists and count values that belong to both lists. We also "flip" the formula to count values in one list that are missing on the other.
In this video, we'll look at a few ways to generate random values with the RANDBETWEEN function. You can use this approach to generate random numbers, random prices, random dates, and even random text values. This is a great way to create dummy data to test formulas and worksheets.
In this video we show how to highlight approximate match lookups with conditional formatting and the LOOKUP function. Highlighting approximate matches is tricky, because you must replicate the original approximate match in the CF rule.
In this video, we demo how to plot results from a survey question in an Excel bar chart. Along the way, we use the COUNTIF function to build a summary table, and show a simple technique for creating an "other" bucket.
In the video, we build one set of SUMIFS formulas with a table, and one set without. This makes it really easy to see the nice benefits you get when you combine tables and formulas.
In this video, we'll look at how you can use the CHOOSE function for simple lookups. The CHOOSE function doesn't need an external table, and can sometimes replace more complicated formulas based on VLOOKUP, INDEX and MATCH, or nested IFs.
VLOOKUP is a great way to merge data in two tables based on a common ID. This video shows you how in 3 minutes. You can even use this technique to reorder the columns you retrieve.
This video shows how to setup VLOOKUP with a table, and how to use the MATCH function inside VLOOKUP to generate a dynamic column index. This makes it easy to refer to columns by name inside VLOOKUP, instead of hardcoding the column number. As a bonus, you can reorder the columns and the formula will keep working correctly.
