Another quick win is finding any Excel error messages in the spreadsheet. These are the #NULL!. #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! and #N/A. As mentioned previously, with the way Excel works and with some Excel functions, some errors are more likely to be a risk of a spreadsheet error as apposed to just Excel doing a mathematical operation. With this in mind we would focus the finding the quick wins on:
- #VALUE! (Excel is expecting a number but it is getting text- did someone enter the wrong data?),
- #REF! (an important cell has been deleted),
- #NAME? (an Excel function has been used that Excel doesn’t understand- could be an Excel version issue, a missing user defined function, or just bad typing),
- #NUM! (a solution could not be found or the format of the number is an issue)
Find all the REF errors
#REF! errors means an important cell has been deleted. So below, the Formula report of the Excel Analysis tool allows you to see all #REF! errors in the spreadsheet. You can click on the hyperlink to see where it is.
Finding the #NULL!. #DIV/0!, #VALUE!, #NAME?, #NUM! and #N/A
For the other errors, short of manually going to every sheet and eyeballing them to see the errors, you can look at any sheet that is in RED. This is the Excel Auditing Tools way of saying that on this sheet there is some sort of Excel error. You can now look at only these sheets to see if it is an obvious error. Keep in mind you will need to investigate the formula correctly at a later stage. This is just the obvious errors that you can highlight quickly.
