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!

Save

Save

Save

Save

Save

Save

Post a comment