The fastest way to copy a formula is to select the cell with the formula, then drag the small square at its bottom-right corner down or across to the cells where you want it to go

Excel's fill handle — that tiny square at the corner of a selected cell — is the quickest method for most people. Click the cell containing your formula, position your cursor over the small square until it turns into a crosshair, then click and drag. Excel automatically adjusts the cell references as it copies, so a formula that adds B1 and C1 will become B2 and C2 in the next row, B3 and C3 in the row after that, and so on.

If you have many rows or columns to fill, dragging can be tedious. Double-clicking the fill handle instead tells Excel to copy the formula down to the last row that has data in the adjacent column, which saves time when you're working with large datasets.

Key Takeaways

  • The fill handle (small square at the bottom-right of a cell) copies formulas and automatically adjusts cell references for each new row or column.
  • Double-clicking the fill handle fills down to the last row with data in an adjacent column, which is faster than dragging for large datasets.
  • Copy and paste preserves the original formula structure, while paste special lets you copy only the values or only the formatting if you need something different.
  • Absolute references (marked with dollar signs like $B$1) stay the same when copied, while relative references (like B1) change automatically — use absolute references when you want a formula to always point to the same cell.

Using copy and paste when the fill handle won't work

The fill handle works best when you're filling adjacent cells in a straight line. If you need to copy a formula to cells that aren't next to each other, or if you want more control over what gets copied, use copy and paste instead.

Select the cell with the formula, press Ctrl+C (or Cmd+C on Mac), then click the cell where you want to paste it and press Ctrl+V. Excel treats this the same way as the fill handle — it adjusts the cell references automatically. If you need to copy the formula to multiple cells at once, select all of them before pasting, and Excel will paste into every selected cell.

When to use paste special to keep only values or formatting

Sometimes you copy a formula but realize you only want the result, not the formula itself. This happens 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 updates. Right-click after pasting and select "Paste Special," then choose "Values Only." The cell will now show the number the formula produced, not the formula itself.

Paste Special also lets you copy only the formatting (colors, fonts, borders) without the formula, or only the formula without the formatting. This is useful when you've spent time formatting cells and want to explore the same look to new data without overwriting the formulas you've already built.

Understanding absolute and relative references in copied formulas

When you copy a formula, Excel assumes you want the cell references to change in a predictable way. A formula in B2 that says =A1+A2 will become =A2+A3 when you copy it to B3. These are relative references — they move relative to the new location.

Sometimes you want a reference to stay the same no matter where you copy the formula. This is called an absolute reference, and you create it by adding dollar signs: =$A$1. If you copy a formula containing =$A$1 to any other cell, it will still point to A1. You can also mix them: =A$1 keeps the row fixed but lets the column change, while =$A1 keeps the column fixed but lets the row change.

A common example: if you have a tax rate in cell E1 and you want to multiply each price in column A by that rate, you'd write =A2*$E$1 in B2. When you copy this down, it becomes =A3*$E$1, =A4*$E$1, and so on. The A reference changes (relative), but the E1 reference stays locked (absolute).

Copying formulas between different sheets

You can copy a formula from one sheet to another the same way you copy within a sheet — select, copy, switch sheets, paste. Excel will adjust the references automatically, but it will assume you want to reference the same cells in the new sheet. If that's not what you want, you may need to edit the formula after pasting.

If you're copying a formula that references cells in the original sheet, those references will stay pointed at the original sheet even after you paste into a new one. For example, if Sheet1 contains =A1+B1 and you copy it to Sheet2, it will become =Sheet1!A1+Sheet1!B1 — the formula now explicitly says "go back to Sheet1 and get those values." This is usually what you want, but it's worth checking the formula bar to confirm.

What to do if your copied formula shows an error

The most common error after copying is #REF!, which means Excel can't find the cells the formula is supposed to reference. This usually happens when you copy a formula that references cells above or to the left, then paste it somewhere that would require looking at cells that don't exist (like trying to reference row 0, which doesn't exist). Check the formula bar to see what references it's trying to use, and either adjust the formula or use absolute references if you need the formula to always point to the same cells.

Another common problem is #VALUE!, which means the formula is trying to do math on text or empty cells. This isn't usually caused by copying — it's a problem with the data itself — but it becomes visible when you copy the formula to new rows. Look at the cells the formula references and make sure they contain numbers, not text or blank spaces.

Copying formulas in bulk with keyboard shortcuts

If you need to copy a formula to many cells at once, select the cell with the formula, then select the entire range where you want it to go. You can do this by clicking the first cell, holding Shift, and clicking the last cell, or by typing the range into the Name Box (the field that shows the cell address at the top left). Then press Ctrl+D (or Cmd+D on Mac) to fill down, or Ctrl+R to fill right. Excel will copy the formula to every selected cell and adjust the references automatically.

This method is faster than dragging when you're working with large ranges, and it's more reliable than double-clicking the fill handle because you have complete control over exactly which cells get filled.

Frequently Asked Questions

Can I copy a formula to a different cell without it changing the references?

Yes, use absolute references. Put dollar signs before the column letter and row number: =$A$1. When you copy this formula anywhere, it will always point to A1. If you only want the row to stay fixed, use =$A1; if you only want the column to stay fixed, use =A$1.

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

A formula is a set of instructions that recalculates whenever the data changes. A value is a static number. If you copy a formula and the source data updates, the copied formula updates too. If you copy only the value, it stays the same forever. Use Paste Special and choose "Values Only" to paste just the number.

Why does my formula show the formula text instead of the result?

The cell is probably formatted as text. Click the cell, go to the Format menu, choose "Cells," and change the format to "Number" or "General." Then press Enter. If that doesn't work, delete the formula and retype it — sometimes copying into a text-formatted cell causes this issue.

Can I copy a formula from one Excel file to another?

Yes, copy and paste works between files. Open both files, copy the cell from the first file, switch to the second file, and paste. If the formula references cells in the original file, Excel will add the filename to the reference so it still works. If you want the formula to reference cells in the new file instead, you'll need to edit the formula after pasting.