How to Preserve Cell References When Copying a Formula in Excel (2026)

When you copy a formula in Excel, the cell references shift automatically, which is exactly what you want most of the time, until it isn’t. Adding a dollar sign ($) to a reference locks it in place so it never moves, no matter where you paste the formula.

Understanding Reference Types

Before locking anything, it helps to know what each reference type actually does when you copy a formula to another cell.

Reference	Example	What moves when copied
Relative	B2	Both row and column adjust
Absolute	$B$2	Nothing, always points to B2
Mixed (column locked)	$B2	Row adjusts, column stays fixed
Mixed (row locked)	B$2	Column adjusts, row stays fixed

A relative reference like B2 is fine when you want each row’s formula to pull from its own row. An absolute reference like $D$1 is what you need when every formula in a column should always point to the same cell: a tax rate, a bonus percentage, an exchange rate.

How to Lock a Cell Reference (F4 Method)

The fastest way to add dollar signs is to press F4 while editing a formula, with no typing required.

1. Click the cell where you want the formula.
2. Start typing your formula, for example, =B2*D1.
3. Before pressing Enter, click inside the reference you want to lock (D1 in this case) in the formula bar.
4. Press F4. Excel cycles through all four reference types:
   • D1 → $D$1 (fully absolute, locks both row and column)
   • $D$1 → D$1 (row locked only)
   • D$1 → $D1 (column locked only)
   • $D1 → D1 (back to relative)
5. Stop pressing F4 when you see the reference type you need.
6. Press Enter to confirm the formula.

On a Mac: Press Fn + F4, or try Cmd + T on newer Excel for Microsoft 365 builds. The cycle is identical; just the key combination differs.

In Excel for the web: F4 usually works in Chrome and Edge. If the browser intercepts it, type the dollar signs manually.

How to Copy a Formula and Keep the References Locked

Once your formula uses absolute references, copying it is straightforward.

Method 1: Fill Handle (fastest for adjacent cells)

1. Select the cell containing your locked formula.
2. Hover over the small square at the bottom-right corner of the cell until the cursor becomes a thin crosshair.
3. Click and drag down (or across) to fill the adjacent cells.

Every filled cell will use the same locked reference (e.g., $D$1) while any relative references adjust per row.

Method 2: Copy and Paste Formulas Only

Use this when you want to paste into non-adjacent cells or avoid copying the source cell’s formatting.

1. Select the cell with the formula and press Ctrl + C (Windows) or Cmd + C (Mac) to copy it.
2. Select the destination cell or range.
3. On the Home tab, click the arrow below the Paste button.
4. Select Formulas from the dropdown.

Alternatively, right-click the destination cell and choose Paste Special > Formulas. The formula pastes with its references intact, so no formatting bleeds over from the source cell.

How to Link to a Cell So Results Update Automatically

Copying a formula creates an independent copy, so if the original changes, the copy does not update. If you want the destination cell to always mirror the original cell’s result, use Paste Link instead.

1. Copy the original cell (Ctrl + C / Cmd + C).
2. Click the destination cell.
3. On the Home tab, click the arrow below Paste.
4. Select Paste Link (it looks like chain links in the Paste Options icons).

The destination cell’s formula bar will show an absolute reference to the original cell, like =$B$2. Any time the original cell updates, the linked cell updates too.

Note: If you only want to paste the current result, not the formula or a live link, right-click the destination and choose Paste Special > Values. The number lands in the cell with no formula attached.

Modern Alternative: Named Cells (Cleaner Than Dollar Signs)

Named ranges are the cleaner approach for constants you reference repeatedly. Instead of $D$1 scattered across dozens of formulas, you name the cell once and use the name everywhere.

1. Select the cell containing your constant (e.g., the tax rate in D1).
2. Click the Name Box, the field to the left of the formula bar that currently shows the cell address.
3. Type a descriptive name with no spaces (e.g., TaxRate or BonusPct) and press Enter.

4. In your formula, type the name instead of the cell address: =B2*TaxRate
5. Copy or fill down as usual. The name always resolves to D1, it behaves like a built-in absolute reference.

Named cells work across Excel for Microsoft 365, Excel 2021/2019/2016, and Excel for the web. On mobile, you can still use names in formulas; you just create them on the desktop version first.

Troubleshooting Common Issues

F4 doesn’t work on my Mac

Mac function keys often require Fn to activate. Press Fn + F4 while the cursor is inside the cell reference. If that still doesn’t work, try Cmd + T, which is mapped to the same toggle in recent Excel for Microsoft 365 builds on Mac. You can also go to System Settings > Keyboard and enable “Use F1, F2, etc. keys as standard function keys” so you don’t need Fn at all.

My formula shows as plain text instead of calculating

The cell was probably formatted as Text before you entered the formula. To fix it, follow the same approach described in our guide on Microsoft Excel formulas not working or calculating:

1. Select the cell.
2. Press Ctrl + 1 (Windows) or Cmd + 1 (Mac) to open Format Cells.
3. Change the format to General and click OK.
4. Press F2, then Enter to re-evaluate the formula.

References are wrong after copying to another sheet or workbook

Check whether the references that should be locked actually have dollar signs. If a formula like =B2*D1 gets copied to Sheet2, Excel will look for D1 on Sheet2, not on the original sheet. The fix is to either use absolute cross-sheet references (=Sheet1!$D$1) or use a named cell, which resolves correctly across sheets automatically. For more context on how sheets and workbooks relate to each other, see Microsoft Excel workbooks and worksheets: what’s the difference.

I forgot to make a reference absolute before copying it everywhere

Fix one instance of the formula first: click the cell, press F2 to edit, position the cursor on the reference, press F4 to lock it, then press Enter. Copy that corrected cell and paste it over the rest of the range. For large ranges, use Ctrl + H (Find & Replace) with “Look in: Formulas” to replace the relative reference with the absolute version in bulk.

Which Method Should You Use?

For most people, the F4 toggle is the right habit to build. It’s fast, it works in every desktop version of Excel, and it covers 90% of cases. If you’re building a spreadsheet others will maintain, named cells are worth the extra thirty seconds: =B2TaxRate is far easier to audit six months later than =B2$D$1. Use Paste Link when you need a live mirror of a result somewhere else in the workbook. Just don’t confuse it with copying a formula, which creates an independent copy that won’t update.