If you have inherited a spreadsheet, or you have been building up a spreadsheet over years (or both), you may find it seems very slow, either all the time or sometimes. So if the slowness of your spreadsheet does not make sense, one of these could be the problem. Remove them to speed up a slow Excel spreadsheet.
Table of contents
Steps to speed up a slow spreadsheet
The most important step is making sure you have patience. You need to understand that for the benefit of speeding up your Excel spreadsheet, you may need to spend a bit of time waiting for Excel (get a book, watch Netflix). Some key points:
- Make a copy of the slow spreadsheet. The initial stage is just to figure out what the problem is. You may be deleting whole sheets, features. You don’t want to worry about whether you are breaking the spreadsheet. It is a play copy.
- Once we know the problem, use the Task Manager to end the Excel session. If you try open the conditional format manager and it is taking tons of time, that is probably the problem. Crash Excel, open it again, choose one cell and try the Manager again and see if it makes sense.
- Once you know the problem, this is where you need patience. If you plan to remove all the conditional formatting, know that it could take hours or computer time (you will be doing nothing). Rather run this overnight. A watched spreadsheet does NOT work quicker.
- ‘Excel not responding’ DOES NOT mean Excel is not working. Leave it. Go have lunch, run it overnight. It will finish the job.
Excel files corrupt regularly and is mostly slow
Too much Conditional Formatting
Symptoms:
- File corrupts regularly.
- Seems unnaturally slow to open
- Mostly slow but can have moments of normal speed.
- Extremely slow when inserting/ deleting rows/ columns
Conditional formatting can cause major speed problems. This is because it is something you don’t easily see unless it is on, but you can theoretically apply as many conditional formats to as many cells as you like. If you like to use the format painter, you may have inadvertently created the problem.
In a spreadsheet we had deleted all contents and still it was slow to open and slow to work with. If you use GOTO special and try and highlight all cells with conditional formatting you will see that it takes forever. Below how to access GOTO Special (this must be done on each sheet unless you use a tool).
Now choose the Conditional Format and leave it on ‘All’. It should be quick so if it takes long it indicates the problem.
You can also go into the Conditional Format manager (see below) and if it struggles to open it could be the problem. Once it opens you will have a better idea (give it time- go have lunch- it could take awhile)
When you eventually get into the Conditional Formatting manager you may see something like this. Note that there are at least 10 conditional formats on each cell and the scroll bar shows that there are lots of entries. There are more efficient ways to build this type of conditional format so that it only takes one line, and is applied to many cells instead of one at a time.
If it is slow, it means that there are so many cells, with so many conditional formats, Excel is consistently checking these cells to see if something must change. You need to remove the unnecessary conditional formats or build them in a more efficient way.
Removing them can be a challenge as well, and requires patience. Where you can, delete entire rows and columns rather (often the extra conditional formats are on cells you are not even using). When you do this, have patience. Even if Excel says it is not responding, it is working. Go have lunch or run it overnight, and see what it looks like when you get back. Removing/ optimizing the conditional formats will speed up your slow spreadsheet significantly.
Picture links (objects)
Symptoms:
- Moving around is OK, but each action seems to have a little pause before it moves on.
- You sometimes seem to have strange ‘shadows’ on your spreadsheet.
- Even bolding or changing a cell colour seems to cause a delay.
A more obscure reason for a slow spreadsheet, because not many people use Picture links (useful in dashboards- they are ‘pictures’ of an area of a spreadsheet but they keep looking at the area and change when the original area changes).
Sometimes you see something like the below. Note that there are the normal gridlines but there seem to be ‘shadows’ of gridlines.
You may find that your spreadsheet is struggling with multiple picture links (and on a apple mac it is crazy slow). To see if this is the case, on each sheet (unless you have a tool), click on HOME, then FIND & SELECT and then GOTO Special as shown below.
Then choose Objects and click OK
If you see Objects highlighted that you never knew existed then try deleting them. So below, even though this spreadsheet looks blank there are several objects on it, and this case they are picture links.
You should see an immediate improvement in speed of the slow Excel spreadsheet.
Hidden columns/ rows (end of sheet)
Symptoms:
- When you enter a number it takes awhile to actually enter it.
- Does not seem to affect formatting issues like using bold or colouring a cell.
It looks nice to hide any unused rows and columns so that the spreadsheet only shows a few columns or rows, with grey space elsewhere as shown below. However, this has a risk.
As shown below, the assumption is that the grey areas have nothing in them. But people love to use shortcuts and, when you want to get the the end of row, the END button is commonly used. What you may not realize is that END means end of the spreadsheet, whether it is hidden or not.
Unhide the rows/ columns and see if any calculations go all the way to the end (16 000 columns!). You can then decide whether to delete them which should speed up the slow spreadsheet.
Interestingly, the other way to get to the end of a row (CTRL + SHIFT + arrow key) would stop at the end of the visible section. You can learn how to do this in our fastest way to navigate cells in the free tips and tricks course.
Excel slow to move between cells.
Hidden Name Ranges
Symptoms:
- Slow to do simple things like moving to the next cell.
- If you highlight cells it is slow to move to the next cell you want to click on.
- Can come and go.
- Asks to connect to external links you have never heard of and shouldn’t be in your spreadsheet.
You need to do a bit of work to find it. You may not be aware, but it is possible to have Named Ranges that are hidden. To know if this is the problem you need to use a tool like ExcelAnalyzer to list ALL the name ranges including the hidden ones (see below). You can then decide if you recognize them and whether you need to keep them or not.
We have explained how to find and fix this problem on the Hidden Name Ranges slowing spreadsheets page. Removing these will speed up a slow Excel spreadsheet.
Excel slow on open then speeds up
Linked to disconnected printer
Symptoms:
- Normally slow for your first few interactions then it is fine.
- Often happens when you are away from the office (or wherever your default printer is).
In some cases your default printer is the problem. If your default is a printer that is not available at the time you open your spreadsheet, the initial few interactions may be slow (Excel checking whether it needs to change something for printer settings).
Rather change your default printer to be a pdf writer or something similar that is in your own computer and Excel will always find it quickly. This will speed up the spreadsheet in those initial stages.
To change the default printer, in Windows, click
- the Start button, then
- Settings, then
- Printers and Scanners, then
- Select a ‘printer’ that is on your computer like a pdf writer, then
- Click Manage, then
- Set as default.
If there is no option for this, go back to Printers and Scanners, and untick the ‘Let Windows manage my default printer’.
Excel slow when using Spell Check, Goalseek or Find/ Replace
Slightly different and we have covered it before, but Excel can seem slow when using something like Spell Check, Goalseek or Find/ Replace. Not sure what it is but you can speed up a slow Excel spreadsheet using these tools by wiggling the mouse! Watch the video as we make Spell Check and Goalseek speed up.
Speed up Excel with the Task Manager
A bit strange, and more observational with no real factual reasons, but we find that just activating the Task Manager (as if you are about to End Task on Excel because it seems to be hanging) seems to speed Excel up, almost as if it works faster when it is being watched!
We don’t think it is co-incidence that, finally we run out of patience, want to end the task by clicking CTRL + SHIFT + ESC and then suddenly within seconds of doing this it is done processing.
A bit like the issue above where moving the cursor seems to speed up Excel, we can’t say why but it seems to work.
Speed up Excel consulting options
We can assist in finding the cause for a slow spreadsheet. Contact us via the consulting pages.
Related
Excel sheet slow (but it is a small spreadsheet!)