- BY Kevin Lehrbass
- POSTED IN Formulas
- WITH 0 COMMENTS
- PERMALINK
- STANDARD POST TYPE

**Have you ever inherited a messy Excel file?**

It can be * REALLY *painful! Over the years I have used various techniques to audit Excel files.

This post shows you how to distinguish between different error types, count them and find them!

**What TYPE of errors?**

Formulas can distinguish between different types of errors:

**$NULL! #DIV/0! #VALUE! #REF! #NAME? #NUM! #N/A**

Each different error has a unique ID number (i.e. **#NAME?** = 5).

The formula below counts the number of **#NAME?** errors in sheet ‘Data1’.

**{=SUM(IFERROR(IF((ERROR.TYPE(Data1!$D$2:$D$1001)=$C18)**

***ISFORMULA(Data1!$D$2:$D$1001),1,0),0))}**

**This formula is useful because the errors must be the result of a formula not a text value in a cell.**

**WHERE are these errors?**

The formula below is a hyperlink to the 1st error of a specific type.

**{=HYPERLINK(“#”&”Data1!” &”D”&MIN(IFERROR(IF((ERROR.TYPE(Data1!$D$2:$D$1001)=$C18)**

***ISFORMULA(Data1!$D$2:$D$1001),ROW(Data1!$D$2:$D$1001),””),””)),”x”)}**

**Watch my YouTube video**

**Is it smart to hide your errors?**

And finally, something to think about… If you use **IFERROR** to hide errors then you may never know the quantity and type of errors in your file.

**What are the ingredients to my solution?**

Error.type, Isformula, Hyperlink.

**Subscribe to my YouTube channel and learn more!**

**See my free templates here!**