F4 and Absolute References in Google Sheets

The Two Jobs of F4

Excel trains you to use F4 for two unrelated things. While you are typing or editing a formula, it adds and cycles dollar signs on the reference under the cursor. When you are not editing, it repeats your last action. Google Sheets keeps the first job and drops the second. This guide covers how the reference cycling works in Sheets, where it differs from what you might expect, and how to get repeat-last-action back.

Relative, Absolute and Mixed References

A reference tells a formula which cell to read. The dollar signs decide what happens to that reference when you copy the formula somewhere else. The rules are identical in Excel and Google Sheets.

Form	Name	Copied Down	Copied Right	Typical Use
A1	Relative	Row changes	Column changes	Row-by-row math, e.g. revenue less costs in each year
$A$1	Absolute	Fixed	Fixed	A single assumption such as a tax rate
A$1	Mixed, row locked	Fixed	Column changes	A header row of years or growth rates
$A1	Mixed, column locked	Row changes	Fixed	A label or driver column on the left

The dollar sign is a lock on whichever part follows it. $A locks the column, $1 locks the row.

How To Cycle References With F4 in Google Sheets

1. Select the cell and start editing: type the formula, press F2, or double-click the cell.
2. Place the cursor inside or directly after the reference you want to change, for example in B2.
3. Press F4. The reference changes to $B$2.
4. Press again for B$2, again for $B2, and once more to return to B2.
5. Press Enter to confirm.

The order of the cycle is the same as Excel's, so your count of presses carries over. References to other tabs, such as Inputs!B2, cycle the same way; only the cell part gets the dollar signs.

On a Mac

Many Mac keyboards send brightness, volume or other functions from the top row by default. If F4 does nothing in Sheets, hold Fn and press F4, or change the keyboard setting in macOS so the top row acts as standard function keys.

Worked Example: A Growth Sensitivity Grid

Mixed references earn their keep in two-way tables. Say base-year revenue for each business line sits in B5:B9, and growth scenarios of 5%, 10% and 15% sit across C4:E4. You want each cell in C5:E9 to show next year's revenue for that line at that growth rate.

In C5, type =B5*(1+C4). Before confirming, put the cursor on B5 and press F4 three times to get $B5: the column is locked so every scenario reads base revenue from column B, while the row still moves as you fill down. Then move to C4 and press F4 twice for C$4: the row is locked so every line reads the growth rate from the header, while the column moves as you fill right. The finished formula is =$B5*(1+C$4). Fill it across and down and every cell is correct without editing a single one.

Getting this wrong is quiet. If you leave both references relative, the grid still fills with numbers; they are just reading the wrong cells. That is why it pays to check a corner cell after filling. Select it and press Ctrl + [ with ShortieCuts to jump to the exact cells it reads (see the trace guide), or switch on Show Formulas with Alt + M + H to scan the grid's formulas at once.

An Alternative to Dollar Signs: Named Ranges

For single assumptions used across a model, a named range can be clearer than $B$2. A name such as TaxRate points at a fixed cell, so it behaves like an absolute reference and reads better in a formula. With ShortieCuts, Alt + M + M + D starts defining a name and Alt + M + N opens the Google Sheets Named Ranges panel, the closest thing Sheets has to Excel's Name Manager.

Excel's Other F4: Repeat Last Action

Outside formula editing, Excel's F4 repeats whatever you just did. Insert a row, move down, press F4, and another row appears. Format a subtotal, move to the next subtotal, press F4. It is one of the most used keys in model building, and Google Sheets has no equivalent.

ShortieCuts restores it while leaving the reference cycling alone:

• When you are editing a cell, F4 behaves exactly as Google Sheets normally does and cycles dollar signs.
• When you are not editing, F4 repeats the last ShortieCuts command.

The second point matters: it repeats commands you ran through ShortieCuts, so the habit is to do the first one with the Excel key sequence. A few common patterns:

• Insert a row with Alt + H + I + R, then click lower down and press F4 for each additional row.
• Put a double underline under a total with Alt + H + B + B, then move to each other total and press F4.
• Apply a thick bottom border to section headers with Alt + H + B + H and repeat it down the page.
• Hide helper columns one block at a time with Alt + H + O + U + C, then F4 on the next block.

Tips for Models

• Lock inputs, not everything. Putting dollar signs on every reference makes formulas impossible to fill. Lock only the part that should not move.
• Keep assumptions in one place. Absolute references work best when they point to a clearly labelled inputs block or tab, not to a number buried in the middle of a calculation.
• Cut and paste keeps references. Moving a formula with cut and paste does not shift its references the way copying does, in Sheets as in Excel.
• Check after filling. One trace on the last cell of a filled range catches most mixed-reference mistakes.

For a deeper refresher on reference types, see what dollar signs do in Excel and dollar signs in Excel formulas. Every Excel sequence ShortieCuts supports is on the shortcuts index, and plans are on the pricing page.

FAQ