Microsoft Excel is a powerful tool, but with large amounts of data or complex calculations, it can sometimes become slow. The following guide will help you understand how to use optimise Excel or better performance.
Remove duplicated, repeated, and unnecessary calculations
Look for duplicated, repeated, and unnecessary calculations, and figure out approximately how many cell references and calculations are required for Excel to calculate the result for this obstruction. Think how you might obtain the same result with fewer references and calculations.
Usually this involves one or more of the following steps:
- Reduce the number of references in each formula.
- Move the repeated calculations to one or more helper cells, and then reference the helper cells from the original formulas.
- Use additional rows and columns to calculate and store intermediate results once so that you can reuse them in other formulas.
Use the most efficient function possible
When you find an obstruction that involves a function or array formulas, determine whether there is a more efficient way to achieve the same result.
For example:
- Lookups on sorted data can be tens or hundreds of times more efficient than lookups on unsorted data.
- Minimize the number of used cells in functions like SUM and SUMIF.
- Calculation time is proportional to the number of used cells (unused cells are ignored).
Avoid links between workbooks
Avoid inter-workbook links when it is possible; they can be slow, easily broken, and not always easy to find and fix. Using fewer larger workbooks is usually, but not always, better than using many smaller workbooks. Some exceptions to this might be when you have many front-end calculations that are so rarely recalculated that it makes sense to put them in a separate workbook, or when you have insufficient RAM (memory). Try to use simple direct cell references that work on closed workbooks. By doing this, you can avoid recalculating all your linked workbooks when you recalculate any workbook. Also, you can see the values Excel has read from the closed workbook, which is frequently important for debugging and auditing the workbook.
Minimize links between worksheets
Using many worksheets can make your workbook easier to use, but generally it is slower to calculate references to other worksheets than references within worksheets.
Minimize the used range
To save memory and reduce file size, Excel tries to store information about only the area on a worksheet that was used. This is called the used range (e.g., 'SUM(A:A)'). Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. This can cause performance obstructions and file-size obstructions. You can check the visible used range on a worksheet by using Ctrl+End. Where this is excessive, you should consider deleting all the rows and columns below and to the right of your real last used cell, and then saving the workbook.
Reduce volatile functions
Volatile functions can slow recalculation because they increase the number of formulas that must be recalculated at each calculation. You can often reduce the number of volatile functions by using INDEX instead of OFFSET, and CHOOSE instead of INDIRECT. However, OFFSET is a fast function and can often be used in creative ways that give fast calculation.
Conditional formats and data validation
Conditional formats and data validation are great, but using a lot of them can significantly slow down calculation. If the cell is displayed, every conditional format formula is evaluated at each calculation and when the display of the cell that contains the conditional format is refreshed.
Excel performance - Tips for optimizing performance obstructions | Microsoft Learn