SUMMARY
Microsoft Excel versions 5.0 and later use the "shared formulas" method
for storing repeated formulas. If your worksheet contains many similar
formulas, this storage method may decrease the size of your file. As a
result, these files take less room on your disk and require less memory
when they are open. In addition, it may take less time for a file to open,
for a sheet to be recalculated, or for cell references to be adjusted (for
example, when you insert a row or column). Note that these file size
improvements and performance enhancements may be imperceptible if your
sheet does not contain many formulas or does not contain formulas with
repeated information.
When you open files created in versions of Microsoft Excel earlier
than 5.0, or files that are in external file formats, the formulas in
these files are converted to shared formulas. This conversion process
results in a slight increase (5 percent to 15 percent) in the amount
of time that it takes to open the file. However, once you save the
file in the Microsoft Excel 2000 file format (in the
Save As dialog box, click
Microsoft Excel Workbook (*.xls) under
Save as type), the
file will load and save faster and will require less disk space.
Example of Performance Enhancement
The following is an example of the performance enhancement provided by this feature. Note that the time it takes to load and save a file
varies, depending on the way your computer is configured.
If cells A1:CZ100 of a Microsoft Excel 4.0 worksheet contain the
following formula
=IF(B125>0.5,B124/B125,B124)
when you save this file, it requires 681,790 bytes of disk space. When
you close and reopen this file in Microsoft Excel 4.0, it takes 16
seconds to open. When you load this file in Microsoft Excel 2000, it
takes 22 seconds to load. However, when you save this file as a
Microsoft Excel 2000 Workbook, the file size shrinks to 345,600 bytes.
When you close and reopen this file, it takes 6 seconds to load.