Hi ,

How to Improve Excel Performance

Do you find yourself frustrated by sluggish Excel files that take forever to load or recalculate? If so, you're not alone. Slow Excel files can be a common headache for anyone who uses spreadsheets regularly.

Fortunately, there are many tips and tricks you can use to improve Excel performance. In this tutorial, we'll identify the most common causes of slow Excel files and how to speed them up so you can improve productivity.

Watch the Video

Download the PDF eBook

Causes of Excel Performance Issues

Large amounts of data

Too much data

One of the most obvious causes of slow Excel files is too much data, although this is rarely the sole contributor.

Solution

Be diligent and only store data in the file that is required for the task. Use Power Query to filter out the unnecessary data before loading it to the Excel file.

Duplicated data

When data is used in a PivotTable it is typically stored in the file twice, once in the worksheet and again in the Pivot Cache.

Solution

Avoid this by using Power Query to get the data and load it direct to the Pivot Cache by selecting PivotTable Report in the Import Data dialog box:

Excel Tables (Excel 2013 only)

Beware that storing large amounts of data in Excel Tables in Excel 2013 (and God forbid you're still using earlier versions) can sometimes yield worse performance than not formatting it in a table.

An unofficial cut off is >500k rows x 10 columns. This is sometimes too much to store in Excel 2013 Tables, although it can depend on whether you have a lot of formulas or not. More on formulas coming up.

Solution

If the file is slow, consider storing the data in the Power Pivot data model rather than the worksheet. Power Pivot has an advanced compression algorithm that enables it to store data more efficiently than Excel itself. Power Pivot can also store 10's of millions of rows of data and over come the row limitations of Excel itself.

Array formulas

Single cell array formulas can be processed several times, depending on the number of cells referenced in the formula.

Solutions

• Avoid mixing row and column references or overlapping array references.
• Try separating the calculation into multiple cells rather than an array formula.
• Move the calculation to Power Query. Power Query only calculates when you load the data or refresh the data, whereas formulas calculate every time something they reference changes, and in the case of volatile functions, every time ANYTHING changes.

Note: Modern dynamic array functions are more efficient than their older CTRL+SHIFT+ENTER counterparts.

Nested formulas

Nested formulas in Excel, like nested IFs can cause performance issues because they involve multiple levels of functions or calculations that require more processing power and memory.

As a result, Excel may take longer to recalculate the worksheet, which can slow down the file's performance.

Nested formulas can be especially problematic when they involve large data sets, such as when performing lookups or other operations across multiple worksheets or workbooks.

In these cases, the amount of processing power required to evaluate the formula can be significant, leading to slower performance.

Solutions

• Breaking down complex formulas into smaller, more manageable parts can help improve performance and make the worksheet easier to read and debug.
• Move the calculations to Power Query where they are calculated once on loading the data, rather than potentially multiple times during the use of the file.
• Copy and paste the formulas as values if they are not expected to change.

Volatile functions

Volatile functions in Excel can cause performance issues because they recalculate every time anything changes in the workbook, even if the change has no direct impact on the function's output.

As a result, volatile functions can consume a significant amount of processing power and memory, which can slow down the file's performance, especially for larger and more complex workbooks.

Examples of volatile functions include:

• INDIRECT
• OFFSET
• NOW
• RAND
• RANDARRAY
• RANDBETWEEN
• TODAY
• CELL (depending on arguments)
• INFO (depending on arguments)
• SUMIF (when the size of the first range is not the same as the second 'sum_range')

Solutions

• Use volatile functions sparingly and only when necessary. If possible, use non-volatile alternatives, such as INDEX instead of OFFSET for dynamic named ranges.
• Another way to improve performance is to manually calculate the workbook only when necessary, rather than allowing Excel to recalculate automatically every time a change is made. This way you only recalculate the workbook when necessary using the F9 key.

CAUTION: be sure to remember that calculation is set to manual!

Lookup formulas

Lookup formulas in Excel can cause performance issues because they often involve searching through large data sets, which can be time-consuming and resource intensive*.

Solutions

• Use Power Query to perform lookups:
  • Power Query lookup exact match
  • Power Query lookup approximate match
  • Power Query lookups using list functions
• If you must use a lookup formula, store the lookup table and the formula on the same sheet.
• Avoid using exact match lookups. Instead use approximate match with a sorted list. Be sure to only reference the cells containing the data being looked up. Including empty rows in the formula will reduce efficiency.

* Office 365 users may not experience performance issues to the same extent as those using earlier versions of Excel due to the new internal cached index Excel creates for lookup functions.

Conditional functions

Conditional functions in Excel can cause performance issues because they involve testing each cell in a given range against a set of criteria, which can be time consuming and resource intensive, especially for large data sets.

Examples of conditional functions include SUMIF/S, COUNTIF/S, AVERAGEIF/S, among others.

Note: as with the lookup functions, in Microsoft 365 these functions now create an internal cached index for the range being searched. This cached index is reused in any subsequent aggregations that are pulling from the same range.

Solutions

• Avoid selecting more cells than necessary
• Use a PivotTable PivotTables can perform these calculations and you don't need to know how to write the formula.

Defined Names

Defined names are recalculated each time a formula that refers to the name is recalculated, even if the value of a cell does not change when calculated.

Solution

Defined names are one of the most valuable Excel features, so don't avoid them because they might cause performance problems.

Instead, check if any of the other causes could be contributing and resolve them first. If you still have performance problems, then try replacing names with direct references to cells.

Handling Errors

Use the IFERROR function to handle errors returned by lookup functions. The old IF(ISNA(VLOOKUP(…),0,VLOOKUP(…)) requires Excel to do double the work i.e. two lookups. IFERROR avoids this duplication.

External links

As a general rule, external links should be avoided. Links to external Excel files are slow to calculate and easily broken. Plus, many functions cannot evaluate on a closed workbook. Internal links can also slow down calculation.

Solution

Use Power Query to bring the data into the current file. If you must use external links, open the file being linked to before opening the file doing the linking.

Excessive Formatting and Used Range

Excessive cell formatting

Including font styles, colors, borders, and other visual attributes that are applied to cells can consume a significant amount of processing power and memory, especially when applied to large data sets.

Excessive formatting can also make the file size larger, which can slow down the loading and saving times of the workbook.

Solution

Instead of applying formatting like cell fill colour to a large range of cells, apply it to the whole column/row. As you can imagine, it's easier for Excel to know that a whole row or column is formatted in a particular way than it is to keep track of 1000 separate cells.

Redundant formatting (used range)

Formatting can sometimes linger in cells unbeknownst to you. Cells can appear empty, but Excel is still storing information about those cells in memory.

If you press CTRL+END you will be taken to the last cell in the sheet that Excel is storing information for. If this isn't the end of your table, then you know you have redundant formatting.

In the image below Excel thinks the last used cell is S337, but there is no data in the cells to the left or above:

Solution

You can try deleting rows and columns that are empty, but I have found this often doesn't resolve the problem (backup the file before you do).

Thankfully, there is a new tool available in Excel Online to Microsoft 365 users that can check for performance issues like this called Optimize Sheet.

It's available on the Review tab of the ribbon via the Check Performance button:

Clicking on Check Performance brings up the Workbook Performance pane which summarizes the sheets that contain any issues.

From there you can see a list of the individual cells/ranges and the issues (see image below). Clicking Optimize all/sheet will remove them for you.

Password Protected Structure

Files with Password Protected Workbook structures will be slower to open and close than one without a password.

Solution

Given that Excel passwords can be removed easily, consider whether the password is worth the performance hit.

User Defined Functions (UDFs)

UDFs are typically less efficient than the built in Excel functions.

Solution

Consider using the built in functions, breaking them into separate calculations if required. Or write a custom function with the new LAMBDA function.

Outdated software

Many improvements to Excel's calc engine were released for Office 365 users for SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS, and their singular counterparts as well as VLOOKUP, HLOOKUP and MATCH functions.

*The improvement is dramatic: for example, calculating 1200 SUMIFS, AVERAGEIFS, and COUNTIFS formulas aggregating data from 1 million cells on a 4 core 2 GHz CPU that took 20 seconds to calculate using Excel 2010, now only takes 8 seconds in Excel M365.

Solution

Where possible update to Microsoft 365 to take advantage of new improved functions and calc efficiencies.

Also use the 64-bit version of Excel. The 32-bit version that is commonly used for compatibility with old add-ins only has 2GBs of virtual memory.

Resources and Diagnostic Tools

• Learn Power Query - save time and effort in cleaning, transforming, and analyzing data from various sources, ultimately making your data analysis more efficient and effective.
• Learn Power Pivot - analyze and visualize large amounts of data from multiple sources with ease, providing powerful insights and driving better decision-making.
• FastExcel Profiler - a comprehensive set of tools focused on finding and prioritizing calculation and VBA bottlenecks build by fellow Microsoft MVP, Charles Williams who is the master of Excel performance.

Understanding How Excel Calculates

• Excel performance - Improving calculation performance
• Excel performance - Tips for optimizing performance obstructions
• Excel Recalculation
• Multithreaded recalculation in Excel

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub