Cell Errors Report
The Cell Errors report scans all the cells in each sheet of the workbook and lists any cells that have the following Excel error codes:
- xlEvaluateToError: Evaluates to an error value (#DIV!, #NULL, #N/A).
- xlEmptyCellReferences: Formula refers to empty cells.
- xlOmittedCells: Formula omits a cell for a region.
- xlInconsistentFormula: Contains an inconsistent formula for a region.
- xlInconsistentListFormula: Contains an inconsistent formula for a list.
- xlUnlockedFormulaCells: Formula cell is unlocked.
- xlTextDate: Contains a text date with 2 digit years.
- xlNumberAsText: Cell contains a number stored as text.
- xlListDataValidation: Cell contains a list validation error.
- Cells with the error codes xlOmittedCells and xlEmptyCellReferences typically also return the xlEvaluateToError code.
- Locating cells with the inconsistent formula error codes (xlInconsistentFormula and xlInconsistentListFormula) will find some inconsistent formulas, but it is is not a robust method for locating them all. Formulas replaced with values will not show up as errors, nor will altered formulas in the last cell in a row or column. Using the Find Inconsistent Formulas tool on a specified range is a better method, and the Visual Audit tool is also useful for locating values in a range of formulas.
This is one of the reports that can be run individually or through the Master Audit Report.