Excel Tips: Absolute References with the F4 Key

Lesson 14: Absolute References with the F4 Key

/en/excel-tips/use-transpose-to-flip-data-on-its-side/content/

Absolute references with the F4 key

An absolute reference is a cell reference that stays locked on a specific cell or cell range, even if the formula is copied. To learn more about them, click here.

You can change the cell reference to an absolute reference by adding dollar signs before the row and column (for example, $D$2).

Although you can type the dollar signs manually, the F4 key on your keyboard allows you to add both dollar signs with a single keystroke.

Watch the video below to learn how to use the F4 shortcut.

Steps

Open the Excel worksheet and start typing or editing a formula that uses a cell reference. Identify the cell reference that needs to be an absolute reference.
Instead of typing the dollar signs before the column letter and row number, press the F4 key. This will automatically add both dollar signs to the cell reference.
Press F4 again to cycle through different types of cell references if necessary. (This is useful if you want to lock the row or the column).
Finish typing or editing the formula as needed. Double-click the fill handle to copy the formula to the empty cells where needed.
Check the formula bar to ensure that the absolute reference is working properly in all copied formulas.

On some keyboards, the F4 key controls the computer's volume or screen brightness by default. In that case, you may have to hold down the Fn (Function) key before pressing F4.

If you create formulas frequently, this keyboard shortcut can definitely save you a lot of time. Next up, we’ll show you how to quickly add a new row to your spreadsheet.