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.
Table of Contents
- Video - How to Improve Excel Performance
- Download the PDF eBook
- Causes of Excel Performance Issues
- Large amounts of data
- Array formulas
- Nested formulas
- Volatile functions
- Lookup formulas
- Conditional functions
- Handling errors
- Defined names
- External links
- Excessive Formatting and used range
- Password Protected Structure
- User Defined Functions (UDFs)
- Outdated software
- Linked Pictures
- Resources and Diagnostic Tools
- Understanding How Excel Calculates
Watch the Video on How to Improve Excel Performance
Download eBook and Cheat Sheet
Enter your email address below to download the files.
Download the How to Improve Excel Performance eBook which contains the 16 most common causes of slow Excel files and links to tutorials on the solutions.
Download the Most Common Causes of Slow Excel Files Cheat Sheet.
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 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:
- 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:
- 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.
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.
Project Management Dashboard Dynamic Periods
Marijan Picinić
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.
Mynda Treacy
Awesome to hear, Marijan!
Tim
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).
Mynda Treacy
Thanks for the feedback, Tim! Glad you enjoy the videos. The music is likely to be a one off
Pablo
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
Mynda Treacy
Awesome, thanks for sharing, Pablo!
jim
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!
jim
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
Mynda Treacy
Yes, good suggestion, Jim. Thanks for sharing!