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

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:

- Select cell C4.
- Press
**F2**. Excel enters Edit mode, with the insertion point at the end of the formula. - Press
**Shift+Ctrl+Home**. Excel selects everything in the cell, back to the beginning of the formula. - Press
**Ctrl+C**to copy the selected text (the formula's text) to the Clipboard. - Press
**Enter**. Excel moves to cell C5. - Select cell D7.
- 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**.

**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!

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

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

Discover MoreIn 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."

2022-03-01 21:11:57

Dick Downey

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

Tomek

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

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

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

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 © 2024 Sharon Parq Associates, Inc.

## Comments