Last Updated on
Copying formulas in Excel is one of the simplest things you can do in the program.
It normally just takes one click of a mouse. However, there are some cases that are a bit trickier, and that it doesn’t hurt to have some tips for.
For example, copying a number of formulas while keeping the cell references the same, or entering the same formula in multiple cells that are not next to each other.
Fortunately, you can perform the same tasks in a variety of ways with Excel, and copying formulas is no exception.
Below we’ll take you through two methods of copying a formula in Excel: copying a formula down a column and copying a formula down without copying the formatting.
How to copy a formula down a column
With Excel, you copy a formula down a column pretty quickly. You just need to follow these steps:
Step
Enter A Formula Into The Top Cell
Enter a formula into the top cell.
Step
Select The Cell With The Formula
Select the cell with the formula, and hover the cursor over a small square at the lower right-hand corner of the cell. This is the Fill handle. When you do this, the cursor will transform into a thick black cross.
Step
Hold And Drag
Hold and drag the fill handle down the column over your chosen cells. Also, the formula can also be dragged into the cells to the right, left, or above.
Step
If The Formula Has Relative Cell References
If the formula has relative cell references (minus the dollar sign) they will change automatically according to where the rows and columns are positioned.
After you have copied the formula you need to confirm that the cell references have been properly adjusted and give you the desired results.
If you need to, use the F4 key to switch between absolute, relative, and mixed references.
How To Copy A Formula Down Without Copying The Formatting
Copying a formula down by dragging the fill handle copies the formula, as well as the formatting of the source cell.
So things like the background color, currency symbols, font color, and the number of displayed decimal places will stay the same.
This usually works with no issues, but sometimes it can wreak havoc on the existing formats in cells where you’re copying the formula to. A popular example is overwriting alternate row shading.
To prevent overwriting the existing cell formatting, drag the fill handle by releasing it, clicking the Auto Fill Options drop-down menu, and select Fill Without Formatting.
Copying formulas in Excel is really simple with the fill handle. But does this get harder when you need to copy a formula down a thousand-line sheet? Well, it could be.
Dragging the formula over hundreds and hundreds of rows does not sound fun, and could be quite time-consuming.
However, Excel offers a couple of solutions to speed this process up.
To apply the formula to the whole column, double-click the + sign rather than dragging it. The steps then unfold as before: Copy the formula to the entire column, then enter your formula into the top cell.
Then move the cursor to the lower right corner of the cell with the formula. It will soon turn into a + sign which you then need to double-click.
Final thoughts
Copying a formula in Excel doesn’t have to be a difficult task. In fact, Excel has a lot of tricks hidden up its sleeve to make copying formulas easier without ruining the formatting.
We hope this article was helpful, and made inputting data into your spreadsheet easier!
Find more How Tos and guides for Excel
