Saving Memory by Using Array Formulas
An array formula is one that constructs the same type of calculation in an entire range of cells in a data table at the time you create it. As a result, you don’t go through the normal process of first constructing a master formula and then copying to all the other cells in the table that need to perform the same type of calculation. Array formulas offer two distinct advantages over normal formulas:
- Array formulas enable you to create all the duplicate formulas needed in the table in a single operation.
- Array formulas require a lot less computer memory to store than individual copies of a single master formula.
It is actually the latter, memory-related benefit that makes array formulas so useful. By building array formulas, you can save substantial amounts of computer memory (remember that Excel must be able to load every bit of data in a workbook into memory in order to be able to open the file at all - it does not perform like other programs that can load just parts of a document into memory, switching them out as you work). This memory savings is important in really large workbooks as it enables you to add more data to the spreadsheet as well as keeps Excel from completely bogging down when you open spreadsheets for editing or printing.