XL2002: File Size Increases After You Paste a Formula in a Large Cell Range

When you copy and paste a formula in a large range of cells in your Microsoft Excel workbook, the file size of your Excel workbook may be larger than you expect.

This behavior occurs when you first save your Excel workbook because Microsoft Excel does not convert a formula that is used repeatedly in multiple cells to a shared formula when you save your Excel workbook.

To work around this behavior, follow these steps:

1. On the File menu, click Close to close your Excel workbook.
2. On the File menu, click Open to open your Excel workbook.
3. On the File menu, click Properties.
4. On the General tab, note the size of your Excel workbook.
5. Click OK to close the Properties dialog box.
6. On the File menu, click Save.
7. On the File menu, click Properties.
8. On the General tab, note the size of your Excel workbook. Your Excel workbook should be a smaller file size.
9. Click OK to close the Properties dialog box.

If you paste a formula in a large range of cells and save your Excel workbook, the formula is saved as part of each cell in the range. When you save your Excel workbook, Excel does not convert each saved formula to a shared formula. As a result, the file size of your Excel workbook may grow to an unexpected file size.

When you close and reopen your Excel workbook, Excel converts formulas that are repeated in your Excel workbook to a shared formula. That is, instead of storing (saving) the formula with each cell, the formula is stored one time in the Excel workbook, and then shared with each of the cells that require the formula. As a result, the overall file size of your Excel workbook is reduced.

Note To determine the file size of your Excel workbook, open the Excel workbook, and then click Properties on the File menu. The size of your Excel workbook is listed on the General tab.