Home > Microsoft Office > Excel

How To Copy A Formula In Excel

Kevin Pocock
Last Updated on

Reviewed By: Kevin Pocock

How To Copy A Formula In Excel
PC Guide is reader-supported. When you buy through links on our site, we may earn an affiliate commission. Read More

Table of Contents

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.

Prime Day is finally here! Find all the biggest tech and PC deals below.

*Prices and savings subject to change. Click through to get the current prices.

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. 

1

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

1

Enter A Formula Into The Top Cell

Enter a formula into the top cell.

Step

2

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

3

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

4

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

About the Author

Kevin Pocock

Kevin has a broad interest and enthusiasm for consumer electronics, PCs, and all things consumer tech. And more than 17 years experience in tech journalism.