Excel Hack

Excel $ dollar sign shortcut key (F4)

In Excel, there is a useful shortcut key that allows you to lock referenced cells in formulas and functions. If you learn the shortcut keys introduced here, you will be able to switch between absolute, compound, and relative references with a single key. The shortcut keys presented here can also be used to unlock referenced cells. This shortcut is useful if you frequently copy formulas.

Shortcut to toggle fixed range of reference cells

Windows
F4

Mac
F4 or command + T

Fix rows of reference cells

This is a shortcut key to fix the row of a reference cell.

Total Time: 1 minute

Select a cell

Select a cell

Select the cell where you want to enter the formula.

Enter the cell to be referenced

Enter the cell to be referenced

Enter = and the cell to be referenced. Here, enter A4 as an example.

Fix rows of reference cells

Fix rows of reference cells

Press F4 twice to add a dollar sign to the row number. The dollar sign in front of the row number fixes the row to be referenced. Fixing reference cells in the row or column direction is called a compound reference.

Enter more reference cells

Enter more reference cells

+ (plus), then enter the cell to be referenced. Here, enter A5 as an example.

Fix rows of referenced cells

Fix rows of referenced cells

Press F4 twice to fix the row of the reference cell.

Copy cell

Copy cell

Press Ctrl + Enter to confirm, then press Ctrl + C to copy the cell.

Rows of copied formulas are fixed

Rows of copied formulas are fixed

Press (Down Arrow key) to select the cell in which to paste the copied formula, then press Enter to paste. The row of the reference cell is fixed, so the reference cell remains the same even if it is pasted into another cell on the same row.

Fix columns of reference cells

Select a cell

Select the cell where you want to enter the formula.

Enter the cell to be referenced

Enter = and the cell to be referenced. Here, enter A4 as an example.

Enter the cell to be referenced

Press F4 three times to add a dollar sign to the column number. This fixes the columns of the reference cell.

Enter more reference cells

+ (plus), then enter the cell to be referenced. Here, enter A5 as an example.

Fix columns of reference cells

Press F4 three times to fix the column of the reference cell.

Copy cell

Press Ctrl + Enter to confirm, then press Ctrl + C to copy the cell.

Reference cell columns are fixed

Press (Right Arrow key) to select the cell in which to paste the copied formula, then press Enter to paste.

Since the columns of the reference cells are fixed, copying formulas in the column direction does not change the reference cells.

Unfasten reference cells

Select cells with formulas entered

Select the cell in which the formula with the fixed reference cell is entered.

Press Shift + ←

Press F2 to put the cell in edit mode.

Press Shift + (Left Arrow key) several times to select the fixed reference cell.

Press F4 several times

Press F4 several times to make the dollar sign disappear and unfasten the reference cell. In this example, the absolute reference is removed by pressing F4 three times.

A condition in which the reference cell is not fixed is called a relative reference.

Articles related to F4

Comments

Please send us your comments on this article.

0 comments
Inline Feedbacks
View all comments