The Repeat key and $ signs

Do more with F4

The F4 function key in Excel has a dual personality.

When used normally it will repeat your last action. This is useful for formatting, deleting rows and other operations that require a few keystrokes. Pressing F4 can reduce the keystrokes. F4 can also repeat running a macro.

Adding $ Signs

Its other use is to add $ signs to cell references when editing in the formula bar or within a cell.

Pressing F4 whilst editing in the formula bar (Excel displays Edit in the bottom left-hand corner of the screen) when the cursor is next to or in a cell reference will insert $ signs to the cell reference. Eg A1 turns into $A$1. This is called a fixed or absolute reference, which will not change when you copy the formula to other cells.

As you keep pressing F4 the position of the $ signs will change.

The sequence is $A$1, A$1, $A1 and back to A1.

The second and third in the sequence are mixed references as they fix the row and column respectively.

The normal A1 reference is a relative reference, it changes relative to the where you paste it.

You can remember the F4 key adds the $ signs by looking at the 4 key as it has the $ above it.

If you select more references at once pressing F4 will change them all.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.