Visual Audit Tool for Excel Spreadsheets

The Visual Audit tool analyses your worksheet and then applies formatting to each cell to indicate its content type as well as any potential errors. By creating a color-coded map of your spreadsheet, Visual Audit helps you identify possible quality and correctness isues at a glance. The content types identified by Visual Audit are:

• Formula types –
  • Formula: Contains a normal calculation with no errors.
  • Unlocked: A formula cell with protection set to Unlocked.
  • Linked cell: A formula with an off-sheet or external workbook reference.
  • Error: A formula with an error value (#DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!), circular reference, or one of the following Excel Error Checking Options flags:
    • Cell evaluates to an error value.
    • Cell contains an inconsistent formula for a region.
    • Cell formula omits a cell for a region.
    • Cell is unlocked and contains a formula.
    • Cell formula refers to empty cells.
    • Cell contains an inconsistent formula for a list.
    • Cell contains a text date with 2 digit years.
    • Cell contains a number stored as text.
    • Cell contains a list validation error.
• Input types –
  • Input: A cell that is referenced in another cell's formula.
  • Locked: An input cell with protect set to Locked.
• Constant types –
  • Normal: Text or blank cells that are not referenced by other cells.
  • Numbers
  • Dates

Visual Audit Form

The image below shows the Visual Audit form. It loads to the right of the screen and stays on top while the audit is performed. When the Apply Visual Audit Styles button is clicked, the worksheet is analyzed and the cells are color coded per the Content Style Legend.

Audit Example - Cash Flow Worksheet (Original)

The image below shows a region of a simple cash flow spreadsheet prior to applying the Visual Audit tool. Spreadspeed captures the stylings for each cell prior to running the audit and will restore them after the audit is complete.

Visual Audit Applied

There are two ways that the Visual Audit tool can be applied - with or without error comments. The image below shows the Visual Audit applied without error comments (error comments are discussed below). The Visual Audit dialog contains a legend of the color codes and floats on top of the worksheet, making it easy to reference the color code definitions. We've annotated some of the color coded cells and describe them below the image.

Color Coded Cell Descriptions

1. Input – A cell with dependencies (i.e. referenced by a formula in another cell).
2. Formula – A normal, locked formula cell with no errors or external dependencies.
3. Blank – A blank cell can simply mean that the cell is is not referenced by any formulas, however, the Visual Audit tool makes it easier to spot two potential issues –
   • Inputs that you expected to be Blanks.
   • Blanks that you expected to be Inputs.
4. Linked cell – A formula with a reference to another worksheet. This is not an error, but it will signify that your worksheet has outside dependencies.
5. Locked input – If the worksheet will be protected, Input cells should have their protection set to Unlocked.
6. Number – Cell N7 contains a number where we would have expected a formula. If you look at the image of the original sheet, this problem is hidden in normal view due to the Accounting number format, but Visual Audit makes the problem clear.
7. Unlocked formula – If the worksheet will be protected, it's a good practice to set the protection of formula cells to Locked.
8. Locked input – This is marked as a Locked Input, just like cell D7, but whereas cell D7 was an Locked Input surrounded by other input cells, D10 is a Locked Input surrounded by Formulas, raising a visual red flag (or yellow flag in this case). Looking at the cell content, we find that the formula has been replaced with a 0, and thanks to Visual Audit we can correct the problem.

Apply Visual Audit - with Error Comments

In the next example, we re-run the Visual Audit on the same worksheet but this time with the "Mark and comment all error types" option on. As the screenshot below shows, cells with errors have a comment marker. Excel will show the error notification flag in the corner of cells with errors, but there are two reasons why the Visual Audit error comments can be useful to you:

1. You may have turned off Excel error checking (after all, they can be kind of annoying).
2. Excel has the "Formulas referring to empty cells" error check turned off by default. Most of the time this error check doesn't matter. Functions like AVERAGE() ignore empty cells, and it has no impact on SUM(). However, because a blank cell is treated as a zero (0) by some formulas, there are cases where is does matter, and the Visual Audit error comment option can help you expose those weaknesses in your spreadsheet.