Absolute and relative references
Use absolute and relative references to control whether your formulas stay the same or update when copied to other cells.
Absolute and relative references
How to quickly toggle between absolute, relative and mixed cell references using the keyboard.
Toggle between absolute, relative and mixed cell references using the keyboard
When in cell edit mode, you can use the F4 key to toggle cell references between absolute, relative and mixed references.
There are four toggle steps, like this:
Step | Toggle key shortcut | Cell reference type | Example |
---|---|---|---|
1 | None (default position) | Relative | =A1 |
2 | F4 | Absolute | =$A$1 |
3 | F4 | Mixed: – Absolute row – Relative column | =A$1 |
4 | F4 | Mixed: – Relative row – Absolute column | =$A1 |
1 | F4 | Return to default position (Relative) | =A1 |
You can keep on pressing the F4 key and the toggle cycle will just keep on going.
You must be in “formula edit mode” to use the F4 shortcut to toggle between absolute and relative references.
(If you press F4 while not in formula edit mode, it will do something else instead).
To enter “formula edit mode”, either:
press F2
or
double-click on the cell
or
click in the formula bar after selecting the cell.
What are absolute and relative cell references?
Let’s quickly recap what each of these are:
Relative referencing
Normally, when a cell contains a formula that refers to another cell (or range of cells), you enter it using relative referencing. For example:
=A1
If you copy the cell that contains this formula and paste it somewhere else, the reference will change and the new pasted cell will point to a different cell instead of cell A1.
For example:
Suppose the cell B3 contains the following formula:
=A1
If you copy that cell B3 and paste it at E7, the formula at E7 will instantly and automatically become:
=D5
Absolute referencing
With absolute referencing, the cell reference will not change when copied to another cell on the same worksheet.
Absolute references contain the symbol $
before both the column letter and row number, like this:
=$A$1
If you copy the cell that contains this formula and paste it somewhere else, the reference will not change and the new pasted cell will still point to A1.
For example:
Suppose the cell B3 contains the following formula:
=$A$1
If you copy that cell B3 and paste it at E7, the formula at E7 will instantly and automatically become:
=$A$1
Mixed relative and and absolute referencing
Sometimes, you need a combination of absolute and relative referencing.
This can either retain the row reference but change the column reference, or vice versa.
In these cases, the $ symbol appears before the part that will not change when copied.
For example:
Suppose the cell B3 contains the following formula:
=A$1
This a mixed reference, with an absolute row reference and a relative column reference.
If you copy that cell B3 and paste it at E7, the formula at E7 will instantly and automatically become:
=D$1
Quick ways to enter cell references
To quickly enter a cell reference, you can either type it into the cell directly, or click on the target cell when editing a cell’s formula.
You can also quickly enter a reference to a range of cells by dragging to select the range while editing a cell formula.
Indicating which reference to toggle
When in cell edit mode, the F4 shortcut will toggle between absolute and relative references for the cell reference that is at or immediately before the text cursor. It will also work if a range reference is highlighted in the formula bar, or immediately after selecting a cell or range.
For example:
Suppose you enter the following into the formula bar, then use the arrow keys or the mouse to position the cursor part-way through the first reference A2
:
=SUM(A2,B3,C5)
In this case, hitting F4 would toggle the A2
reference to absolute, like this:
=SUM($A$2,B3,C5)
Another example:
Suppose you enter =SUMIF(
into the formula bar, then drag the mouse to select the range A2:A7
:
=SUMIF(A2:A7
In this case, hitting F4 would toggle the whole range reference to absolute (not just the A2
part) like this:
=SUMIF($A$2:$A$7
Using a combination of absolute and relative references
If you have more than one cell reference within one formula, feel free to use any combination of absolute, relative and mixed reference styles that suits the situation.
For example
The following formula is a perfectly valid formula, which combines absolute, relative and mixed references:
=$B6+SUM($C$7:E$7)-F7
These examples use the popular “A1-style” notation, but these shortcuts also work if you are using “R1C1-style” notation.
Please log in to see the bonus tip.
Don’t have a login?
Get a login for free on our standard plan, and get a new tip each week.
All logins also get access to the bonus tip each week.
Get even more
Get even more with one of our reasonably priced paid plans, with access to features like:
- Quicksheet PDF summaries
- Example files
- VBA syntax
- Additional expert tips each week
- Advance publication
View our plans to see what else you are missing, or join for free on our Standard plan.
Please log in to see the expert tip.