3 Ways to Copy a Formula Down a Column

One of the more tedious tasks in Excel is to copy a formula down an entire column of a report. Not only is it mind-numbing, but it also leaves the opportunity for error if you don’t copy the formula down to exactly the correct cells. For best results, try one of the methods below.

Option 1: Drag the Plus

First create your formula in one cell.

After you are satisfied that it is correct, place your mouse cursor at the lower right-hand corner of the cell. You’ll know you have hit it when the cursor changes to a plus sign. Click the plus and drag it down, filling the cells with a copy of the original formula.

Option 2: Double-click the plus

What if you want to copy the formula down a four-hundred-line report? Option 1, dragging the plus down four hundred rows, would burn up your time—and your temper.

Instead, you can accomplish the same copy with a double-click instead of a drag. Set up your formula in the top cell, position the mouse in the lower right-hand corner of the cell until you see the plus, and double-click.

Note that this option can copy the formula down as far as Excel finds data to the left. If you have row headers or other data in place, Excel continues to copy the formula. The fill stops when Excel sees that you have no headers or data to the left.

Option 3: Copy and Paste

What if you want to copy the formula but not the formatting? What if you want to copy beyond the end of the data? Or if you have some other need for flexibility in copying the formula?

You can always use the good ole’ copy and paste method.

1. Set up your formula in the top cell.
2. Either press Control + C or click the “Copy” button on the “Home” ribbon.
3. Select all the cells to which you wish to copy the formula. You can select cells either by dragging the mouse or by using keyboard shortcuts.
4. Either press Control + V or click the “Paste” button on the “Home” ribbon. For more paste options, click the arrow below the “Paste” button.

Watch for Green Warning Triangles

What should you do if, after copying your formulas, you see a green triangle in the upper left-hand corner of a cell?

This indicates a possible error in the formula. In this example, the correct formula was copied to the surrounding cells but not to this cell, resulting in a formula that differed from its neighbors. Excel noted this and marked it for review. You can fix it by recopying the correct formula to this cell or fixing it in the formula bar.

Otherwise, click the exclamation point for other options.

Next Steps

One of Excel’s strengths is that it offers you many ways to do the same task, leaving you to choose the one that is most convenient. This is true for copying formulas down. This post shows three options for copying a formula down a column, but as you continue to work in Excel, you’ll find other ways to do the same thing, such as through tables or through special range names.