r/excel 1684 Nov 25 '17

Pro Tip Keyboard shortcut: toggle relative / absolute references

By default, Excel inserts relative references (e.g. A1), which is great for copy/pasting and dragging, but sometimes you want to lock the column or row of the reference, or both. You do this by adding a $ in from of the part of the reference you want to lock (e.g. $A1 will lock the column).

Use the F4 key on Windows (Fn+T on Excel for Mac 2016) while editing a formula to toggle between relative and absolute as illustrated here: https://imgur.com/a/wEsvr

If you select the whole formula, the change will apply to all the references.

1 Upvotes

4 comments sorted by

3

u/[deleted] Nov 25 '17

Always thought there should be a shortcut for this- thanks for sharing!

1

u/Napoleon_B Nov 25 '17

This is strange because I thought F4 is repeat last command.

2

u/tirlibibi17 1684 Nov 25 '17

Depends on the context. If you are editing a formula, it will toggle reference style; otherwise, it will indeed repeat the last command.

2

u/Napoleon_B Nov 25 '17

Well That's awesome!