Maintenance Alert: Saturday, December 9th, 7:00am-5:00pm CT. During this time, the shopping cart and information requests will be unavailable.

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.

Fred Pryor Semianrs_Excel Copy Formula Down 1

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.

Fred Pryor Semianrs_Excel Copy Formula Down 2

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.

Fred Pryor Semianrs_Excel Copy Formula Down 3

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?

Fred Pryor Semianrs_Excel Copy Formula Down 4

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.

 

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.


Choose a Seminar and Save $10:

Related Excel® Articles

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.

Categories

27 Comments

  • shivani rai says:

    simple to understand !

  • Mary says:

    =IF(ISBLANK(H10),””,H$6)
    =IF(ISBLANK(H11),””,H$6)
    =IF(ISBLANK(H12),””,H$6)

    The 6 should be a 10 & 11 & 12, etc! How do I get the 6 to change along with the other cell numbers.??

    • jess says:

      just remove the $ between H and 6 😉

    • Jon says:

      Mary you just need to delete the $ sign and then it will change with them.

      The $ sign acts as an anchor, which can be useful sometimes.

      If you have $H6 then no matter than column, it will always refer to H. If H$6 then no matter the row, always points towards 6. And if $H$6 then no matter where you place it, it will always refer that one cell.

      You can manually add in / delete the $, or when you highlited on that cell of the euqation, you can click F4 (the function key) and it will cycle through the various anchor options, kinda fun.

  • Hall D says:

    I am a begginer, and not a very clever one either but man you saved me tons of time with double click on the plus. And I’ve searched a few sites.
    Many thanks

  • Lakeisha Galloway says:

    I have created a vey simple formula and am attempting to fill down, the formula is filling down but each cell is filling down as the first value. I have this a million times, don’t know what is wrong.

  • Anna says:

    is there a faster way of copying cells like d$3 down a column to get the d to change to e?. what have i done wrong. i tried this a million times. i am doing manually now.

  • rm says:

    Thank you SOOO much!

  • DeerWoman says:

    Thank you — I looked everywhere and then came across this: “go to formulas, calculation options, select automatic rather than manual” Super helpful!

  • Shelly says:

    i have in one cell =300*4. In all the other cells I just have numbers, ie, 475, 294, 927, etc.
    My question is how do I copy the formula of *4 to all those cells? Thanks.

    • Excel Tips and Tricks from Pryor.com says:

      One option would be to put the numbers in one column and the formula (=A2*4) in another, this way you can copy the formula down. Here is an example:
      Copy Formula Down

  • Elshan says:

    What if we have 700 rows? I still need to drag down the + sign?

  • Bob says:

    When performing the drag copy fuction is there a keyboard hotkey that allows you to only take the formula and not the format?

    • Excel Tips and Tricks from Pryor.com says:

      Ctrl + D is a shortcut to copy your information down but it does copy the format as well. Another option would be to copy the information, highlight where you would like it to be pasted and use Alt + H + F to paste ONLY the formula.

  • J says:

    Bloody brilliant. Thank you so much.

  • Paul says:

    I want to format the whole column, not just a portion of it. How do I do that?

    • Excel Tips and Tricks from Pryor.com says:

      Try highlighting the entire column by clicking on the letter above the column you want to format. By having the entire column “selected”, any formatting will now apply to the whole column.

  • Terry Kepner says:

    Hi! I want to copy this formula down a column about 500 times with the row number changing automatically:
    =((1/(R3C19/1000))*3.26)*COS(R3C13) * SIN(R3C14)
    (And I’ve got about five more that need this, too)

    Nothing I’ve tried seems to work!
    I’m using Office Professional 2013.

    • Excel Tips and Tricks from Pryor.com says:

      If R3C19 means “row 3, column 19”, in the description then this problem is only the cell references.

      In MS Excel, cell references are (outside a few specific functions) made as Column/Row, not Row/Column

      In addition, columns are referenced by letter, not number.
      “row 3, column 19” becomes “S3” (as in the example below.)

      If we swap S3 for R3C19, M3 for R3C13 and N3 for R3C14, your calculation works and it can be copied/pasted.

  • Shelan Kaky says:

    thanks so much, it helped me lots

  • Deeptha says:

    Thanks! Coudn’t find Option 2 in many other sites.