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:
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 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.
Search JabSto ::

Custom Search