We often find ourselves working with Microsoft® Excel® workbooks that contain large amounts of data, causing them to process instructions slowly. To reduce the size of an Excel workbook, consider replacing several formulas with a single array formula.
Array formulas can perform multiple calculations and ensure consistency which can lead to better accuracy in your data. In today’s tip we explain how this can be done.
You are welcome to download the workbook to practice.
NB: Applies to, Microsoft® Excel® 2010, 2013 and 2016.
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.
3. Press CTRL + SHIFT + ENTER.
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. Then press Delete. A message dialog will be displayed:
6. For normal formulas, any cells within the data range D2:D21 can be deleted.
7. To delete an array formula, the entire data range will have to be selected and then you press the ‘Delete’ key.
8. To edit the formula, select the data range D2:D21. Edit from the formula bar then press CTRL + SHIFT + ENTER.
By using a single array formula you will ensure that the formulas in your workbook is protected, because arrays provide security by ensuring that data is not easily deleted.
