Excel User Tips

Making an exact copy of a range of formulas

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," we 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

Here's a procedure to accomplish this task without using VBA. (contributed by Bob Umlas):

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

Note: This sounds more complicated than it actually is.