Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Exact Formula Copies.

Exact Formula Copies

Written by Allen Wyatt (last updated February 26, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


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:

  1. Select cell C4.
  2. Press F2. Excel enters Edit mode, with the insertion point at the end of the formula.
  3. Press Shift+Ctrl+Home. Excel selects everything in the cell, back to the beginning of the formula.
  4. Press Ctrl+C to copy the selected text (the formula's text) to the Clipboard.
  5. Press Enter. Excel moves to cell C5.
  6. Select cell D7.
  7. Press Ctrl+V to paste the Clipboard contents into the cell.

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, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Exact Formula Copies.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Cleaning Up Text in a Macro

Need to remove extraneous characters from a text string? VBA makes it easy through the CleanString method, described in ...

Discover More

Inadvertantly Getting Rid of Frozen Panes

Excel provides quite a bit of flexibility in displaying your data. You can have multiple windows visible for the same ...

Discover More

Changing Lots of Title Properties

Word maintains a collection of descriptive properties for each document you create. One of these properties is the Title ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Pulling Initial Letters from a String

When working with names or a different series of words, you may need to pull the initial letters from each word in the ...

Discover More

Counting Non-Blank Cells

Need to count the number of cells in a range that are not blank? You can use the COUNTA function of a more complex ...

Discover More

Returning the Smallest Non-Zero Value

In a series of values, you may need to know the smallest value that isn't a zero. There is no built-in function to do ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 7?

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

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


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


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


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


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)

This Site

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.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.