Easily Find and Fix Excel Errors
How much time do you spend trying to find and fix error in your Excel workbooks? Or even worse, fixing errors in other people's workbooks. After commenting on my article See Formulas on an Excel Worksheet, Patrick O'Beirne, author of Spreadsheet Check and Control , asked if I'd like a review copy of his new Excel add-in, XLTest. It's designed to test your workbooks, in Excel 2007 and earlier versions.
Even though all my workbooks are error free ;-) I accepted his offer. Patrick sent the add-in, instructions, and a couple of sample files. Here's a screen shot of the first sample. If your files look like this, you might need more help than this add-in can provide!
The Add-In Commands
After I installed the add-in, a drop-down menu appeared on the Ribbon, as well as all the icons. I'd rather have just the drop-down menu, so maybe there's a way to turn off one or the other.
The add-in adds 8 key shortcuts that you can see in the Ribbon, and in the popup menu that appears when you right-click a cell. Since the add-in features these shortcuts, let's look at a few of those first.
Copy Formula: This is handy if you want to move a formula without changing the relative references. It's quicker than copying the formula from the Formula Bar, and pasting it into another cell, which is the way I'd do it without this shortcut.
Operate on Selection: Select non-contiguous cells, in multiple rows and columns, then move or copy them to a different location. If you try this in Excel, you'll get an error, so this shortcut could really save you some time and aggravation. To get this to work in Excel 2007, I had to select the top left cell last.
Select Formula Region: Selects all the cells in the current region that have the same formula (in relative R1C1 terms) as the active cell. This helps you see if you've entered or updated a formula in all the relevant cells. Excel's error checking could flag those cells for you, but I usually have that turned off because it clutters up the worksheet.
Jump to Bottom Right: I use Ctrl+End to go to the bottom right, so I'd rather have another feature shortcut here.
Document Your Workbook
The rest of the commands let you test your workbooks for errors, starting with the Start New Test Session command. It opens a dialog box that lets you choose from 4 options for keeping logs, recording settings, opening files and closing open workbooks.
Next, you can document your workbook with the Worksheet Documentation command. Select all the options, or just a few, and list the results in a new workbook or existing one. All the details are reported in a well organized worksheet. Here's a small section of the report for the demo file.
In Excel 2007, when I selected Number Formats with the other options, the Format Cells dialog box stayed open, and none of the Custom Number Formats were listed in the documentation. Everything else was correctly documented though, including the VBA modules.
Inspect Your Workbook
For me, the main feature in the XL Test add-in is the Detailed Inspection. Instead of spending hours or days combing through your worksheets, click a button and get a report in a few seconds. Again, there were problems with reporting the Number Formats, but I'm sure Patrick can sort that out quickly.
It creates a detailed report, with errors and other problems listed. You can quickly focus on the crucial errors, and get things fixed.
I don't know what happens if you choose to see errors in separate cells, and there are more errors than columns. Maybe it wraps around, or maybe its head explodes!
Other Tests for Your Workbook
There are several other tests that you can run in the XLTest add-in. For example, colour and document the data validation, conditional formatting or number formats on a worksheet. These test will quickly highlight any cells in a range that are different than their neighbours, and allow you to fix them.
After testing, you can click the add-in command to clear all the fill colour from a worksheet. Since the tests also add rectangle shapes with hyperlinks, it would help if those could also be removed with a single click.
The add-in also has a command for Batch Testing, so you can run all the tests on a workbook, with a single click, instead of running each test individually. The documentation warns of Excel memory problems if you try this on a large workbook.
Test Cases: The XLTest add-in can run a list of tests, and create a report on the results of each test. Use this to ensure that a new version of a workbook works the same as the previous version, except where you have intentionally changed things. The add-in will also convert any existing Scenarios to test cases, so you can run those.
Comparison: With the add-in, you can compare worksheets or workbooks, and create a detailed list of differences. For workbooks, even the VBA code is compared.
Housekeeping: There are several housekeeping features, such as creating a table of contents, unprotecting a sheet, and deleting custom styles.
Functions: The add-in also adds 14 functions to Excel, such as GetFormula, ColorName and FileSize.
Should You Buy the XLTest Add-in?
If you're an expert programmer, you might have your own code that does error testing, comparison and housekeeping, so you won't need Patrick's add-in.
If you don't have your own code, this add-in would be well worth its purchase price (£199, approx $296 US), in the time you'd save in looking for errors, and other tests. Yes, the add-in is more expensive than many other utilities that I've seen. It's a bargain though, when compared to hiring an Excel programmer or trying to do the testing yourself.
For workbooks that you've inherited from colleagues or clients, you might not even know where to begin the error hunt. The XLTest add-in can do most of the detective work for you – it even unprotects and unhides sheets, rows and columns.
And, of course, the real value in XLTest is in finding those critical errors that you didn't even know you should look for. If the add-in saves your job, it's priceless!