F4 not adding $ signs? How to make absolute references
Causes and fixes
- Your laptop's F4 key does something else
- The formula isn't being edited
- You're using Excel for Mac
- F4 changes the reference, but not the way you want
- Type the $ signs yourself
1Your laptop's F4 key does something else
On many laptops the top keys control volume, brightness or the microphone, and the F1 to F12 functions need the Fn key.
Fix
Press Fn+F4. Many laptops also have an Fn Lock, often Fn+Esc, that swaps the two; the key varies by maker.
2The formula isn't being edited
F4 only adds $ signs while you're typing or editing a formula, with the cursor inside or right after the reference. Outside a formula, F4 repeats your last action instead.
Fix
Click the cell, press F2 (or click in the formula bar), put the cursor in A1, then press F4.
3You're using Excel for Mac
Fix
Press Command+T with the cursor in the reference. On some Macs Fn+F4 also works.
4F4 changes the reference, but not the way you want
Each press moves to the next type: $A$1 (both fixed), A$1 (row fixed), $A1 (column fixed), then back to A1.
Fix
Keep pressing until you get the one you need. To change several references at once, select them all in the formula bar first.
5Type the $ signs yourself
If F4 doesn't work in your browser or on your keyboard, type $ before the column letter, the row number, or both: =B2*$E$1.
Still stuck?
- Fixed a reference but copies still go wrong? Check which part needs the $: see relative, absolute and mixed references.
- In an Excel table, formulas use column names such as
[@Price], so there's nothing to fix with $.