Written by Allen Wyatt (last updated February 26, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Many formulas in a worksheet don't occur in solitude—they often appear numerous times in a worksheet. For instance, you may copy a formula into a range of cells in a column, each formula operating on data on its own row.
When you copy formulas, Excel automatically adjusts any relative cell references in the formula so that they remain relative in the target cell. For instance, if a formula in cell C4 is =A4+B4, then copying the formula down to cell C5 results in the formula =A5+B5.
There may be times when you want to create an exact copy of a formula, without Excel adjusting the relative cell references during the copy process. If you want to copy the exact formula down by one cell, there's an easy technique: Just select the cell under the formula and then press Ctrl+' (that's an apostrophe).
If you want to make your copy to a different cell that isn't "one cell down," then a different technique is in order. Let's say you want to copy the formula from cell C4 and place it in cell D7. Follow these steps:
During this paste process, the relative cell references are not updated—the formula in cell D7 is now an exact duplicate of the one in cell C4.
Of course, there is one other thing that should be mentioned—Excel only changes cell references if the cell references are not absolute. This means you could simply change the formula in cell C4 from =A4+B4 to =$A$4+$B$4. When you copy this formula, the references are not modified by Excel at all.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6159) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Exact Formula Copies.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Got a list of data from which you want to delete duplicates? There are a couple of techniques you can use to get rid of ...
Discover MoreCreating math formulas is a particular strong point of Excel. Not all the functions that you may need are built directly ...
Discover MoreWant to know the letters assigned by Excel to a particular column? Excel normally deals with column numbers, but you can ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-03-01 21:11:57
Dick Downey
An even easier method is to select the contents in the formula bar then Ctrl/C goto the cell you want and Ctrl/V
2022-02-28 04:33:17
Gerhard Seljehammer
Hi,
Highly recommend to check out ASAP Utilities. Among lots of other very useful utilies, there is on called "Copy formulas without changing their references". Then you can copy several different formulas at the same time.
I assure I'm just a user of ASAP U. No connections to the company.
2022-02-26 14:24:26
Tomek
@Mark,
What you propose is another way to change the addresses from relative to absolute (range names are always absolute). While doing this has its benefits, it is essentially the same as what Allen suggested in the last paragraph of the tip.
@Robert's second suggestion allows to keep the relative addresses, but still copy a single cell into multiple locations, and even a block of cells into multiple locations. The first two approaches in the tip only allow copying into a single cell.
@Robert: Your first approach does work, but is too convoluted and error prone, so your second is really the way to go. The only thing to watch for is to not use characters with special meaning like "*" or "~" in your marker. "^^^" seems to be a good choice.
2022-02-26 07:39:39
Mark
Another way to handle this and create more auditable equations is to use Named Ranges.
For a simple example (don't type the quotes):
- Enter "Length:" in A1, put the cursor in B1 and in the Name Box type "Length",
- Enter "Width:" in A2, put the cursor in B2 and in the Name Box type "Width" ,
- Enter "Area = " in A3, put the cursor in B3 and in the Name Box type "=Length*Width"
Now enter values in B1 and B2 and note that B3 equals their product.
Now do a normal copy-paste of B3 to some other cell and note that there's no adjustment of cell references because of the names.
Of course if you paste B3 in either B1 or B2 you'll create a circular reference ;-).
2022-02-26 06:48:43
Robert
This works if you only want to copy a single formula but is tedious if you want to copy many formulae in a block.
There are a number of ways of doing this but I find the easiest and most reliable if you are copying a block to another single place on the same sheet is:
1. Select the block you want to copy noting the reference for the top left cell and press Ctrl+C
2. Open a new worksheet (Ctrl+N)
3. Go to the same reference as the top left cell of the block on the original (either by scrolling or by pressing F5 and entering the reference)
4. Paste the block into the new sheet and immediately cut it (Ctrl+V followed by Ctrl+X)
5. Go to the reference on the new sheet of the top left of the block you want to paste the formulae into on the original sheet (as in step 3)
6. Paste the block in and immediately copy it (Ctrl+V followed by Ctrl+C)
7. Return to the original sheet and paste the block where you want it to go (Ctrl+V).
An alternative but potentially less reliable way if you want to paste into multiple areas is:
1. Select the block you want to copy
2. Find and replace all the '=' signs in the selection to a string text that you know does not appear anywhere else on the sheet, eg '^^^'
3. Copy the block to the new locations
4. Find and replace all the replacement strings in the sheet back to equal signs
2022-02-26 05:07:13
charlie
Another easy way is to select the cell with the formula, copy its contents without the = equal sign at beginning, go to cell where you want to have the formula, select cell, enter the = and paste (ctrl+V)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments