The fastest way to copy a formula

To copy a formula in Excel, click the cell containing the formula, then drag the small square at the bottom-right corner of that cell down or across to the cells where you want the formula to appear. Excel automatically adjusts the cell references as it copies — so if your original formula adds A1 and B1, the next row will add A2 and B2 without you having to change anything.

This small square is called the fill handle. When you hover over it, your cursor changes to a crosshair. That's your signal you can drag. If you're copying to many cells at once, you can also double-click the fill handle and Excel will automatically fill down as far as your data extends in the adjacent column.

The fill handle works for any direction: down the column, across the row, or even diagonally. The principle stays the same — Excel watches which cells you're dragging across and shifts the formula references to match.

Key Takeaways

  • Click the cell with the formula, then drag the fill handle (small square at bottom-right) to copy the formula to adjacent cells.
  • Excel automatically adjusts cell references when you copy, so A1+B1 becomes A2+B2 in the next row without manual changes.
  • Double-clicking the fill handle copies the formula down automatically until it reaches the end of your data.
  • If you need to keep one cell reference the same while copying, add a dollar sign before the letter and number (like $A$1) to lock it in place.

Using copy and paste instead of dragging

If dragging feels awkward or you're copying to cells far away from the original, use copy and paste instead. Click the cell with the formula, press Ctrl+C (or Cmd+C on Mac), then click the first cell where you want the formula to go. Press Ctrl+V to paste. Excel adjusts the references the same way it does when you drag.

You can also select multiple cells at once before pasting. Click the first cell you want to fill, then hold Shift and click the last cell in the range. When you paste, the formula fills all the cells between them. This is especially useful when you're copying across a large area or to cells that aren't next to each other.

When to lock a cell reference with dollar signs

Sometimes you want a formula to reference the same cell no matter where you copy it. This happens often when you have a tax rate, a discount percentage, or a conversion factor that applies to every row. To lock a cell reference, add a dollar sign before the column letter and the row number — for example, $A$1 instead of A1.

If you have a formula like =B2*$A$1 and you copy it down, the B2 will change to B3, B4, and so on, but $A$1 will stay the same in every row. You can also lock just the column (like $A1) or just the row (like A$1) if you only need part of the reference to stay fixed. This is called an absolute reference, and it's one of the most common reasons a formula doesn't work the way you expected.

Copying formulas between different sheets

You can copy a formula from one sheet to another the same way you copy within a sheet — use Ctrl+C and Ctrl+V. Click the cell with the formula, copy it, switch to the other sheet, and paste. Excel keeps track of which sheet each cell reference belongs to, so the formula will still work correctly.

When you paste a formula from one sheet to another, Excel shows the sheet name in the formula bar. For example, a formula might show =Sheet1!A1+B2, where Sheet1!A1 means "cell A1 on Sheet1". You don't need to type this yourself — Excel adds it automatically when you copy and paste between sheets.

What to do if the formula doesn't copy the way you expected

The most common problem is that a cell reference you wanted to stay the same changed when you copied. If you have a formula that divides every number by a total in one cell, and that total changed when you copied, you forgot the dollar signs. Go back to the original formula and add $A$1 (or whichever cell should stay locked) instead of A1, then copy again.

Another issue is copying a formula to a cell that's empty or contains text instead of a number. Excel will still copy the formula, but it might show an error like #VALUE! or #DIV/0!. This usually means the formula is trying to do math on text, not a number. Check that the cells you're copying to contain the right kind of data.

If you copy a formula and it shows #REF!, that means the formula is trying to reference a cell that no longer exists — usually because you deleted a column or row. You'll need to fix the original formula before copying it again.

Using Paste Special to copy only the formula or only the values

By default, when you paste a formula, you get the formula itself. But sometimes you want to paste only the result (the number the formula calculated) without the formula, or paste only the formula without the formatting. Use Paste Special for this.

Press Ctrl+Shift+V (or Cmd+Shift+V on Mac) after copying. A dialog box appears with options. Click "Values" if you want only the numbers, or "Formulas" if you want only the formula without any formatting from the original cell. This is useful when you're moving data between sheets or when you want to lock in a calculation so it won't change if the source data changes.

Frequently Asked Questions

Can I copy a formula to a non-adjacent cell?

Yes. Use copy and paste instead of dragging. Click the cell with the formula, press Ctrl+C, click the cell where you want it, and press Ctrl+V. You can also select multiple non-adjacent cells by holding Ctrl and clicking each one, then paste to fill them all at once.

What does the #REF! error mean?

It means the formula is trying to reference a cell that doesn't exist anymore, usually because you deleted a column or row. Check the original formula to see which cell reference is broken, fix it, and then copy again.

How do I copy a formula but keep the original numbers instead of the formula?

Copy the cell, then use Paste Special (Ctrl+Shift+V) and choose "Values". This pastes only the result of the formula, not the formula itself, so the pasted cells won't change if the source data changes.

Why did my formula change when I copied it down?

Excel automatically adjusts cell references when you copy — this is normal and usually what you want. If a reference should have stayed the same, add dollar signs to lock it: change A1 to $A$1 in the original formula, then copy again.