Given you are on this page, chances are you’ve seen this type of problem. Don’t put up with Excel® files running slowly and stopping you getting on with your important work. Chances are the slow file is due to a combination of three problems. All these problems are easy to fix.
Go and look at your slowest spreadsheet file (probably one that’s been used for years [maybe rolled over monthly or weekly]). Particularly if you paste stuff in to that file regularly.
I’m confident you’re going to fix it by: (you can do all these yourself or do it quicker with our file fix and speed up tool)
- Resetting the used range on each sheet
- Deleting unwanted named ranges
- Deleting unwanted cell styles
Excel® File Problems
- File runs slowly – this is self explanatory. Whenever you recalculate the file or make changes there is a delay. You don’t need any metrics to measure this other than your frustration
- Large file size – this likely goes along with the slow speed of the file
- Out of memory error – if your file is running slowly at the moment then you can expect to see this error at some point in the future.
- Too many formats error – ‘xls’ file types can have up to 4,000 format combinations, ‘xlsx’ file types can reach 64,000. This sounds like a lot but you may be surprised how many combinations you are using. Particularly if you’ve pasted from other workbooks.
- Requests to update links to obsolete files – you may have files that ask to update links when you believe there should be none. Or maybe the file has some legitimate external links but in addition there are some links to redundant files. Some people try and work round this by just avoiding link updates. That might avoid the need to click on ‘update link’ dialog boxes but will not help the file’s speed.
How did this happen?
There is a price to pay for Excel’s flexibility
There’s a reason that Excel® is the most used piece of software in Finance functions around the world. Its flexibility makes it a critical processing tool between inputs from your company’s data and the information and reports required by management.
Excel®’s flexibility does have at least one downside. Given you can do pretty much anything you can guarantee that people will do just about anything. Most of which will not impact your spreadsheet’s speed but some of it inevitably will.
Old files used for many months
The accumulation of this workbook bloat will be seen mostly in files that have been used regularly over a long period of time. I’ve seen files used for over 20 years. And of course in the Finance function this often means the file has been rolled-over 240 times. Each time there is possibility for unnecessary formats, functions or objects to be added.
Copy/paste from other files
Two of the biggest Excel spreadsheet bloat contributors are formats and named ranges. Both of these can easily be pasted in to a file. Even if you believe no one pastes formats or named ranges in to the file I would reckon over a long period there is a high probability this has happened. Particularly if entire sheets have been moved in to the file.
See what’s in an Excel file – convert to ZIP – optional step
This is an interesting exercise. Its not essential but may help you understand a little more of Excel workbooks.
Make a copy of the file you selected at the start of this post. Its very important you make this copy as the file may become corrupt.
- Rename the copy of the file. Change the Excel® file’s extension to ‘zip’. An Excel® workbook is actually a collection of files.
- Use file explorer to extract all files in the folder and view the extracted files.
- You’ll see a folder called ‘xl’. Right click this and then ‘properties’. Look at the size of the folder. It is very likely this is the bulk of your workbook’s file size.
- If you now look at the contents of the ‘xl’ folder you may see a file called ‘styles.xml’. This is the information on the format combinations used by the workbook. If you are using many formats then this will have a large file size.
- The folders ‘externalLinks’ and ‘worksheets’ may also have large files. These are used to hold information on the links and sheets.
What happens if I don’t fix these?
Continue to run slowly
Without taking action the file is not going to speed up. However, if people carry on using the file in the same way then the problem is going to get worse over time. You might use workarounds like not updating links or changing calculation to manual but at some point you can expect….
The file may become corrupt
Eventually the file is likely to become corrupt. It is likely Excel® will still be able to open the file but to do so it may remove some content. If you’ve been keeping backups of the file you may be able to go back to an earlier version of the file.
It makes much more sense to fix the slow running file now before things do get worse.
How can I fix these issues?
1. Reset used ranges
If you did the ‘zip’ file analysis earlier and you saw a file in the ‘worksheets’ folder that seemed large (relative to the overall size of the workbook) then resetting the used range of each sheet may help speed up the file.
Create a backup of your file before proceeding.
Start by unhiding all the sheets in your file. You can do this by right clicking one of the sheet tabs and clicking <unhide> (the option will be grayed out if there are no hidden sheets).
Note: once you’ve unhidden all sheets click on <File>. Look at the ‘Prepare for Sharing’ section and make sure it does not say there are still hidden sheets. If it still says there are hidden sheets then you’ll need to go in to the Visual Basic Editor (<ALT>+<F11>) and unhide ‘VeryHidden’ sheets.
In each of the sheets in your file:
- Hold down the <CTRL> button on the keyboard and press the <End> button. This is the last used cell (farthest right and down) in the sheet. If this looks lower or further right than you expect then follow the rest of these steps to delete unwanted rows and columns. Otherwise go on to the next sheet.
- If the last cell is too low then go to a cell one row below where you expect the bottom of your used cells. Now hold down <Shift> and <Ctrl>. With these held down press <end>. You should now have a region highlighted from the bottom of your data to the bottom of Excel®’s used range.
- Click the right mouse button on this range and select <Delete…>. Click <Entire row> and OK.
- If the last cell from step 1. is too far right then click in a cell one column to the right of your used cells. Hold down <Ctrl> and <Shift> then press <End>. Right mouse button click in the highlighted region and click <Delete…>. Then click <Entire column> and click <OK>.
Once you’ve completed all the sheets in the file you can progress to the next step.
2. Delete obsolete ‘Named Ranges’
In the <Formulas> ribbon, click on <Name Manager>.
Are there any named ranges there that are not needed by your spreadsheet? You’ll often find thousands of them in old workbooks (particularly if you use BPC or other software that relies on named ranges to function).
You can sort them by clicking on the field headings. And use the ‘Filter’ dropdown to help analyze them. Highlight named ranges that are not needed and click <Delete> to remove them. Use this method to find and delete named ranges that contain errors.
Check the named ranges that are linking to external files. Sort by ‘Refers To’ and you can analyze by file.
There is a comprehensive guide here about breaking links: Excel Cannot Break Link – The Ultimate Guide. You will want to particularly check step 3. “Delete Named Ranges to External Files”.
The AIRT add-in (click on Downloads above) fix and speed up tools can remove the unwanted named ranges and you can use the external links report to show all the cells that have external links.
Once you’ve completed that you can go on to the next step to remove unnecessary styles.
3. Delete ‘Custom Styles’
If you’ve seen a ‘too many formats’ warning or checked the file size of the ‘styles.xml’ file for your workbook you may already know if you have a problem here.
It doesn’t matter if you haven’t. The easiest way to find out how many styles (format combinations) are your Excel file is to check the ‘Styles’ dropdown. You’ll find this in the <Home> ribbon.
You may be surprised to see thousands of ‘styles’ there. Even if there are not that many it is still worth removing unused custom styles.
For each style in the ‘Custom’ section that you don’t need you can right click on it and click <Delete>. You don’t need to delete any standard styles. Deleting the custom styles should not change the individual cell formats.
How do I stop these happening again?
The fixes above should cover 99% of slow running workbook problems. But how do you stop them happening again?
Remove or replace the process you are working on month after month
For monthly processes it is worth asking the question if the process is really required or could be achieved more simply.
Automate the process with a Macro
Repetitive tasks in Excel can be replaced with VBA macros. This is mainly a cost/benefit question. Is it worth investing time/money to automate to save X hours per month in the finance department. Consider hiring a consultant if you have processes that could be automated.
Try to avoid pasting formats in to a workbook
Most of all you probably want to watch what you are pasting in to your workbook. If you can avoid moving sheets in to your file from other workbooks, pasting formats and pasting named ranges you should be able to avoid your file slowing down.
What if the file is still slow?
I recommend looking at the three issues above first. But if you are still experiencing slow running files then I suggest looking at the following.
Are you using many rows of data?
Excel® is not a database application. While Excel® on a modern computer can cope with many rows of data – if your file is still running slow then you should consider an alternative such as using Power Pivot or storing the data in Access® or some other database application. Excel® can reliably retrieve data from databases.
Are there many volatile formulas?
There are a number of Excel functions that recalculate every time Excel recalculates. Obvious examples such as NOW, RANDBETWEEN. And less obvious examples such as OFFSET, INDIRECT, INFO, CELL.
The question is not just how many volatile functions you have but how many cells are dependent on those volatile functions.
Are you using whole column and row references in functions?
If you have data in a range but instead of referring to the range with the data you just refer to whole columns or rows then you will slow your file.
Conclusion
Create a backup of your file and use the file fix utility (just click on Downloads above) or go through your file and carry out the following manually:
- Reset used ranges
- Delete obsolete named ranges
- Delete redundant cell styles
Leave a Comment