Tools available in ExcelAnalyzer sheet analysis

When using ExcelAnalyzer you will be spending time on the individual sheets debugging the functions and formula to identify spreadsheet errors. ExcelAnalyzer has a number of tools that are useful in (quickly) working through a sheet and reviewing all the unique formula after it has run the ExcelAnalyzer sheet analysis.

If you don’t have a copy of a spreadsheet auditing tool yet, get a free trial copy of ExcelAnalyzer.

Reduce the size to find the inconsistent formula and hardcoding

As per our tutorial on finding inconsistent formula and formula that have been deleted, it is useful to have an overview of the structure of the spreadsheet. On the sheet, you can click on the ‘Narrow Columns’ and ‘Zoom 60%’ to see more of the sheet with the colours. You can easily switch these on or off and identify the problems quicker.

Trace formulas and inputs (one or many at a time)

When you look at a row or column, it is useful to see what they are looking at, and which cells use them.

If you are on a sheet and you click on the ‘Tracing Formulas’ button, then as you click on a cell it will show, with blue lines, where the cell gets its information from. The best feature though is that if you highlight a row (cell D34 to K34 below), it shows the trace precedents on all of them at once.

So below we can see that the opening balance always looks at the previous closing balance which is correct. You can do the same with the Trace inputs.

Find specific functions to easily review the high risk functions.

As shown in the section on prioritizing which functions have higher risks and why, on the sheets you can choose to see only one function at a time.

So below, if you choose, from the ‘Function Filter’, the ‘IFERROR VLOOKUP’ cells, only those cells will have colours and you can review them without the distraction of the other cells. Once you are done you can look at different function/s or choose to ‘Reset colours’ to see the full picture again.

Find the cells that link to other sheets and spreadsheets

When you are on a sheet, you can also find all the cells that are relying on sheets or spreadsheets outside of the one you are viewing.

So by clicking on the links dropdown, you will get a listing of all inter sheet and spreadsheet links. As shown below, when you choose one to review, only those cells are highlighted and you can review them in detail.

Find the hardcoded cells in the sheet

There is an option called Overall Filter and in here are a number of useful filters to find functions and formula with risky characteristics. These include

• hardcoded formulas (formulas with numbers in them- see why hardcoded numbers are a risk),
• formulas with functions,
• formulas with “Text” in them (see where “text” in function is a risk),
• absolute formulas, array, table, user defined and named formulas.

As shown below, when the ‘Hardcoded Formulas’ are chosen, only the cells with a number of some sorts in them are coloured and you can work through just these very quickly.

Find common Excel errors

You can also choose to look at only the error messages in the sheet. Some may be ‘acceptable’ errors but others may be genuine spreadsheet errors.

As shown below you can filter on the following error messages and error types:

• #NULL!
• #DIV/0!
• #VALUE!
• #REF!
• #NAME?
• #NUM!
• #N/A
• Numbers formatted as text (risk is that it will not be added up- see the SUM risk areas)
• Circular references
• Suppress Excel errors (finds all IFERROR functions that may be hiding an error (see the IFERROR/ ISERROR risks).

Work through each unique formula

Lastly, you can use the colour maps and work through each unique formula one by one to review it for errors. Practically speaking this is the only way to be comfortable with an entire spreadsheet.

When you click on a colour, eg (1) below, the sheet will flash on that cell (2) and you can now debug the cell and look for errors. If you click on (3) it will alternate through different view on that cell being:

• 1- Isolate only those unique formulas (so in this case only the bright green will be shown).
• 2- Show where the first formula in the range gets its information from(trace precedents), or you can choose to do trace dependents on this cell.
• 3- Show where all the same formula types get their information from (trace precedents but on multiple cells), or you can choose to do trace dependents on these cell.
• 4- Go back to normal view with first cell showing trace precedents, or you can choose to do trace dependents on this cell.
• 5-Stay in normal view and show trace precedents on all the same formula types , or you can choose to do trace dependents on this cell.