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


6

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. ...

MORE FROM ALLEN

Deleting Everything Except Formulas

Need to get rid of everything in a worksheet except the formulas? It's easier to make this huge change than you think it is.

Discover More

Shortcut to Merge Cells

Need to merge a bunch of cells together on a regular basis? You'll love the two macros in this tip which can make short ...

Discover More

Printing a Portion of a Worksheet

Need to print a portion of a worksheet, but don't want to waste paper by printing the whole thing? It's easy to print ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Putting Amounts in Month Columns

Sometimes putting together a formula can be a challenge. Part of the process, though, is examining the data with which ...

Discover More

Converting from Relative to Absolute

Addresses used in a formula can be either relative or absolute. If you need to switch between the two types of ...

Discover More

Extracting Street Numbers from an Address

Want to know how to move pieces of information contained in one cell into individual cells? This option exists if using ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 2 + 8?

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)


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
Subscribe

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.