A new Excel tool might help with very large, sluggish workbooks but don’t expect a lot despite the promising name “Check Performance”. It took us just minutes to find some undocumented strangeness in this cautious addition to Excel.
“Check Performance” looks for unnecessary formatting, metadata and styles that might be cluttering up a large workbook. That can happen when cell values or formula are removed (often using the Delete key), which leaves the underlying formatting etc. behind.
There might be a “Check Performance” prompt when opening a workbook or go to Review | Check Performance.
A side pane opens, listing the sheets which have ‘issues’.
Click on a sheet to see details of the problems detected. Choose one of the cells or ranges listed to make Excel jump to that part of the workbook.
Unfortunately, you can’t optimize individually suggested cells or ranges. The only choices are to ‘Optimize sheet’ or ‘Optimize all’.
Tip: To totally empty cells, use Home | Editing | Clear | Clear All.
For really bloated workbooks “Check Performance” can help. Microsoft’s, perhaps extreme, example optimizes over a million cells, reducing the XLSX from 3.14MB to a mere 17.5kb
Overly or Commendably Cautious
We did some quick tests of Check Performance. It’s perhaps overly or commendably cautious, depending on your point of view.
Check Performance had no issues with this worksheet. The empty cells in Row 16-21 below the table in Col A-B were ignored (quite right).
Surprisingly all the empty but formatted cells in Col D were also left alone. It’s surprising because some of the cells C2:C15 are empty with unnecessary formatting, some of which isn’t visible.
Possibly the reason is the text in D1 which seems to make Check Performance ignore all the cells in the column. We added Column F with the same empty but formatted cells as Col D but without text in Row 1. Now ‘Check Performance’ sees the Col F cells as empty and offers to optimize them.
That doesn’t mean a whole column with heading is exempt from “Check Performance”. Some unneeded formatting in D100:D108 is detected, despite the heading. Likely the totally empty rows before Row 100 allow “Check Performance” to resume the checks.
The message ‘Remove text properties’ refers, in this case, to the Font Color set for the cells.
No references to the ‘empty’ cells
Not mentioned by Microsoft is the effect of a formula referencing an ‘empty’ cell with a formula from another part of the workbook.
Going back to an earlier example where the empty cells in Column F are detected by “Check Performance”. We added a simple IsBlank() test to cell F2 and got a surprising result.
Just that single cell reference changed “Check Performance” results for all the other cells in Column F, even ones NOT linked to any other cell.
See what we mean by ‘cautious’? It’s a wise move by the Microsoft developers but needs to be properly explained. Otherwise they will have a lot of Excel users wondering why ‘empty’ cells aren’t being detected by “Check Performance”.
Do we need to remind about backups? “Check Performance” should not change your workbook results and it’s hard to see how it could, but don’t risk it and run the new tool over a backup workbook.
“Check Performance” might change the look of a workbook. Formatting like Fill Color and Borders will be removed from empty cells.
Low Hanging Fruit
The tool is very much looking for easily detected issues or ‘low hanging fruit’ in a workbook. More complex performance issues like unnecessary or repeated calculations or data links are beyond “Check Performance”, at least in its current form.
Only very large workbooks will noticeably benefit from these checks. In other words, don’t expect too much.
We like the idea of “Check Performance” though the title might seem to promise too much.
Like many Office features, it needs better documentation so paying customers can understand what they get and not to expect from the tool.
The current blog post covers only the basics. That post says there’s “… more about this feature” at a Microsoft Support page., but that page doesn’t really tell customers any more and certainly not the sort of detail they deserve.
Who gets it?
Check Performance is only available in Excel on the web, at least for the moment.
There might be a delay before it’s available globally.