While you type or edit a formula, put the cursor in a cell reference and press F4. Excel adds dollar signs, so E3 becomes $E$3 and stays fixed when you copy the formula. For example, =B2*$E$1 copied down a column keeps pointing at E1 while B2 moves to B3, B4 and so on.
Keep pressing F4 to cycle through the mixed references: the second press gives E$3 (row locked), the third gives $E3 (column locked), and the fourth returns to E3.
On a laptop where F4 changes the volume or brightness, press Fn + F4 instead, or turn on Fn Lock so the function keys work on their own (the key for this varies by laptop). On a Mac, Cmd + T toggles the reference.
With the cursor in a range such as A1:B5, F4 locks both ends at once to give $A$1:$B$5. To lock every reference in a formula, select the whole formula in the formula bar and press F4.
If F4 changes the cell’s formatting or repeats something else, you weren’t editing the formula. Outside edit mode, F4 repeats your last action. Press F2 or click in the formula bar first, then put the cursor in the reference and press F4.
Latest Video Tutorial – 10 Excel Keyboard Shortcuts (Probably YOU are NOT Using)
Keyboard Shortcut for Absolute Reference
The keyboard shortcut to change a relative cell reference to an absolute cell reference is below:
F4
Or
Fn + F4
Use Fn + F4 on laptops where the function keys control the volume or brightness. On a Mac, the shortcut is Command + T (or Fn + F4).
An absolute reference is a cell reference that doesn’t change when you copy the formula to another cell.
In Excel, whenever you use cell references in a formula and copy the formula to another cell, Excel adjusts the references to the new position. Those references are called relative references.
But sometimes you want to fix or lock a cell reference used in the formula, even when the formula gets copied to another cell. For this, you can change the relative reference to an absolute reference.
Steps to Convert Reference to Absolute Reference Using Keyboard Shortcut
With an absolute reference, you can lock the whole cell reference, or only its column or its row.
- First, enter the formula, and while you are still editing it, select the cell reference (or place the cursor in it) that you want to make absolute, and then press the Fn + F4 shortcut keys.
- Once you press Fn + F4, Excel will add the ($) Dollar sign before the column letter and the row number to make that selected cell reference (combination of row and column) absolute.
- Now, even if you copy the formula anywhere in the sheet, your “E3” cell reference, as per the below example, will not change at all.
Points to Remember
- To lock the whole cell reference (combination of row and column), like $E$3, press Fn + F4 only once.
- To lock only the row, like E$3 (the column changes when you copy the formula, but the row stays the same), press Fn + F4 twice.
- To lock only the column, like $E3 (the row changes when you copy the formula, but the column stays the same), press Fn + F4 three times.
- To go back to a relative reference, press Fn + F4 four times.
Example of an Absolute Reference
Let’s say you have prices in A2:A10 and a tax rate in cell E1. In B2, enter the following formula and copy it down to B10. As E1 is absolute, every row uses the same tax rate, while A2 changes to A3, A4 and so on:
=A2*$E$1
Without the dollar signs (=A2*E1), the formula in B3 would use E2, which is empty, and return 0.
Frequently Asked Questions
Why does F4 not add dollar signs?
F4 only changes references while you are editing a formula. If you are not in edit mode, F4 repeats your last action instead. Press F2 to edit the cell first, and on laptops, add the Fn key.
Can I make all references in a formula absolute at once?
Yes. While editing the formula, select the whole formula text and press F4. Excel changes every reference in the selection at the same time.
Do absolute references change when I cut and paste?
When you cut and paste (move) a formula, none of its references change, relative or absolute. Absolute references only matter when you copy a formula.