Why is my spreadsheet so slow? Excel's new Check Performance feature

A new command has recently been added to the Excel Review Ribbon tab: Check Performance. Useful as this command might be for some users, it's a long way from being a complete solution to all of the problems that can cause spreadsheets to slow down.

Check Performance is designed to check for 'Excess formatting, unneeded metadata and unused styles'. It promises not to affect any actual data, but the detailed help does include a warning not to try and optimise a worksheet that includes Pixel Art using the feature. For those that haven't come across the idea of Pixel Art in Excel, it's a way of creating pictures by using Excel cells as the pixels in a picture by formatting each cell in the appropriate colour. Here's a seasonal example:

Check Performance in action

The Check Performance command can be found in its own Performance group in the Review Ribbon tab. Clicking on the command opens a Workbook Performance task pane that describes what the Performance Checker looks for and displays an overall performance check for the whole workbook. An Optimize All button will optimise the whole workbook in one go, but for more control, a separate review is shown for each individual sheet in the workbook:

Here, we have selected our Performance sheet:

This particular example led me to realise something that I'd previously been unaware of. Excel seems to hold a 'default' format for each column. The range of cells that we have formatted with a green fill (D5:J492) is identified as containing empty cells that have been formatted, but columns M,N and O, which have a blue fill entered for every cell in the column are not included as available for optimisation.

Here, we have removed the fill colour formatting from the top 3 rows of or our blue columns. The Performance Checker notices the change and displays a Check again button:

When we click on the Check again button, we can see that it is the 9 'unformatted' cells that are shown as available for optimisation, rather than the millions of cells that we have actually formatted:

Once we have decided that we want to optimise the sheet, we can click on the Optimize Sheet button and the formatting will be removed (or applied):

By reviewing individual sheets, rather than just using the Optimize All button on the All sheets version of the pane, we can check what is causing the problem before we make any changes that we might regret, and also optimise some sheets without affecting others, such as our Pixel Art sheet.

Other causes of poor performance

This new feature provides a quick way of identifying and correcting a few problems that can cause performance issues in a spreadsheet, but it's likely that issues with overall spreadsheet design or with specific formulas are more common causes of inadequate Excel performance.

Unsurprisingly, there is lots of guidance available on the Internet to help address these types of performance issues. One site that is dedicated to performance issues that has been mentioned before in the Excel Community is Decision Models. As well as advertising some utilities that can help with resolving performance issues, the site also includes lots of useful information about how Excel formulas are calculated and what can be done to speed up calculation.

It's also worth noting that one of the main causes of slow calculations in spreadsheets, the calculation of an exact match as part of a lookup function, has been addressed by recent enhancements to the way in which these functions work.

Related articles

For more articles on spreadsheet performance, search the ICAEW Excel Community article archive. Performance is one of the terms include in the Keywords word cloud.