What happens when you copy a formula

When you copy a formula from one cell to another in Excel, the cell references inside the formula change automatically. If you write =A1+B1 in cell C1 and copy it down to C2, Excel changes it to =A2+B2. This is usually what you want — it lets you write one formula and reuse it across rows or columns without retyping. But sometimes you need a reference to stay put while others move, and that's where you need to lock it.

Excel has two types of references: relative (which change when you copy) and absolute (which don't). Most formulas use relative references by default. Understanding which type you need before you copy saves you from discovering halfway through that all your numbers are wrong.

Key Takeaways

  • Relative references like A1 change automatically when you copy a formula down or across, so A1 becomes A2, then A3.
  • Absolute references use dollar signs ($A$1) and never change, no matter where you copy the formula.
  • Mixed references ($A1 or A$1) lock only the column or only the row, letting the other part move.
  • You add dollar signs by typing them yourself or by pressing F4 while the cursor is on the reference you want to lock.
  • Test your formula in the second cell before copying it down the whole column to catch mistakes early.

Using relative references when you want formulas to adjust

Relative references are the default in Excel, and they're the right choice when you're copying a formula across similar data. Say you have sales numbers in columns A through D for four salespeople, and you want to calculate the total for each person in column E. You write =A1+B1+C1+D1 in cell E1. When you copy this formula to E2, it automatically becomes =A2+B2+C2+D2, which is exactly what you need.

To copy a formula with relative references, select the cell with the formula, copy it (Ctrl+C on Windows, Cmd+C on Mac), then select the range where you want it to go and paste (Ctrl+V or Cmd+V). Excel shifts all the references for you. This works the same way whether you're copying down rows or across columns.

Locking references with dollar signs

An absolute reference uses a dollar sign before the column letter and before the row number: $A$1. When you copy a formula containing $A$1, that reference stays $A$1 no matter where you paste it. This is useful when one cell contains a value you need to use in many calculations — like a tax rate, a discount percentage, or a conversion factor.

Say you have a tax rate in cell B1 (0.08 for 8 percent) and you want to calculate tax for ten different purchase amounts in column A. In cell C1, you write =A1*$B$1. When you copy this down to C2, it becomes =A2*$B$1 — the A reference moves to A2 (because you want each row's amount), but B1 stays locked. If you had written =A1*B1 without the dollar signs, copying down would have changed it to =A2*B2, and you'd be multiplying by the wrong cells.

To add dollar signs, you can type them directly into the formula. Or, while editing the formula, click on the reference you want to lock and press F4. Each press cycles through the four options: relative (A1), absolute column (A$1), absolute row ($A1), and fully absolute ($A$1). Stop when you see the version you need.

Using mixed references to lock only part of a reference

A mixed reference locks either the column or the row, but not both. $A1 locks the column (A stays A, but 1 becomes 2, 3, 4 as you copy down). A$1 locks the row (1 stays 1, but A becomes B, C, D as you copy across). Mixed references are less common than absolute or relative, but they're essential for certain layouts.

Imagine a multiplication table where you want to multiply the numbers in row 1 by the numbers in column A. In cell B2, you'd write =$A2*B$1. The $A locks the column so it always refers to column A (the row multiplier), and the $1 locks the row so it always refers to row 1 (the column multiplier). When you copy this formula across and down, it adjusts correctly for every cell in the table.

Copying formulas without changing references

The method you use to copy doesn't matter — what matters is what's inside the formula. Whether you use Ctrl+C and Ctrl+V, drag the fill handle (the small square at the bottom-right corner of a selected cell), or use the menu, Excel applies the same rules. Relative references move, absolute references stay put, and mixed references lock only the part you specified.

If you're dragging the fill handle, select the cell with the formula and position your cursor at the bottom-right corner until it turns into a small plus sign. Then drag down or across as far as you need. This is faster than copy-paste for short distances, but copy-paste is more reliable when you're going a long way or copying to non-adjacent cells.

Testing your formula before copying it down

Before you copy a formula to fifty rows, test it in the second cell. Write your formula in the first cell, copy it to the second cell, and check that the references changed the way you expected. If you see the wrong cell reference in the formula bar, you caught the mistake before it spread. This takes thirty seconds and saves you from having to fix a hundred cells later.

To see what a formula actually contains, click on any cell with that formula and look at the formula bar at the top of the screen. You'll see the exact references, with or without dollar signs. This is the fastest way to verify that your absolute and relative references are in the right places.

Common mistakes and how to avoid them

The most common mistake is forgetting to lock a reference that should be locked. You write a formula that works in the first row, copy it down, and halfway through you realize the reference that should have stayed put has moved. The fix is to go back to the original cell, add dollar signs to the reference that should be absolute, and copy again.

Another mistake is locking a reference that should move. If you write =$A$1+$B$1 and copy it down, every cell will add the same two numbers instead of moving down the columns. Check your formula logic before you copy: which references should change, and which should stay the same?

A third mistake is mixing up the syntax. The dollar sign goes before both the column and the row for a fully absolute reference: $A$1, not A$1$ or $A1$. If your formula isn't working the way you expect, check the dollar sign placement in the formula bar.

Frequently Asked Questions

What's the difference between copying a formula and copying the value?

When you copy a formula, Excel copies the formula itself and adjusts the references based on where you paste it. When you copy a value, you're copying only the result — the number that appears in the cell. To copy only the value, use Paste Special (Ctrl+Shift+V on Windows, Cmd+Shift+V on Mac) and choose Values Only. This is useful when you want to replace a formula with its result.

Can I lock just the column or just the row?

Yes, that's a mixed reference. $A1 locks the column A but lets the row number change. A$1 locks row 1 but lets the column letter change. Use F4 to cycle through the options while editing a formula, or type the dollar signs yourself.

What happens if I copy a formula to a cell that already has a formula?

The old formula is replaced by the new one. Excel doesn't warn you, so be careful when pasting over existing formulas. If you need to keep both, paste into an empty cell first.

Do I have to use dollar signs, or are there other ways to lock a reference?

Dollar signs are the standard way. Some people use named ranges (giving a cell a name like "TaxRate" instead of referring to it by address), which also stay locked when you copy. Named ranges are more readable in complex spreadsheets, but dollar signs are faster for straightforward cases.

Why does my formula show the cell address instead of the result?

Excel is showing the formula as text instead of calculating it. This usually happens if the cell is formatted as text. Click the cell, go to Format > Cells, change the format to Number, and press Enter. The formula should calculate and show the result.