Dealing with extended calculation times in financial modeling is a common issue for modelers working with intricate models. In such cases, it is prudent to thoroughly review your model and focus on optimizing its calculation speed prior to presenting it to clients.

This blog outlines the seven most effective strategies to enhance the calculation speed of your Excel workbooks:

1. Avoid Volatile Functions:

Volatile functions such as INDIRECT, OFFSET, NOW, and TODAY recalculate whenever any change is made to the worksheet, potentially slowing down calculations. Minimize their usage where possible.

2. Write Efficient Formulas:

Choosing more efficient formulas whenever possible. For example, use SUMIFS instead of SUMPRODUCT for multiple criteria summation, or employ INDEX/MATCH instead of VLOOKUP for faster lookups.

3. Limit the Use of Array Formulas:

Extensive use of complex and large array formulas can increase the size and calculation time of your Excel workbook, as they need to recalculate each cell in the selected range if any change is made in the selected cells or their precedent cells. Therefore, it is advisable to minimize the use of large ranges in array formula calculations.

4. Check for the End Cells of Each Worksheet in Your Workbook:

To save memory and reduce file size, Excel attempts to store information about only the area on a worksheet that was used, known as the used range. Sometimes, various editing and formatting operations extend the used range significantly beyond the range you would currently consider used. This can cause performance obstructions and increase file size. You can check the visible used range on a worksheet by using the keyboard shortcut ‘Ctrl + End’. If this range is excessive, consider deleting all the rows and columns below and to the right of your actual last used cell, and then saving the workbook.

5. Avoid Extensive Use of Conditional Formatting and Data Validation:

While conditional formatting and data validation are valuable tools, excessive usage can significantly impede calculation speed. When a cell is visible, each conditional format formula is evaluated during every calculation cycle and whenever the cell’s display containing the conditional format is refreshed.

6. Handle Data Ranges When Adding or Deleting Data Frequently:

Many modelers use Excel Tables to dynamically adjust formula ranges as new rows or columns are inserted or removed. Alternatively, utilizing whole column and row references (e.g., $A:$A) in formulas can be effective. Certain Excel built-in functions such as SUM and SUMIF efficiently handle whole column references by automatically identifying the last used row. However, array calculation functions like SUMPRODUCT either cannot handle whole column references or calculate all the cells in the column. At FAB Analytics, we address this issue by incorporating an additional row or column, termed the 'Stretch bar,' at the end of our formula range. Any new row or column is inserted before this stretch bar, ensuring the selected range adjusts automatically, eliminating the need for unnecessary range inclusion in our formulas.

7. Split Large Models into Smaller Ones:

If your model is excessively large and complex, consider breaking it down into smaller, more manageable pieces. This can reduce calculation time and make the model easier to work with. For instance, create one model for all the back-end calculations, which are usually of low importance to the client, and another for all the reports and financial statements.

At FAB Analytics, we ensure to follow all these techniques while designing the model. We meticulously test our calculation logic and model structure to ensure the creation of highly efficient models that complete calculations within 1-2 seconds.