How to deal with unstable Excel workbooks

Old wooden bridge
istock_jotaelesalinas_aw
Share this content

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:

Excessive formatting

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.

Unused styles

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

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).

unstable workbook solution 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.

Shapes

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).

unstable workbook solution 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).

Volatile functions

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

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.

Defined names

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.

Hyperlinks

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.

Compatibility issues

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.

About Nick Brown

Nick Brown

Nick Brown is a chartered accountant and a partner at Eastbourne and Brighton Accountants Plummer Parsons. You can connect with Plummer Parsons on Twitter, Facebook or LinkedIn.

Replies

Please login or register to join the discussion.

avatar
08th Jun 2018 18:38

All good stuff, Nick - but you've overlooked the single most frequently encountered file size and "slowing down" problem. Somehow, it's possible to fool a worksheet of quite modest size into thinking that it is much bigger than it is. You need to find out where the bottom right hand corner of your "used" worksheet is. To do this press "end" and then "home": this will take you there. If you find the cursor is now hundreds or even thousands of columns to the right of where you think your worksheet stops, or thousands or millions of rows too far down, or both, then you have a lot of blank space that Excel thinks it is using. Highlight the excess rows or columns, right click and then delete them - and save the workbook immediately.

If you check the file size before and after this operation, you are likely to see a reduction - quite possibly a dramatic one. You should also notice that the whole thing has speeded up.

How did that 'inflation' happen in the first place? I've never quite got to the bottom of that. It's probably down to human fallibility, and I think even the most expert spreadsheet users share that with us. I have encountered this sufficiently often (in my own spreadsheets and those of others) to make this well worth checking for whenever something does not look right.

Thanks (5)
avatar
to johnfrancis
08th Jun 2018 16:14

Great tip that also alleviates the frustration of trying to drag a scrollbar to look three columns to the left, and finding yourself deep in three-letter territory.

To the question of how - it can happen if entire rows or columns are created with "insert copied row". It can also happen if formatting is applied to an entire row rather than to necessary cells. Any action, in short, which causes Excel to believe that the entire possible span of the worksheet is important to you; user error, as you say.

Thanks (2)
avatar
12th Jun 2018 11:52

This is really awesome. This will help me improve my self. gba emulator

Thanks (0)
avatar
16th Jun 2018 06:57

thanks for sharing with us. It's very great and informative article.
regards
Mi Account Unlock Tool

Thanks (0)
avatar
26th Jun 2018 11:22

Thanks for sharing this awesome post. This post really help me to deal with unstable excel workbooks. connect ps3 controller to pc

Thanks (0)
avatar
28th Aug 2018 08:01

This was really helpful for us!
top xbox emulators for windows pc

Thanks (0)