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.
- Set up your formula in the top cell.
- Either press Control + C or click the “Copy” button on the “Home” ribbon.
- 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.
- 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.
27 Comments
simple to understand !
=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.??
just remove the $ between H and 6 😉
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.
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
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.
go to formulas, calculation options, select automatic rather than manual
THANK YOU… I just had this same problem today, and was having fits until I found this forum. Cheers!
Thank you, thank you, thank you! This simple tip has saved me!
Kim thank you so much, i was pulling my hair out over that one.
Thank you so much! You’ve saved my sanity!
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.
Thank you SOOO much!
Thank you — I looked everywhere and then came across this: “go to formulas, calculation options, select automatic rather than manual” Super helpful!
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.
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:
What if we have 700 rows? I still need to drag down the + sign?
Option 2: Double-click the plus should help answer this question!
When performing the drag copy fuction is there a keyboard hotkey that allows you to only take the formula and not the format?
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.
Bloody brilliant. Thank you so much.
I want to format the whole column, not just a portion of it. How do I do that?
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.
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.
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.
thanks so much, it helped me lots
Thanks! Coudn’t find Option 2 in many other sites.