How to Improve Excel Performance

Mynda Treacy

May 2, 2023

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 on How to Improve Excel Performance

Subscribe YouTube

Download eBook and Cheat Sheet

Common causes of Slow Excel Files how to improve excel performance

Enter your email address below to download the files.

By submitting your email address you agree that we can email you our Excel newsletter.


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:

Load Power Query into Pivot Table


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.

Excel Table Styles

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 overcome 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:

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.

Set excel Calculation Mode to manual

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

* 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.

Excel Name manager showing Defined Names

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:

Empty cells in worksheet with formatting

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:

Empty cells in worksheet with formatting

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.

Excel workbook performance pane showing issues

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.

Microsoft 365 is 250% Faster* than earlier Excel versions!

*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.

Linked Pictures

Linked Pictures on their own can make your file quite large, but the real problem comes if your file contains VBA that changes cells. This triggers VBA to scan the workbook for changes that affect linked pictures, which can result in your VBA running 3-4x slower.

Solution

You can turn off the picture update or force a refresh only when needed. Both options are explained here.

Thanks to Harald for making me aware of this issue.

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

Project Management Dashboard Dynamic Periods

Excel Linear Regression

9 thoughts on “How to Improve Excel Performance”

  1. I love your site and find very useful tips.I offten use hiden pages in workbook ,and very hiden also,and hope that is not slowing work.

    Reply

  2. Love your videos, extremely educational and helpful.
    The music in this video was loud enough to make it hard to hear you (thought you’d want to know).

    Reply

  3. Hi Mynda,
    I have taken 3 of your courses, I have been reading your blog for the last 12+ years and in the last few years I have been watching your YouTube channel. It’s great content and I do appreciate all the great knowledge you share, your contributions and great work. Keep it up.
    I have been dealing with the above issues for over 12 years as well, and the users in my company keep propagating those not ‘clean’ files. It’s a lost battle, no matter how many times I tell them, they just don’t do it until the files get corrupted and the data is lost.
    The 2 major issues I normally face are: too many styles, and too many defined names, specially hidden defined names. Some files have over 60,000 of each. The most effective tool I have to combat those issues is the following macro that deletes both, I hope this helps others:

    Sub DeleteHiddenNames()
    Dim N As name
    Dim Count As Integer, Contar As Integer
    Dim Tipo1 As Integer, Tipo2 As Integer, Tipo3 As Integer
    Dim Tipo4 As Integer, Tipo5 As Integer, Tipo6 As String
    Dim Tipo7 As Integer

    On Error Resume Next
    For Each N In ActiveWorkbook.Names
    If Not N.Visible Then
    N.Delete
    Count = Count + 1
    End If

    Tipo1 = InStr(1, N.Value, “#REF”)
    Tipo2 = InStr(1, N.Value, “\\”)
    Tipo3 = InStr(1, N.Value, “%”)
    Tipo4 = InStr(1, N.Value, “‘http”)
    Tipo5 = InStr(1, N.Value, “:\”)
    Tipo6 = N.name
    Tipo7 = InStr(1, N.Value, “#N/A”)

    If Tipo1 > 1 Or Tipo2 > 1 Or Tipo3 > 1 Or Tipo4 > 1 Or Tipo5 > 1 Or Tipo7 > 1 Or Left$(Tipo6, 3) = “Nvs” Or Left$(Tipo6, 4) = “ADJ_” Then
    If Tipo6 “Print_Area” Then
    N.Delete
    Contar = Contar + 1
    End If
    End If
    Next N
    MsgBox Count & ” hidden names were deleted” & vbCr & vbCr & _
    Contar & ” names with errors were deleted”, vbInformation, “Delete Hidden Names”
    End Sub

    Sub Delete_Styles()
    Dim styT As Style, I As Long, J As Long, Resp As VbMsgBoxResult
    Dim UserId As String, Nombre As String
    UserId = Environ(“username”)
    Nombre = StrConv(Left$(UserId, InStr(1, UserId, “.”) – 1), vbProperCase)

    On Error Resume Next
    J = ActiveWorkbook.Styles.Count – 47
    If J > 29999 Then
    Resp = MsgBox(“Hi ” & Nombre & “, ” & Format(J, “#,##0″) & ” styles to be deleted! It takes approximately ” & _
    vbCr & vbCr & “1 minute for every 15,000 styles. Be patience!”, vbOKCancel + vbInformation, “Delete Custom Sytles”)
    If Resp = vbCancel Then Exit Sub
    End If
    For Each styT In ActiveWorkbook.Styles
    If Not styT.BuiltIn Then
    styT.Delete
    End If
    Next styT
    MsgBox Nombre & “, just as FYI ” & Format(J, “#,##0″) & ” styles were deleted!”, vbInformation, “Delete Custom Sytles”
    End Sub

    Reply

    • Hidden Defined Names – why, just why?
      I’ve come across both Style bloat, again in the order of many tens of thousands, and invalid Defined Names linking to inaccessible or long-dead files on someone else’s computer (default Table formulae can do this too, even when they’re not visible) – never just copy a sheet from one workbook to another!

      Reply

  4. Excessive conditional formatting can be a real killer, as this is volatile too
    Copy and pasting often proliferates and confuses this (as well as creating links to other workbooks)
    Even worse if you don’t click the “stop if true” option when possible

    Reply

Leave a Comment

Current ye@r *