Microsoft Office Tutorials and References

In Depth Information

**Making an Exact Copy of a Range of Formulas**

Making an Exact Copy of a Range of Formulas

When you copy a cell that contains a formula, Excel adjusts all the relative cell references.

Assume that cell D1 contains this formula:

=A1*B1

When you copy this cell, the two cell references are changed relative to the destination. If you

copy D1 to D12, for example, the copied formula is

=A12*B12

Sometimes, you might prefer to make an exact copy of a formula. One way is to convert all the

cell references to absolute references (for example, change =A1*B1 to =$A$1*$B$1). Another way

is to (temporarily) remove the equal sign from the formula, which converts the formula to text.

Then you can copy the cell and manually insert the equal sign into the original formula and the

copied formula.

What if you have a large range of formulas and you want to make an exact copy of those

formulas? Editing each formula is tedious and error-prone. Here’s a way to accomplish the task. It uses

Windows Notepad, but any text editor (including Microsoft Word) will do.

For the following steps, assume that you want to copy the formulas in A1:D10 on Sheet1 and

make an exact copy in A13:D22, also on Sheet1:

1.
Put Excel in Formula view mode.

The easiest way to do this is to press Ctrl+` (that character is a backwards apostrophe

and is usually on the same key that has the ~ [tilde]).

2.
Select the range to copy.

In this case, it’s A1:D10 on Sheet1.

3.
Press Ctrl+C to copy the range.

4.
Start Windows Notepad.

5.
Press Ctrl+V to paste the copied data into Notepad.

6.
In Notepad, press Ctrl+A to select all of the text, followed by Ctrl+C to copy the text.

7.
Activate Excel, activate the upper left cell where you want to paste the formulas (in this

example, A13 on Sheet2), and make sure that the sheet you’re copying to is in Formula

view mode.