5

It is possible in Excel to create a cell reference using $ to prevent Excel from incrementing the row/column designator when the formula is copied. IE, if you fill a formula containing the reference $A$1 across a row, every pasted formula will remain referencing $A$1. However, if you do not manually add the $ prefix, the row/column designator will increment automatically.

When you enter formula entry mode and then click on another cell, that cell's reference will be insert at the cursor's position inside the formula.

Is there a modifier combo I can hold such that clicking on cell A1 will result in `$A$1' being entered into the formula, so I don't have to key in the $ after clicking?

Apologies for the circumlocution, I don't know the proper terms for these Excel features.

7

On Windows, you can press F4 after entering the cell coordinates to automatically toggle between relative and absolute: A1 → $A$1 → A$1 → $A1.

On OS X, the shortcut is Cmd-T.

  • I should've mentioned I'm on Excel for Mac. Your answer gave me enough info to find the equivalent Mac shortcut (cmd+T) though. Thank you! – Alterscape Jan 8 '12 at 20:50
  • +1 I can't believe this is the first I've heard of this feature. I feel like I've been living in a cave. – Excellll Jan 9 '12 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.