The fastest way to copy a formula to many rows at once

To copy a formula down 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 next row becomes =A2+B2, and so on. This is called a relative reference, and it is what makes copying formulas useful instead of typing each one by hand.

If dragging feels slow or imprecise, you can also select the cell with the formula, copy it (Ctrl+C on Windows, Command+C on Mac), then select the range where you want it to go and paste (Ctrl+V or Command+V). Both methods do the same thing — the drag method is just faster once you get the feel for it.

Key Takeaways

  • The small square at the bottom-right corner of a selected cell is called the fill handle; drag it down to copy the formula to multiple rows below.
  • Excel changes cell references automatically as it copies, so =A1+B1 becomes =A2+B2 in the next row, which is usually what you want.
  • If you want a cell reference to stay the same when you copy, put a dollar sign before the column letter and row number — =A$1+B1 keeps row 1 fixed but lets the column change.
  • You can copy a formula across columns the same way you copy it down rows, by dragging the fill handle to the right instead of down.

Finding and using the fill handle

When you click on a cell in Excel, a small square appears at the bottom-right corner of the cell border. This is the fill handle. Position your cursor directly on that square — the cursor will change to a small crosshair — then click and drag downward. As you drag, Excel highlights the cells you are filling, and when you release, the formula copies to all of them at once.

The fill handle works best when you can see both the cell with the formula and the last row you want to fill. If you need to copy a formula down 500 rows, dragging is not practical. In that case, click the cell with the formula, then hold Shift and click the last cell in the range you want to fill. Then press Ctrl+D (Windows) or Command+D (Mac) to fill down all at once.

When Excel changes cell references and when it does not

By default, Excel uses relative references. This means when you copy a formula down, the row numbers change automatically. If your formula in row 1 says =A1+B1, and you copy it to row 2, it becomes =A2+B2. This is almost always what you want — you are calculating the same thing for each row, just using that row's data.

Sometimes you need a reference to stay the same no matter where you copy the formula. This is called an absolute reference. To create one, type a dollar sign before the part you want to lock. =A$1+B1 keeps row 1 fixed but lets the column change as you copy across. =$A$1+$B$1 locks both the column and row completely. You can also lock just the column (=$A1) or just the row (=A$1), depending on what you need.

A common use for absolute references is a tax rate or discount that applies to all rows. If your tax rate is in cell C1, you might write =A2*$C$1 in row 2, then copy that formula down. Every row will multiply its value by the tax rate in C1, not by the value in its own row's C column.

Copying formulas across columns instead of down

The same fill handle works left and right as well as up and down. Click the cell with your formula, then drag the fill handle to the right to copy the formula across columns. 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 select a cell, copy it, then select a range of cells to the right and paste. The keyboard shortcut Ctrl+R (Windows) or Command+R (Mac) fills to the right, just like Ctrl+D fills down.

What to do if the formula does not copy the way you expected

If you copy a formula and the results look wrong, the most common cause is that you used a relative reference when you needed an absolute one, or vice versa. Check the formula bar (the box at the top that shows the formula) to see what cell references the copied formula is actually using. If they changed when they should not have, add dollar signs. If they stayed the same when they should have changed, remove them.

Another issue is copying a formula that refers to cells outside the range you are filling. If your formula in row 1 says =SUM(A:A), copying it down will not break anything — each row will still sum the entire column A. But if your formula says =A1+A2+A3 and you copy it down, the references shift, which might not be what you intended. In that case, use absolute references for the cells you want to stay fixed.

Copying formulas with mixed data types

Excel treats numbers, text, and dates differently, but copying a formula works the same way regardless. If your formula adds numbers in one column and concatenates text in another, the formula copies correctly — Excel does not change how the formula works, only which cells it refers to.

One exception: if a cell contains text that looks like a number (like a ZIP code stored as text), and your formula treats it as a number, copying the formula down will explore the same treatment to every row. This usually works fine, but if you are getting unexpected results, check whether your data is actually stored as text or as a number. You can see this in the formula bar when you click the cell.

Frequently Asked Questions

Can I copy a formula to non-consecutive rows?

Not in one action. You can copy the formula to a continuous range, then delete the rows you do not need, or you can copy the formula to each separate range one at a time. If you need to fill many non-consecutive rows, it is usually faster to copy to the whole range and then delete the unwanted rows.

What if I copy a formula and it shows an error like #REF?

This usually means the formula refers to a cell that no longer exists or is outside the valid range. Check the formula bar to see what cells it is trying to use. If you copied a formula that refers to cells above it, and those cells do not exist in the new location, you will get this error. Use absolute references for cells that should not change.

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

Click the cell with the formula, then press Ctrl+Shift+End (Windows) or Command+Shift+End (Mac) to select from that cell to the last cell with data in the sheet. Then press Ctrl+D or Command+D to fill down. This works if your data is in a continuous block with no empty rows in between.

Can I copy a formula and have it always refer to the same cell?

Yes, use an absolute reference by adding dollar signs: =$A$1. This locks both the column and row, so no matter where you copy the formula, it will always refer to cell A1. If you only want to lock the row, use =A$1. If you only want to lock the column, use =$A1.