Reducing the Size of a Workbook Using a Single Formula

Sometimes large Microsoft® Excel® workbooks can be slow which can cause frustration.  To reduce the size of a workbook, consider replacing several formulas with a single array formula.  Array formulas can perform multiple calculations and they also ensure consistency which can lead to greater accuracy.  Furthermore array formulas provide security since a cell within an array formula data range cannot be deleted.
Note: Download the sample workbook to practice this exercise.
Applies To: Microsoft Excel 2003, 2007, 2010 and 2013
1. Select the data range D2:D21.
2. Type the formula =B2:B21*C2:C21 in the formula bar.
Note: The data range D2:D21 should remain highlighted as you enter the formula in the formula bar.
Refer to the screen shot below:

3. Press CTRL + SHIFT + ENTER.
The result will be as below:

4. When you select any cell within the data range D2:D21, you will see the same formula {=B2:B21*C2:C21}.
  • There is only a single formula in the entire data range instead of 19 different formulas; which would have been the case had we multiplied B2*C2 and copied the formula down.
  • The file size is thus reduced and data processing in the workbook will be faster.
5. Select any cell within the data range D2:D21.
6. Press Delete.A message dialog like the below will be displayed:

7. For normal formulas any cells within the data range D2:D21 can be deleted.
8. To delete an array formula, the entire data range will have to be selected and then you press the ‘Delete’ key.
9. To edit the formula, select the data range D2:D21. Edit from the formula bar then press CTRL + SHIFT + ENTER.

No comments: