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
When you copy a cell that contains a formula, Excel adjusts all the relative cell references. Assume
that cell D1 contains this formula:
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
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
8. Press Ctrl+V to paste.
9. Choose Formulas➜Formula Auditing➜Show Formulas to toggle out of Formula view mode.