The fastest way: double-click the fill handle

When you have a formula in one cell and need it to repeat down a column, Excel's fill handle is the quickest method. The fill handle is the small square at the bottom-right corner of any selected cell. Position your cursor directly over that square until it turns into a plus sign, then double-click. Excel will automatically copy the formula down to match the length of the data in the adjacent columns.

This works because Excel looks at the columns next to your formula and extends the formula as far as the data goes. If you have customer names in column A from rows 1 to 50, and a formula in column B row 1, double-clicking the fill handle in B1 will copy that formula down to B50. The cell references in the formula update automatically as they go down — if your formula says =A1*10, it becomes =A2*10 in row 2, =A3*10 in row 3, and so on.

Key Takeaways

  • Double-clicking the fill handle (the small square at the bottom-right of a selected cell) copies a formula down to match the length of nearby data columns.
  • Dragging the fill handle down manually gives you control over exactly how many rows receive the formula.
  • Selecting the range you want to fill and using Ctrl+D (or Cmd+D on Mac) copies the formula from the top cell down to all selected cells below it.
  • When you copy a formula down, cell references like A1 automatically adjust to A2, A3, and so on — unless you lock them with dollar signs like $A$1.
  • If the double-click method copies too many or too few rows, you can undo with Ctrl+Z and try dragging instead.

Dragging the fill handle for precise control

If you want to decide exactly how far down the formula goes, drag the fill handle instead of double-clicking. Click and hold on the fill handle, then drag it down as many rows as you need. You will see a tooltip showing which row you are on, so you can stop at the right place.

This method is useful when your data is uneven — for example, if some columns have 50 rows of data and others have only 30. You can copy the formula down exactly 30 rows without accidentally filling empty cells below. Release the mouse button when you reach the row you want, and the formula will fill all cells from the starting point to where you stopped.

Using Ctrl+D to fill down a selected range

Another reliable method is to select the entire range you want to fill, then use the keyboard shortcut. First, click on the cell containing the formula. Then hold Shift and click on the last cell in the column where you want the formula to go. This selects the whole range — for example, B1:B50.

Once the range is selected, press Ctrl+D on Windows or Cmd+D on Mac. Excel copies the formula from the top cell down to every cell in your selection. This method is especially useful when you are working with large datasets and want to be certain of the range before copying, or when the fill handle is hard to see on your screen.

Understanding how cell references change when you copy down

When Excel copies a formula down a column, it automatically adjusts the cell references. If your formula in B1 says =A1+10, then B2 will say =A2+10, B3 will say =A3+10, and so on. This is called a relative reference, and it is what you want most of the time.

Sometimes you need a reference to stay the same as you copy down. For example, if you have a tax rate in cell D1 and want to multiply every value in column B by that rate, you do not want the reference to D1 to change. To lock a reference, add dollar signs: =$D$1. Now when you copy the formula down, it will always refer to D1, never to D2 or D3. You can also lock just the column (=$D1) or just the row (=D$1) if you need partial locking.

What to do if the formula copies to the wrong number of rows

If double-clicking the fill handle copies too many rows or too few, undo the action when ready with Ctrl+Z. Then try dragging the fill handle manually instead, or select your exact range and use Ctrl+D. The undo command will restore your spreadsheet to the state before the copy, so you can try again without losing anything.

The double-click method sometimes behaves unexpectedly if your data has gaps or if the adjacent columns have different lengths. In those cases, dragging or selecting your range first gives you more control over the outcome. There is no penalty for undoing and trying a different method — it is faster than fixing a formula that went to the wrong rows.

Copying formulas across columns instead of down

The same methods work for copying a formula across columns instead of down rows. If you have a formula in B1 and want to copy it to C1, D1, and E1, select B1, then drag the fill handle to the right. Or select B1:E1 and press Ctrl+R to fill right. The cell references will adjust the same way — A1 becomes B1, then C1, then D1 — unless you have locked them with dollar signs.

You can also combine both directions: select a range like B1:E10, put a formula in B1, and use Ctrl+D to fill down, then Ctrl+R to fill right. Excel will copy the formula to every cell in the range, adjusting references as it goes. This is useful for creating tables where each row and column needs a slightly different calculation based on its position.

Common mistakes and how to avoid them

The most common mistake is forgetting that cell references change when you copy. If you write a formula that refers to a specific cell and you want that reference to stay the same everywhere, add the dollar signs before you copy. Check your formula in a few cells after copying to make sure the references are what you expected.

Another mistake is copying a formula to cells that should be empty. If you use the double-click method and it fills too far, undo and try dragging instead. It is also worth checking that your original formula is correct before copying it down — if the formula in the first cell is wrong, every copy of it will be wrong too. Click on a cell in the middle of your copied range and look at the formula bar to verify that the formula looks right and the references have adjusted correctly.

Frequently Asked Questions

Why does double-clicking the fill handle sometimes copy too many rows?

Double-click copies as far as the data extends in the nearest column with data. If column A has 100 rows but column C has 50, and you double-click the fill handle in column B, it will copy to row 100. Check which columns have data, or use Ctrl+D with a selected range instead for more control.

How do I copy a formula down without changing the cell references?

Use dollar signs to lock the references. Write =$A$1 instead of =A1, and the reference will not change when you copy. You can lock the whole cell ($A$1), just the column (=$A1), or just the row (=A$1) depending on what you need.

Can I copy a formula down to thousands of rows at once?

Yes. Select the cell with the formula, then select the entire range where you want it to go (for example, B1:B5000), and press Ctrl+D. Excel will fill all of them at once. Dragging the fill handle is not practical for that many rows, but keyboard shortcuts and selection methods work when ready.

What if I copy a formula down and some cells show an error?

Check the formula in one of the error cells by clicking on it and looking at the formula bar. The cell references may have adjusted in a way that does not work — for example, referring to an empty cell or a cell with text instead of a number. You may need to use dollar signs to lock certain references, or rewrite the formula to work correctly when copied.

Is there a way to copy a formula down without it adjusting the cell references?

Not automatically — Excel is designed to adjust references when you copy. But you can lock the references you want to stay the same by adding dollar signs before copying. If you want every copy to refer to the exact same cells, use =$A$1 instead of =A1.