MAKING AN EXCAT COPY OF A RANGE OF FORMULAS IN EXCEL

Assume that A1:D10 on Sheet1 has a range of cells that contain formulas. Furthermore, assume that you want to make an exact copy of these formulas, beginning in cell A11 on Sheet1.  By "exact," I mean a perfect replica -- the original cell references should not change.

If the formulas contain only absolute cell references, it's a piece of cake. Just use the standard copy/paste commands. But if the formulas contain relative or mixed references, the standard copy/paste technique won't work because the relative and mixed references will be adjusted when the range is pasted.

If you're a VBA programmer, you can simply execute the following code:

With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

Following are step-by-step instructions to accomplish this task without using VBA (contributed by Bob Umlas):

1. Select the source range (A1:D10 in this example).
2. Group the source sheet with another empty sheet (say Sheet2). To do this, press Ctrl while you click the sheet tab for Sheet2
3. Select Edit - Fill - Across worksheets (choose the All option in the dialog box).
4. Ungroup the sheets (click the sheet tab for Sheet2)
5. In Sheet2, the copied range will be selected. Choose Edit - Cut.
6. Activate cell A11 (in Sheet2) and press Enter to paste the cut cells. A11.D20 will be selected.
7. Re-group the sheets. Press Ctl and click the sheet tab for Sheet1
8. Once again, use Edit - Fill - Across worksheets.
9. Activate Sheet1, and you'll find that A11:D20 contains an exact replica of the formulas in A1:D10.

Note: For another method of performing this task, see

When you copy a range of formulas and paste them to a new location, Excel adjusts the cell references automatically. Most of the time, this is exactly what you want. Consider this simple formula:

=SUM(A2:A13)

If you copy this formula and paste it to the next column, the references are adjusted and the pasted formula is:

=SUM(B2:B13)

Making an exact copy of a single formula is easy: Press F2, highlight the formula, and press Ctrl+C to copy it as text. Then paste it to another cell. In some situations, however, you might need to make an exact copy of a range of formulas. In an older tip, I described a rather complicated way to do this. See Making An Exact Copy Of A Range Of Formulas.

Matthew D. Healy saw that tip and shared another method, which uses Notepad. Here's how it works:

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.
3. Press Ctrl+C
4. Start Windows Notepad
5. Press Ctrl+V to past the copied data into Notepad
6. In Notepad, press Ctrl+A followed by Ctrl+C to copy the text
7. Activate Excel and activate the upper left cell where you want to paste the formulas. And, make sure that the sheet you are copying to is in formula view mode.
8. Press Ctrl+V to paste.
9. Press Ctrl+` to toggle out of formula view mode.

Note: If the paste operation back to Excel doesn't work correctly, chances are that you've used Excel's Text-to-Columns feature recently, and Excel is trying to be helpful by remembering how you last parsed your data. You need to fire up the Convert Text to Columns Wizard. Choose the Delimited option and click Next. Clear all of the Delimiter option checkmarks except Tab.