XL2000: Copying and Pasting Noncontiguous Range Loses Formulas

When you copy and paste cells, copied formulas are pasted as static values; the formulas are not copied.

This problem occurs when you copy cells that are not one contiguous range of cells. For example, you select the range A1:A5, C1:C5, copy the cells, and paste them all as one block in cell D1.

To copy cells and retain their formulas, use either of the following methods.

Method 1: Paste Special Formulas

Instead of using the Paste command, use the Paste Special command on the Edit menu, and click Formulas under the Paste options.

NOTE: This method will not copy any of the original formatting. To apply formatting, with the target cells still selected, click Paste Special on the Edit menu, and click Formatting under the Paste options.

Method 2: Copy Each Range Separately

Copy blocks of cells that are contiguous. For example, if you need to copy cells A1:A5 and C1:C5 to cell D1, follow these steps:

1. Select A1:A5 and click Copy.
2. Click cell D1 and click Paste.
3. Select C1:C5 and click Copy.
4. Click cell E1 and click Paste.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

You can copy and paste nonadjacent cells of the same size in a single operation, by holding down CTRL as you click cells. For example, you can copy the ranges A1:A5 and C1:C5 in one operation, but not the ranges A1:A5 and C1:C4, because both contiguous blocks of cells must contain the same number of rows and columns.

However, if you do copy noncontiguous blocks of cells that contain formulas, Excel converts these formulas to static values when pasting them to the destination cells. If you need to copy ranges of cells that are not contiguous blocks of cells, copy and paste each contiguous block separately.