Exact Formula Copies

An even easier method is to select the contents in the formula bar then Ctrl/C goto the cell you want and Ctrl/V

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.

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

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

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

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)