Sometimes, large Microsoft Excel workbooks can be slow and inefficient—which could 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 for more security, since a cell within an array formula data range cannot be deleted.
In this tip, we show you how to do this.
You are welcome to download the sample workbook to practice this exercise.
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.
By doing this, you’ll be reducing the size of your workbook and save on the amount of time it takes to run it out.
The post How to reduce the size of a workbook using a single formula appeared first on Sage Intelligence.
Source: Excel on Steroids