Skip to content
AnIntent

How to Lock a Cell Reference in Excel: F4, $ and Mixed References

Lock a cell in an Excel formula with $, toggle it with F4 or Cmd+T on a Mac, and know when to use $A$1, A$1 or $A1.

AnIntent Editorial · · 4 min read

Hands typing on a laptop displaying a data spreadsheet
Photo by Gorilla ROI Data Connector on Unsplash
In this article
  1. Why your formula breaks when you drag it
  2. Lock the reference with $
  3. Mixed references: lock only the row or only the column
  4. F4 not working? Check these first
  5. Which Excel versions this applies to
Share this article

To stop a formula from shifting its references when you fill it down or across, add $ signs to make an Excel absolute reference. You can type them or press F4 (Cmd+T on a Mac). This guide covers both methods, the mixed references A$1 and $A1, and why F4 sometimes seems to do nothing.

Why your formula breaks when you drag it

Excel references are relative by default. Microsoft's support page explains it this way: if a formula in C2 refers to A2, Excel reads that as "the cell two columns to the left in the same row." Copy the formula somewhere else and the reference moves with it.

Microsoft's example: copying this formula from D4 to D5

=B4*C4

gives you

=B5*C5

That is usually what you want. It goes wrong when one input should stay fixed, such as a single tax rate or a lookup range that every row should use. Fill the formula down and the reference to that fixed cell slides off it onto empty cells, and your results turn into zeros or errors.

Lock the reference with $

Put a $ before the column letter and before the row number. Per Microsoft, copying this from D4 to D5 leaves it exactly as it is:

=$B$4*$C$4

You only need to lock the part that should stay put. In a typical fill-down, the per-row value stays relative and only the fixed cell gets the $ signs.

Toggle it with F4 instead of typing

Microsoft's steps:

  1. Select the cell that contains the formula.
  2. In the formula bar, select the reference you want to change.
  3. Press F4 to switch between the reference types.

Each press moves the reference to the next type. Microsoft's page doesn't give the order. Exceljet says it starts A1 → $A$1 and cycles back to relative. A Mac-focused blog gives the full sequence as $A$1, A$1, $A1, A1. If you overshoot, keep pressing until you get back to the one you need.

The Mac shortcut is Cmd+T

Microsoft's page doesn't mention the Mac. Exceljet and Computerworld both list ⌘T on the Mac. Exceljet says earlier Mac versions accept only Command+T, while Fn+F4 also works in Mac Excel 365.

Mixed references: lock only the row or only the column

A mixed reference fixes one half. $B4 locks column B but lets the row change. C$4 locks row 4 but lets the column change. Microsoft says you'll use these "less often," and its table shows what each type does when a formula is copied two cells down and two cells to the right:

Reference What's locked After copying two down, two right
$A$1 column and row stays $A$1
A$1 row only becomes C$1
$A1 column only becomes $A3
A1 nothing becomes C3

Mixed references are what you need when one formula fills a grid in both directions. A multiplication table is the classic case. One input has its row headers down a column, so you lock that column ($A2 style). The other has its column headers across a row, so you lock that row (B$1 style). Then a single formula can be filled right and down. Check a couple of cells after filling to confirm each one points at the headers you expect.

F4 not working? Check these first

You're not editing the formula. F4 has two jobs. Microsoft's keyboard shortcuts page says F4 on its own repeats the last command or action. It only cycles reference types when a cell reference or range is selected in a formula. So if you click a cell and press F4, Excel repeats whatever you did last, such as a format, instead of adding $ signs. Click into the formula bar (or double-click the cell) and put the cursor in the reference first.

You expected it to fix every reference at once. It doesn't. Select one reference, press F4, then move to the next. You can also highlight a whole range reference such as a lookup range and toggle it in one go, since Microsoft says F4 works on a selected range.

Your laptop's F-keys do something else. On keyboards where the top row controls brightness or volume, you may need to hold Fn with F4. Maccove gives this advice for Macs. On a Mac you can skip the function key entirely and use Cmd+T.

You're on a Mac and want F4's other job. The same blog says Microsoft gives no Mac shortcut for repeating the last action, so don't expect Cmd+T to do double duty.

Which Excel versions this applies to

The $ syntax works the same in all of them, so typing the signs by hand is the fallback when a shortcut isn't available.

If you're locking a lookup range so it doesn't shift as you fill down, the same rule applies inside INDEX and MATCH formulas. And if "lock a cell" for you means stopping people from editing it, that's sheet protection, not $ signs. See how to lock only some cells in Excel.

Common questions

What is the absolute reference shortcut on a Mac?

Exceljet and Computerworld both list Cmd+T. Exceljet says Fn+F4 also works in Mac Excel 365, while earlier Mac versions accept only Command+T.

Why does F4 repeat my last action instead of adding $ signs?

Microsoft says F4 on its own repeats the last command or action. It only toggles reference types when a reference or range is selected inside a formula you're editing.

Can I make every reference in a formula absolute with one F4 press?

No. A Microsoft Q&A community answer says you can't select a cell and press F4 to lock all its references. Select each reference in the formula bar and toggle it separately.

What's the difference between A$1 and $A1?

A$1 locks the row and lets the column change. $A1 locks the column and lets the row change. In Microsoft's example, copying two cells down and two right turns A$1 into C$1 and $A1 into $A3.

black and silver laptop computer
How-to

How to Start a New Line in an Excel Cell (Windows, Mac and CHAR(10))

Add a line break inside one Excel cell with Alt+Enter on Windows, Control+Option+Return on Mac, or a CHAR(10) formula with Wrap Text on.

· 3 min

More in Cloud & Productivity Software