How to Speed Up Excel Financial Models by Fixing Bottlenecks & Excel Volatile Functions

Slow spreadsheets can be frustrating, especially when they are critical financial models relied upon to support key business decisions or forecasts. If a CFO is asking for an updated scenario by lunchtime, but you’re watching the Excel calculation bar slowly creep to 100%, there’s a problem.

Understanding why Excel becomes slow or unresponsive is essential if you want to build models that are both useful and usable.

This article explores how Excel calculates, why performance issues arise, and the practical steps you can take to diagnose and resolve the underlying problems.

How Excel Calculates: Understanding Dependency Trees and Calculation Chains

Whenever you update a cell in Excel - and therefore it changes - the application must determine which other cells require recalculation and in which order. This enables Excel to return the correct result and in the quickest, most efficient way – recalculating every cell in the workbook would be inefficient. To do this, Excel relies on a dependency tree.

The dependency tree records the relationships between cells and is created in the background as you build out your financial model. Input cells (with no precedents) sit at the ‘roots’, while output cells (with no dependents) sit at the ‘leaves’. The route from an input cell to its ultimate output cell(s) is known as a calculation chain.

When an input cell changes, Excel marks every dependent cell in the relevant calculation chain(s) as a dirty cell (official Microsoft terminology!) that must be recalculated. These dirty cells are recalculated in sequence until the affected outputs are up to date.

This method is highly efficient, but it also means that the structure of your model and the formulas you use can have a significant impact on performance.

** Download our FREE Excel Modelling Tips eBook for more best practice advice and insights **

 Why Dependency Trees Matter for Model Performance

In smaller spreadsheets, knowledge of dependency trees is almost irrelevant because recalculations are typically instantaneous. But in large or complex financial models, inefficiencies or problems within the calculation chains can lead to noticeable delays, where Excel spends significant time recalculating – frustrating the user or making the model unusable.

If a single formula or group of formulas is particularly resource-intensive, it can slow down the entire recalculation process. Knowing how Excel works in the background can help you diagnose and resolve existing issues, and design faster, more reliable financial models.

Diagnosing Slow-Running Spreadsheets

Almost every business has at least one spreadsheet that users dread working with because it is slow, prone to freezing, and difficult to maintain. These performance problems are almost always caused by inefficiencies in the dependency tree.

The two most common sources of these inefficiencies are:

1. Bottleneck calculations – formulas or groups of formulas that are memory-intensive and take longer than the rest to calculate.
2. Volatile functions – functions that force Excel to recalculate more often than is necessary.

Either of these sources can lead to financial models that are unacceptably slow to work with. In combination, they can be spreadsheet killers! Understanding them is the key to diagnosing and improving model performance.

** Transform your strategic and financial modelling skills with our Excel Modeller course **

Identifying and Fixing Bottleneck Calculations

A bottleneck calculation is one or more formulas that take a disproportionate amount of time to evaluate. They typically involve complex formulas, large data ranges, or functions that are computationally heavy.

Common bottleneck examples

• Large sets of SUMIFS or SUMPRODUCT functions containing multiple criteria and referencing thousands of cells.
• Nested functions performing multiple operations on wide data ranges.

These bottlenecks create calculation delays in the affected calculation chains. Excel may calculate other chains quickly, but these areas hold up the process.

How to locate bottleneck calculations

1. Create a copy of your model to be used in the following steps.
2. Identify which inputs cause the longest calculation delays.

3. Trace which outputs ultimately depend on these inputs – they change when the inputs change.

1. Iteratively test the effect on calculation time of deleting the calculation chain backwards – starting with the outputs. Once you delete a section and see a material improvement in calculations, you will know you have deleted a bottleneck.
2. Once identified, revisit the formula design of this section. Reducing data ranges, breaking calculations into smaller steps, or using alternative functions can often resolve the issue.

This process can be time-consuming but is essential if you want to improve the performance of a large model.

** Transform your skills with our FREE eBooks designed for finance professionals, analysts, and consultants **

Understanding Excel Volatile Functions and Their Impact

Volatile functions recalculate every time Excel detects a change in the workbook, regardless of whether they are directly affected by the change. This could be the insertion of a row or entering text into a blank cell unconnected to any calculations - all volatile functions in the workbook immediately recalculate.

The volatile functions in Excel are: INDIRECT(), OFFSET(), RAND(), RANDBETWEEN(), NOW(), TODAY(), CELL(), INFO().

How volatile functions override the efficiency of the dependency tree

As we learnt earlier, when a cell changes, all dependent cells in the associated calculation chain(s) must be recalculated. Therefore, all calculations downstream of all volatile functions recalculate effectively every time anything happens in the model - overriding the efficiency of the dependency tree which aims to recalculate only the sections of the model that need it.

When volatile functions are scattered near the ‘roots’ of the dependency tree (input cells), the performance impact is amplified as larger swathes of the model are likely to be affected. For example, if a financial model has a scenario functionality where key input drivers are flexed by calculations containing the OFFSET function, given that they are ‘key’ they are likely to affect large parts of the model and as a result model performance will to suffer.

How to manage volatile functions

When a model suffers from slow performance due to volatile functions, INDIRECT() and OFFSET() are almost always the main culprits, as they are the only two typically used in model calculations.

• Avoid using them wherever possible. Adopt the mindset of volatile functions not being an acceptable component of your spreadsheet.
• Where applicable, replace volatile functions with alternative non-volatile functions, even if this requires restructuring your model or calculations.
• If unavoidable, place them closer to output cells (leaves) so fewer dependent cells are affected.

Summary & Learn More

Slow spreadsheets are not inevitable. They are almost always caused by inefficiencies in Excel’s calculation process, either from bottleneck calculations or the presence of volatile functions.

By understanding how dependency trees work, you can:

• Locate and resolve bottleneck calculations.
• Appreciate the impact of volatile functions and eliminate them.
• Design models that calculate efficiently, reducing delays and improving user experience.

Learn how to design, build, and test financial models for forecasting and strategic decision-making with our Excel financial modelling course. It leverages methods and techniques used within Big 4 modelling teams and is designed for finance professionals, analysts, and consultants.