How to Copy a Formula Down a Column in Excel

Key Takeaways

• The fastest way to copy a formula down in Excel is to select the cells and press Ctrl+D to fill down instantly.
• You can also drag or double-click the fill handle (the small plus icon at the cell's lower-right corner) to copy a formula down an entire column.
• Use copy and paste (Ctrl+C, then Ctrl+V) when you need flexibility, such as copying formulas without formatting or beyond existing data. 
• If a formula returns unexpected results after copying, check whether your cell references should be a relative reference, an absolute reference or a mixed reference.

One of the more tedious tasks in Excel is copying a formula down an entire column of a report. It's time-consuming, and it leaves the opportunity for error if you don't copy the formula down to exactly the correct cells. And what do you do if you get an unexpected result after moving a formula? Fortunately, Excel offers several ways to fill a formula down quickly, including keyboard shortcuts, the fill handle and copy/paste. Let's look at each method and learn how to fix common problems along the way.

When to Use Each Method

Before diving into the steps, here's a quick guide to choosing the right approach:

• Use Ctrl+D when you want the quickest single-keystroke fill.
• Drag the fill handle when you're working with a short, visible range.
• Double-click the fill handle when your column runs hundreds or thousands of rows and there is data in an adjacent column.
• Use copy and paste when you need to paste only the formula, skip formatting or copy beyond the last row of data.

3 Ways to Copy Formulas Down a Column

Option 1: Drag the Fill Handle

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 (also called the fill handle). Click the plus and drag it down. This way you drag the formula down all the way in Excel.

Excel will fill the cells with a copy of the original formula and automatically advance all relative cell references based upon the direction the formula has been moved from its original cell.

Option 2: Double-Click the Fill Handle

What if you want to copy the formula down a 400-line report? Option 1, dragging the fill handle down 400 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—for instance, to preserve conditional formatting you've already applied? 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 Ctrl+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 Ctrl+V or click the "Paste" button on the "Home" ribbon. For more paste options, click the arrow below the "Paste" button.

Tip: For a quicker fill, select the cell with the formula and the cells below it, then press Ctrl+D to fill the formula down without copying first.

What to Do When Something Goes Wrong

Formulas—from a basic SUM formula to complex nested calculations—depend upon cell references in related columns or rows to complete their calculations. When you use the methods above, Excel will automatically advance cell references based upon the direction the formula has been moved from its original cell.

References in Excel copy to new locations just as you would want them to in most situations; but to understand how to fix problem formulas, let's review a bit about how Excel formula references work in the Excel environment.

Relative References

"Relative reference" means that the formula changes when you copy it to another cell. In other words, the reference is relative to the location of the formula.

Try it. In cell A1, enter "20"; and in cell A2, enter "30". In cell B1, enter the formula "=A1+1".

Now copy the formula in cell B1:

• Click on cell B1.
• Click the Copy button. The selection rectangle surrounding B1 will change to moving dashes.
• Select cell B2.
• Click the Paste button.

Now examine the excel formula in B2, and you'll find that, instead of "=A1+1", it shows "=A2+1". The reference to A1 has changed to refer to A2. This is how a relative reference behaves. Wherever you copy this formula, you'll find that it operates similarly, always referring to the cell directly to the left of the formula.

Absolute References

Excel defaults to relative references because that's what you'll need more often than not, yet sometimes you want a formula always to point to the original source. You can do this with an "absolute reference," which means that the formula does not change when you copy it to another cell.

In the example above, edit the formula in cell B1 by inserting dollar signs in front of the "A" and the "1" in the cell reference.

Now copy the formula in cell B1 down to B2 and examine the result. Instead of changing to "=A2+1", it shows "=$A$1+1", just as you typed it in B1. Wherever you copy a formula with an absolute reference, it will always to point to the original source. 

Mixed References

Sometimes you need a formula always to refer to the original source column, but to change with each row. You can do this with a mixed reference—that is, one that is made up partially of relative references and partially of absolute references. 

In the example above, change the formula in B1 to "=$A1+1". Now copy it to a cell in a different column and a different row. Wherever you paste the formula, it always refers to column A, but the row changes to the current row. 

Single-Column References

Excel provides an alternative method of referring to the cell in a specific column of the current row: by referring only to the column, leaving the row out of the reference. In the example above, change the formula in B1 to "=$A:$A+1" and copy this formula anywhere in the spreadsheet.

Because of the dollar signs, this formula refers to the value in column A, regardless of where you paste it; and because the row is not specified, it always refers to the current row. This also causes Excel to "spill" the formula into the rest of the column, even where you don't have data in the reference column (A). You cannot, then, paste this formula into another column.

Troubleshooting

So when you run into a formula that gives an error or isn't working as expected after copy/pasting, check the cell references and make sure calculations are based on the cells you want. 

Let's look at an example: In this sheet, F3 contains the formula =E3*H2, or the sale price in row 3 times the commission rate (H2). When that was copied into F4, the formula advanced the references to =E4*H3. But there is no data in H3, giving us a null result. 

Because H3 will always be the same reference on each row, it needs to be absolute.

Our new formula =E3*$H$2 corrects the problem and we can paste the column.

Next Steps

Excel's relative and absolute references provide you the ability to create powerful formulas that can be copied across multiple rows and columns, always returning the answers that you need. Here are a few ways to keep building your Excel skills:

• Try using range names, which act like absolute references but, by using your own custom names, make your formulas easier to read and maintain.
• Explore Pryor Learning's Microsoft® Excel® Basics and Microsoft® Excel®: Beyond the Basics courses to strengthen your overall Excel proficiency.
• Explore 60 Minutes of Excel® Secrets for additional time-saving shortcuts, or check out PryorPlus for ongoing access to hundreds of professional development courses.