Microsoft Office Tutorials and References

In Depth Information

**Tip 46: 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 may 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.

To toggle Formula view mode, choose Formulas➜Formula Auditing➜Show Formulas.

2.
Select the range to copy. In this case, A1:D10 on Sheet1.

3.
Press Ctrl+C to copy the range.

4.
Launch the Windows Notepad text editor.

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

8.
Press Ctrl+V to paste.

9.
Choose Formulas➜Formula Auditing➜Show Formulas to toggle out of Formula view mode.