Excel is a fantastic spreadsheet tool that has evolved beautifully over the years. There are so many amazing features and functionalities that are continuously being improved and added to Excel.
But despite so much improvement, one pain point that continues to plague most Excel users are Slow Excel spreadsheets.
It’s so annoying watching Excel slog through with the calculations while you see a small moving circle – indicating that something is going on in the backend and you should wait.
If you’re on a deadline trying to complete something urgent in Excel, getting stuck with a slow Excel workbook is a nightmare.
In this article, I will explain what makes Excel slow, and more importantly, what can you do to make sure that you have a fast Excel file.
11 Ways to Speed Up Slow Excel
If you are experiencing a slow Excel workbook, here are some tips that you can use to improve its performance.
In some cases, there could be more than one issue that could be slowing down your Excel file. So make sure you go through all these tips, and see which one can be applied in your case.
Expert Advice: Do make sure to create a copy of your Excel file, as a slow Excel workbook is very close to crashing (which is another nightmare at a completely different level)
Change Formula Calculation to Manual
If you’re using a lot of formulas in Excel, adding any new formula or making changes to any old one could trigger a recalculation of all the existing formulas – making your Excel file extremely slow.
One quick fix for this problem is to stop Excel from re-calculating the formulas again and again on its own and take control by changing the calculation mode from Automatic to Manual.
By default, the calculation method in Excel is automatic – which means that whenever you make a change in Excel (such as entering any data/formula or modifying any formula), Excel recalculates all the formulas.
When you change the calculation method to Manual, Excel will stop doing the calculations automatically.
And if you need to recalculate all the formulas in the worksheet/workbook, you will have to manually force it by hitting the F9 key.
Below are the steps to change the calculation mode from automatic to manual:
- Click the Formula tab
- In the Formula editing group, click on the ‘Calculation Options’
- Click on ‘Manual’
If you want to change the calculation mode from Manual to Automatic, follow the same steps above (and select Automatic in Step 3)
Remove Unused Formulas (Convert to Values)
If you have formulas in your Excel file that you do not need anymore, it is best to remove these formulas by converting them into values.
This way, while you will still have the values in the cells, the formulas would be removed thereby speeding up your Excel File.
This happens because now Excel needs to recalculate less number of formulas, so it can do the processing faster.
Below are the steps to remove the formulas and convert these into static values:
- Select all the cells that have the formulas (in the below example, I have a formula in column B)
- Copy these cells (you can use the keyboard shortcut Control + V or Command + V, or right-click on any of the selected cells and then click on Copy)
- Right click on any of the selected cells
- Click on the ‘Paste Values’ icon (the one that has 123 written)
The above steps would remove all the formulas while still keeping the values.
Avoid Using Volatile Formulas
Excel has a certain set of functions that are called ‘Volatile’ functions.
These are called volatile because these would update every time there is a change in the workbook (such as entering the data/formula in any of the cells in excel or modifying any of the existing formulas)
Examples of these functions include TODAY(), NOW(), RAND(), RANDBETWEEN(), etc.
While a few volatile formulas will not have a significant impact on your Excel file, if you’re using a lot of these or you already have a file that has a lot of data or formulas, having volatile formulas could be quite damaging to the speed.
So if you don’t absolutely need them as a part of your Excel file, it’s best to remove them.
Remove Formatting (Conditional Formatting) You Don’t Need
For small datasets, adding formatting and Conditional Formatting rules is not going to make your Excel file run slow.
But if you have a large Excel file with multiple tabs or large datasets in it, applying formatting to these datasets could make your Excel file slow.
You got to be especially careful with Conditional Formatting.
Conditional Formatting is volatile, which means that it is re-calculated and applied every time there is a change in the Excel file.
Again, a few Conditional Formatting rules on a small data set is not going to add any considerable overhead to your file, but multiple rules on large datasets would.
So if your Excel file is running slow, and you’re using Conditional Formatting on a large data set, try removing Conditional Formatting completely (or remove as many rules as you can from it).
To remove Conditional Formatting, follow the below steps:
- Select the cells from which we want to remove Conditional Formatting
- Click the Home tab
- In the Styles group, click on the ‘Conditional Formatting’ icon
- Click on ‘Clear Rules’
- Click on ‘Clear rules from Selected Cells’
This would remove all the Conditional Formatting rules from the selected cells. If you want to remove it from the entire sheet, choose the ‘Clear Rules from Entire Sheet’ option.
In case you do not want to remove Conditional Formatting completely, and only want to remove some of the rules, click on ‘Manage Rules’ in Step 4. This will open the Rules Manager dialog box where you can edit/delete the rules.
Avoid Using Array Formulas
Array formulas in Excel are quite helpful in getting some complex stuff done. They allow you to analyze a large amount of data inside a single formula.
While array formulas have their place, one downside is that it has the potential to slow down your Excel file. Since you’re trying to analyze multiple cells or rows/columns inside a single formula, it needs time to process it all.
So if you want to speed up your Excel file, identify array formulas that you do not need, or if there is a possibility of replacing these array formulas with regular formulas or features such as Pivot Table or Power Query.
Note: Excel has made great strides in speeding up the tool, I have also come up with dynamic arrays that make it really fast for formulas to use arrays within it. Despite this, array formulas still have the potential to slow down Excel. So use them wisely
Pro Tip: Avoid referring to the entire row or column in formulas. For example, if your data is in the range A2:A10, then it’s better to use this exact range instead of using A:A (which would consider the entire column for calculation). While this may not have a significant impact on speeding up Excel, it’s these small things that can eventually add up and matter.
Reducing the Used Range Area by Removing Unused Cells
Excel has an inbuilt memory that remembers the cells you have used.
Even when you delete the data from the cells that you have used, Excel would still remember it and designate it as the ‘Used Range’
For example, if you have some data in A1:R1000, and then you delete this data so that these cells are empty, Excel would continue to have this range in the back end as the ‘Used Range’.
This unnecessary usage of Excel memory can increase the file size and also slow down your Excel.
To check the used range, hold the Control key and press the End key. Your cursor will be taken to the last cell in the used range.
if you have already deleted the data and you find a lot of cells that are still considered a part of the used range, you can get rid of it by deleting these additional rows and columns.
Disable Unnecessary Add-ins
Add-ins are quite useful, but they would definitely slow down your Excel File.
This is because adding an add-in means adding extra code to your Excel file.
While it would give you some additional functions and functionalities, it would come at a cost of slowing down your Excel.
So if you’re someone who uses a lot of third-party add-ins (or if you have enabled some of Excel’s own inbuilt add-ins), you can speed up your Excel file by disabling these.
You can still keep these add-ins and enable these in the future if you need them, but if you keep them enabled all the time, it is surely going to have an impact on the speed of your Excel file.
Below are the steps to disable add-ins in Excel:
- Click the File tab in the ribbon
- Click in Options
- In the Excel Options dialog box, click on Add-ins
- Click on the ‘Manage’ drop-down at the bottom of the dialog box, and select Excel Add-ins (if not selected already)
- Click on Go
- In the Add-ins dialog box, uncheck the add-ins you want to disable
- Click on OK
The above steps would disable all the add-ons that you selected in step 6. You can also go and check for the presence of add-ins in different options in Step 4 (in the ‘Manage’ drop-down options).
Remove Pivot Tables You Don’t Need
If you use Pivot Tables when summarizing data, you need to also know about Pivot Cache.
Whenever you create a Pivot Table using a data set, Excel automatically creates something called the Pivot Cache.
This Pivot Cache holds a copy of the data, and it is this data in the cache that is used to create the pivot table.
So while you may think that your Pivot Table is actually connected to your data in the Excel worksheet, it is actually connected to the Pivot Cache.
This is done to optimize the performance and make Pivot Tables fast.
While it does make your Pivot Tables fast, one downside of having multiple Pivot Tables in an Excel file is that multiple Pivot Cache would increase the file size and can slow down your Excel files.
So if you have some additional Pivot Tables that you are not using, it would be best to delete them – as it would also delete the pivot cache and reduce the size of your Excel file.
This would also help you speed up your Excel workbooks
If Using Images, Reduce the Size
If you have added images in your excel file, there is a possibility that these images are large and are leading to a speed issue.
To handle this issue, the first step would be to audit your Excel file and see if you absolutely need all the images or not. If there is something we can get rid of, do that.
If you still have images that are large and are slowing down your excel, one workaround would be to compress these images and make them lighter and smaller in size.
There are many free and paid options available online, but the one I like the best is called Tiny PNG. You can simply upload any image file and this will compress it for you (for free)
Replace Complex Formulas with Simpler Ones
A lot of times, you’re working with Excel files that were created a long time ago by you or by some of your coworkers/colleagues.
Over time, there could be many different formulas that have been added that is causing your Excel to run slow.
If there is a possibility to audit the file and replace some of the complex formulas with simpler ones try and do that.
Check if there are any new Excel functions that can be used instead of the old ones. For example, instead of using multiple IF and nested IF functions, see if you could use the IFS function.
There are also new formulas in Excel such as the FILTER function or the SORT function, that can now do things that were earlier used to take a complex workaround.
Give Power Query (Get and Transform) a Chance
If you work with large datasets, there’s a higher probability of you dealing with slow Excel spreadsheets.
In the last few years, Excel has been hard at work in improving the Power Query tool (now called Get and Transform).
While the main purpose of Power Query is not to help you speed up Excel, it is a great tool for helping you deal with large datasets and save you time when trying to analyze and transform large data.
While Excel would start giving you trouble as soon as the number of records in your data set exceeds a few thousand, you can easily manage millions of rows of data with Power Query.
If you haven’t used Power Query yet, give it a shot. It’s quite easy to learn (here’s a free online course on Power Query).
I guarantee you wouldn’t go back to the old ways of doing stuff in Excel when you learn Power Query
Slow System = Slow Excel
At the end of the day, there is only so much optimization you can do.
While these tricks would help you speed up Excel, sometimes, there’s just too much data and formulas making it really hard to speed up your Excel files.
But there’s one thing you need to know about how Excel works – it’s a desktop application that uses the processing power of your computer (unless you’re using Excel on the Web).
So if you’re using an old laptop with low ram and an outdated processor, no matter how hard you try, your Excel would not be fast enough.
But if you have a decent enough laptop with a good ram and processor, your Excel files would be able to do a lot more work in less time.
In a nutshell, if you have a slow computer, you most likely have slow Excel files. and if you upgrade your computer and add more power to it, your Excel files would automatically be faster.
In this article, I have given you 10 tricks that you can use to speed up your excel and get rid of slow Excel spreadsheets forever. Sometimes one of these tips will do the trick for you, and sometimes you may have to Use a combination of these tips.
I wish you ‘fast Excel’ days ahead.
Other Excel tutorials you may also find helpful: