In this guide, Nick Brown of Brighton-based accountancy firm Plummer Parsons looks at how to diagnose and solve common causes of unstable Excel workbooks.
Despite the use of professional accounting software, accountants of all shades and hues still rely on Excel for processing and analysing financial data.
However, the simplicity of pumping huge amounts of data into Excel can also be its biggest stumbling block. As small worksheets eventually grow into bigger and more complex ones, stability and performance issues can arise.
Excessive slowdown and “not responding” errors are the first sign of an unstable workbook, but in worst-case scenarios, workbooks can become completely unusable or too unstable to open.
Formatting, Styles and Shapes
When your Excel files start to crash and/or give you a “not responding” error, you need to start looking at the contents of the file and remove certain items that you may no longer need, including:
There are several tools out there for cleaning excessive formatting. These will help remove formatting (eg colour, borders, non-printing characters) that are not in use, which causes you to have a larger file than necessary. If you are using Excel 2013 or later then Microsoft has its own add-on which you can install using the following guide.
Different styles on one file make the file grow bigger, which could then cause you to get a “too many different cell formats” error. The Style Cleaner Tool for 2007, 2010, and 2013 versions works like a treat. Of course, the best way to avoid this problem is to avoid using multiple styles on one worksheet.
Conditional formatting can really slow down your workbook so use it sparingly. To remove conditional formatting from a column or range of cells, highlight them and go to Home Ribbon> Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
Save the file under a different name and it should run well the next time you open it.
To clear all conditional formatting on your worksheet simply select Clear Rules from Entire Sheet (see image 1).
To check for corruption in conditional formatting, go to Conditional Formatting under the Home Ribbon > clear rules > follow steps 1 and 2 > save the file under a different name. It should run well the next time you open it.
To remove unnecessary shapes (e.g. charts, drawings, pictures, etc.), go to Home Ribbon > Find and Select > Selection Pane. On the Selection Pane, remove all unwanted shapes on your worksheets by clicking on the corresponding eye icons beside each shape’s name (see image 2).
Excel calculations and formulas
Huge datasets tend to eat a lot of your computer’s RAM, resulting in hanging workbooks each time you try to run calculations. While you may be limited by your device’s hardware, there are still things you can do short of buying a new computer. If you’ve cleared any unnecessary formatting but Excel continues to crash or perform slowly, you need to look at the formulas and calculations in your worksheet.
SUMIF, COUNTIF, and SUMPRODUCT
These formulas eat more memory than VLOOKUP and IF statement. It’s all too easy to create tables that use hundreds or even thousands of complex SUMIF and COUNTIF formulas, causing your Excel workbook to slow down or crash. Try using less memory-hungry formulas or a pivot table, which demands far less memory.
Large number of formulas
An excessive number of formulas in a workbook may cause Excel to run slowly and in worst case scenarios even crash. The quickest and simplest solution to this is to copy and paste as values. If you add new data to your workbook, you can then re-enter the formulas to calculate the new values and then copy paste this as values (you can always keep records of these formulas as text strings in a separate tab for quick reference).
Formulas that reference entire rows or columns
If you reference an entire row or column using Excel 2003 or earlier you are referencing 65,536 and 256 cells respectively. In later versions of Excel, this jumps to 1,048,576 cells for a column reference and 16,384 for a row reference, meaning Excel has to do a lot more work. To fix this, simply make sure your formulas only reference the necessary cells or convert your data into a table (which comes with all sorts of other benefits).
Limit your use of volatile functions, since you don’t need these for every single cell that you have. This includes RAND, NOW, TODAY, OFFSET, CELL, INDIRECT, and INFO formulas. Excessive use of these can massively slow a workbook down.
Array formulas by their nature can be quite slow to calculate, so keep in mind the cells that it needs to calculate and make it a point not to use cells that are not completely necessary.
Other potential culprits
In the majority of cases, addressing excessive formatting and an unnecessary volume of formulas will address slowdown issues on your Excel workbook. This isn’t always the case though and there are a number of other culprits that could be causing your workbook to slow down, many of which can be addressed with quick fixes or by installing a simple add-on.
Use the Defined Name Manager tool to double-check whether any of your defined names are linked to other workbooks or temporary internet files, as this will only slow down your workbook.
Excessive links to external sources like URLs can cause continuous crashes. If your spreadsheet is crashing, try saving a version of it and running it without the links or copy-pasting the links as text only. This is a quick way to find out whether your file is causing the crash or another file linked to it.
Add-ons and plugins
Some plugins, while helpful, can significantly slow down your workbook, especially with huge datasets. The same goes for installing and running a number of add-ons or plugins with your Excel Workbook.
Minimise the add-ons you use down to what is absolutely necessary to run that workbook. You can also disable plugins by going into File > Options > Add-Ins > Excel Add-Ins (in dropdown menu) > Go. You can also COM Add-Ins, Actions and XML Expansions from here.
Linking to other Excel workbooks
Linking between two (or more) workbooks, especially if values in workbook1 depend on values in workbook2, can trigger an unstable performance. It also means having to open up multiple workbooks in order to see all the information. Limit linking to tabs within the same workbook when possible.
Certain Excel attributes may not translate well from one version to another. If you want to successfully run a file made on an older version of Excel on a newer version of the program (and vice versa), Microsoft has provided a number of possible solutions.
Watch your Excel file size
Given how much processing power modern computers have, unstable workbooks only tend to happen when your spreadsheets have grown to at least 20 MB in size. That said, keep an eye out on your file size and backup your files regularly should you need to revert to an older version.
As a general rule, you should always try to limit (or completely avoid) the use of unnecessary programs to lessen the use of your computer’s resources. If you’re opening a big Excel workbook then close down any memory-hungry programs, such as excessive browser tabs, before you do so.
Finally, always make a backup if you are making big changes to your Excel workbook. You might well want to revert to your original version if you find that the fixes you’ve made haven’t worked.
If all this fails and you’re still getting performance issues, then it may be an issue with your computer’s memory. Unfortunately, the only solution for this involves getting out your business bankcard.