Excel has a wide range of useful built-in functions, but that doesn’t mean you should use them indiscriminately. One of the things you can do when Excel is lagging is examine certain formulas to ensure you’re using these functions efficiently. Even seemingly simple ones can have a significant impact on performance and cause numerous headaches.
While there are many culprits, the formulas that utilize volatile, array, and lookup functions are the most significant ones. I will explain why they may slow down your worksheets, as well as some tips on how to fix them to optimize performance, or when to avoid them completely.
Formulas with volatile functions
There’s a reason the pros use them sparingly
If you notice that your Excel sheet recalculates every time you make a change and it’s affecting performance, volatile functions are likely the culprit. Volatile functions force recalculation to keep results current. They do this even if the change doesn’t affect any of the referenced cells or their output.
A common volatile function is NOW, which returns the current date and time and is useful for scenarios such as calculating deadlines, inserting timestamps, and updating dashboards in real-time. Another one is the INDIRECT function, which uses text strings to build cell references and is useful for referencing dynamic ranges, among other things.
While they do a good job of keeping things dynamic, they should only be used when necessary. This is especially true for large models with dozens of sheets or thousands of rows. It’s also best to use non-volatile alternatives when possible (e.g., manually entering the date instead of using the NOW function).
If you can’t avoid volatile functions, and they slow down the workbook, you can turn off automatic recalculation. That way, you can trigger recalculations manually instead of when every little change occurs, but the downside is that it’s easy to forget to do it. F9 recalculates all open worksheets, and Shift + F9 recalculates only the active worksheet.
Legacy array formulas
Processing array formulas is not always so simple
You create array formulas by writing out your formula and pressing Ctrl + Shift + Enter instead of just Enter. You will know you’re successful in creating one if the formula is automatically wrapped in curly brackets.
Array formulas are particularly helpful when your calculations involve multiple values, saving you the extra step of creating helper columns. They can also return multiple results.
Array formulas are a legacy feature, especially now that Microsoft has introduced the more efficient dynamic array functions in Excel for Microsoft 365. However, they still have their uses, particularly when you want to ensure compatibility with older versions of Excel (2019 or earlier). They aren’t going away anytime soon for this reason.
Even though everything is wrapped neatly in a single line when you use array formulas, Excel will process the formula many times to achieve the results. This can be computationally expensive, especially in large Excel sheets.
To avoid array formulas, you can consider using helper columns, which spread your calculations across multiple cells. If you can’t avoid them, try not to use them with volatile functions. If compatibility isn’t an issue, you can just use the dynamic array functions where possible.
Lookup formulas
They’re convenient but can be expensive
If you need to find a particular value in an Excel sheet, the quickest way is to use a lookup formula. The most popular functions to use in these formulas are VLOOKUP and HLOOKUP, which search across columns and rows, respectively. For instance, it’s easy to find the price of a product in a vertical list using its name with the help of VLOOKUP.
The problem is that these lookup formulas can cause performance drops if they have to search large datasets, especially when the data being searched is spread across multiple sheets. It becomes a time-consuming process that can put a strain on Excel.
Unless you use the lookup functions excessively, this may not be a major problem for the Microsoft 365 version of Excel, as it creates a temporary index that speeds up repeated lookups. Also, it has a more efficient (and versatile) alternative lookup function called XLOOKUP that you can use instead.
If you must use lookup formulas in older versions of Excel, it’s more efficient to use the formula in the same sheet as the lookup array. You must also limit the lookup range to include only the necessary cells (e.g., B2:B100)—avoid referencing the whole column (e.g., B:B). Also, use an approximate instead of an exact match, since the former doesn’t need to search every cell to find the value you need.
Unlock the power of a fast Excel sheet
In my experience, these three are the biggest culprits. You should also be on the lookout for nested formulas, especially ones that contain complex conditional logic. Again, this might not be an issue for Excel for Microsoft 365, which utilizes cached indexes to expedite repeated lookups.
Besides looking into formulas, ensure you update Excel, delete any unnecessary data, and remove unused add-ins. Anything you can do to speed up your Excel sheet is one less problem to deal with. You can focus on getting work done instead of wasting more time on optimization.
