Hello it is #formulafriday again, today let’s look at how to make an EXACT copy of an Excel Formula. So, if you ever needed to make an exact copy of an Excel formula it can be a bit tricky. Or, sometimes give unexpected results. What do I mean by that?. Well, when you copy of formula and paste it to a new range of cells, like another location in your worksheet or workbook, Excel is really helpful. All of the relative references are changed to reflect the new location of the formula. This would be the expected behaviour of Excel. However, sometimes you want to move the formula to another location on the worksheet but do not want to change the cells references. In this tutorial, I show you how to make a copy of a formula without having to retype the formula with the cells references you want.
Let’s Work Through An Example.
My example formula is in the screen grab below. I have the following formula which sums cells A1 to A4.
=SUM(A1:A4)
When I copy the formula to Column C, the formula is adjusted accordingly by Excel. Thanks Excel but in the instance it is not exactly what I wanted to happen. I just wanted to change the location of the formula on the worksheet, not the cells the formula refers to.
The formula copies with the cells references below.
=SUM(C1:C4)
Fortunately, there is a solution to the auto adjusted cell referecenes. So, if you do not want this helpful assumption by Excel then it is easy to make a copy without these adjustments. Simply follow the steps below.
How To Copy Your Excel Formula.
- Hit F2 to enter the Edit mode for formulas.
- Next, highlight the formula the hit CTL+C to copy the formula as text from the formula bar.
- Exit out of Edit mode by hitting the ESC key
- Go ahead and paste it into the new cell where you want the formula.
- That’s it you now should have correctly made an exact copy of your Excel formula.
When you exit out of Edit mode in Excel by hitting the Escape key (ESC) on your keyboard, Excel return back to regular enter mode. To confirm the current mode of Excel, simply look at the bottom left hand screen. If Excel is in Edit mode the word Edit will be displayed.
If you want to learn more about how awesome Excel is you to take a look our Recommended Excel Online
For more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on every month and receive my free Ebook, 50 Excel Tips.
Read all of my Macro Monday and Formula Friday blog posts using the links below.
Need Some Extra Help With Your Excel Formulas?
