How to Copy a Formula Without Changing Its References

To copy a formula without changing its references, lock the references with dollar signs ($F$1) before you copy, or move the formula with cut and paste, which never changes references. For a single exact copy, copy the formula's text from the formula bar or press Ctrl+'. This guide explains why Excel changes references in the first place and when to use each method.

Why Excel changes references when you copy#

By default, references are relative: when you copy a formula, Excel moves them by the same distance as the copy. Here's a monthly sales table:

B6 shows 715. Copy B6 and paste it into C6, and the formula becomes =SUM(C2:C5), February's total (687). You moved one column right, so the range moved one column right too.

Most of the time, that's exactly what you want. The problem comes when one reference must not move.

Method 1: Lock references with $ before you copy#

A dollar sign locks the part of the reference right after it. $F$1 locks the column and the row, so it never moves. This is called an absolute reference.

If C2 is =B2*(1+F1) and you fill it down, C3 becomes =B3*(1+F2). F2 is empty, so the monitor gets no tax. Lock the rate instead:

=B2*(1+$F$1)

Now C3 becomes =B3*(1+$F$1) and shows 268.92. B2 still moves to B3, because each row needs its own price. Only the rate is locked.

The quick way to add $ signs: while you edit, put the cursor on the reference and press F4. On a Mac, press fn+F4 or ⌘T. Each press cycles F1 → $F$1 → F$1 → $F1. The last two are mixed references, which lock only the row or only the column. Excel cell references explained covers all four.

Method 2: Cut and paste to move a formula#

When you cut a formula (Ctrl+X, or ⌘X on a Mac) and paste it, its references stay exactly the same. Excel treats it as a move, not a copy.

Cut is the right tool when the formula is in the wrong place and should still point at the same cells. Other formulas that use the moved cell are updated to follow it, so nothing breaks. The original cell is left empty.

Method 3: Copy the formula text from the formula bar#

To make an exact duplicate and keep the original, copy the formula as text:

1. Click the cell with the formula.
2. Click in the formula bar and select the whole formula, including the =.
3. Press Ctrl+C (⌘C), then Esc to leave the cell unchanged.
4. Click the new cell and press Ctrl+V (⌘V), then Enter.

The pasted formula is identical: =SUM(B2:B5) stays =SUM(B2:B5). This works anywhere, even on another sheet. You can also press F2 (⌃U on a Mac) to edit the cell and select the text there; editing cells without the mouse explains those keys.

Method 4: Ctrl+' copies the formula from the cell above#

Press Ctrl+' (Ctrl and the apostrophe key; ⌃' on a Mac) to copy the formula from the cell directly above into the active cell, unchanged. Excel puts it in edit mode, so you can change it before you press Enter.

It's the fastest way to repeat a total or a check formula exactly one row down.

Method 5: Copy many formulas at once with Find and Replace#

To copy a whole block of formulas exactly, you can turn them into text first:

1. Select the formulas and press Ctrl+H (⌃H on a Mac) to open Replace.
2. Replace = with #=. The formulas become plain text.
3. Copy and paste the block where you need it.
4. Select both blocks and replace #= with = again.

This is an advanced trick. Make sure #= doesn't already appear in your sheet, and check a few results afterwards.

Copy formula without changing references: which method?#

Pick the method by what you're trying to do:

In job tests and interviews, the $ sign is what people check most. Locking a rate is one of the most common Excel interview questions, and fixed rates and totals are everyday work in Excel for finance jobs.

Common mistakes when locking references#

Watch for these mistakes:

• Locking everything. =$B$2*(1+$F$1) copied down gives the laptop's price in every row. Lock only what must stay fixed.
• Locking the wrong part. When you fill right, the column letter moves. B$2 still moves to C2. Use $B2 or $B$2.
• Typing $ in the wrong place. F$$1 or $1F are errors. The $ goes before the column letter and before the row number.
• Forgetting the format. The rate in F1 should show as 8%, not 0.08, so people can read it. Here's how to format numbers as percent.

Practise copying formulas#

Open the relative vs absolute references lesson to fill a tax formula down, see it break, then fix it with $ and F4. The copy, cut and paste lesson shows how copied references move and how cut keeps them the same. Both run in a live sheet and take about five minutes each.