The fastest way to copy a formula to every row

To copy a formula down a column in Excel, select the cell with the formula, then drag the small square at the bottom-right corner of that cell down to the last row you need. Excel automatically adjusts the cell references as it copies — so if your first cell says =A1+B1, the second row becomes =A2+B2, and so on. This is called a relative reference, and it is the reason Excel saves you from typing the same formula hundreds of times.

The drag method works well for short columns. For longer ones, there is a faster keyboard shortcut: select the cell with the formula, then select from that cell down to the last row where you want the formula to appear. Press Ctrl+D (or Cmd+D on Mac), and Excel fills the entire range at once. This is much quicker than dragging across 500 rows.

Key Takeaways

  • Dragging the fill handle (the small square at the cell's corner) down a column copies the formula and automatically adjusts cell references for each row.
  • For long columns, select the starting cell and the range where you want the formula, then press Ctrl+D to fill down when ready.
  • Excel uses relative references by default, so =A1+B1 becomes =A2+B2 in the next row — this is what makes copying formulas useful.
  • If you want a cell reference to stay the same when you copy, add dollar signs: =$A$1 will not change, but $A1 will only lock the column.

Understanding relative and absolute references

When you copy a formula down, Excel changes the row numbers automatically. If your formula in row 1 says =A1*2, the formula in row 2 becomes =A2*2. This is a relative reference — the cell reference moves relative to where you paste it. Most of the time, this is exactly what you want.

Sometimes you need a cell reference to stay the same when you copy. For example, if you have a tax rate in cell D1 and you want every row to multiply by that same rate, you need to lock D1 so it does not change. To do this, type the formula with dollar signs: =A1*$D$1. The dollar signs tell Excel "do not change this part when I copy." Now when you copy the formula down, A1 becomes A2, A3, and so on, but $D$1 stays as $D$1 in every row.

You can lock just the column, just the row, or both. =$D1 locks the column D but lets the row number change. =D$1 locks the row 1 but lets the column change. This matters when you are copying formulas across columns as well as down rows.

Copying formulas across columns instead of down

The same method works left to right. Select the cell with the formula, then drag the fill handle to the right, or select the range and press Ctrl+R. Excel adjusts the column letters the same way it adjusts row numbers — =A1+B1 becomes =B1+C1 when you copy it one column to the right.

You can also copy both down and across at once. Select the cell with the formula, then drag diagonally to fill a rectangle of cells. Excel adjusts both the row and column references as it goes. This is useful when you are building a table where each cell needs a slightly different calculation based on its position.

What to do when the formula does not copy the way you expect

If you copy a formula down and the results look wrong, the most common cause is that you used absolute references ($) when you meant to use relative ones, or vice versa. Check the formula bar (the long box at the top that shows the formula) and look at what cell references are there. Click on a few cells in the copied range and see whether the references changed the way you expected.

Another common problem is copying a formula that refers to a cell outside the range you are filling. For example, if you copy a formula from row 1 down to row 100, but the formula refers to a header in row 1, you probably need to lock that reference with dollar signs so it does not become row 2, row 3, and so on as you copy.

If the formula copies but shows an error like #REF!, it usually means the formula is trying to refer to a cell that no longer exists after the copy. This happens when you copy a formula that refers to cells above it, and those cells shift. Use absolute references ($) to lock the cells you want to stay the same.

Using fill down with multiple columns at once

If you have formulas in several columns that you want to copy down to the same rows, you can do them all at once. Select all the cells with formulas (the top row of your range), then extend the selection down to the last row you need. Press Ctrl+D, and Excel fills down all the columns at once. This is faster than copying each column one at a time.

To select multiple columns at once, click the first cell, hold Shift, and click the last cell in the range you want to fill. Make sure the range includes both the formulas you want to copy and all the empty cells below them where you want the formulas to go.

Copying formulas from one sheet to another

If you need the same formula on a different sheet, copy the cell normally (Ctrl+C), go to the other sheet, and paste it (Ctrl+V). Excel adjusts the references based on where you paste, just as it does within a single sheet. If you want the formula to refer back to the original sheet, the reference will include the sheet name — for example, =Sheet1!A1+B1.

If you copy a formula to a different sheet and it refers to cells on the original sheet, those references stay locked to the original sheet even if you copy the formula down on the new sheet. The row and column numbers still adjust, but they adjust within the context of the original sheet.

Frequently Asked Questions

What is the fill handle and where do I find it?

The fill handle is a small square at the bottom-right corner of a selected cell. When you hover over it, your cursor changes to a crosshair. Click and drag it down (or right, or diagonally) to copy the formula to adjacent cells. If you do not see it, make sure the cell is selected and you are looking at the very corner of the cell border.

Can I copy a formula to non-adjacent cells?

Not with the fill handle or Ctrl+D — those only work on continuous ranges. To copy a formula to scattered cells, copy the cell (Ctrl+C), then click each cell where you want it and paste (Ctrl+V). Or select all the cells you want at once by holding Ctrl and clicking each one, then paste.

Why did my formula change when I copied it down?

Excel adjusts cell references when you copy — this is relative referencing, and it is usually what you want. If a reference should not change, add dollar signs: =$A$1 locks both the column and row. Check the formula bar to see what references are in the copied cells and whether they match what you intended.

How do I copy a formula down to the last row with data without counting rows?

Select the cell with the formula, then press Ctrl+Shift+End to select from that cell to the last cell with data in the sheet. Then press Ctrl+D to fill down. Alternatively, select the cell, then double-click the fill handle — Excel automatically fills down to match the length of adjacent columns with data.

What does #REF! mean and how do I fix it?

#REF! appears when a formula refers to a cell that no longer exists or is invalid. This often happens when you copy a formula that references cells above it. Use absolute references with dollar signs ($) to lock the cells you want to stay the same, so they do not shift when you copy.