Data Validation in Excel is useful but not perfect.
It is still possible – or shall I say common? – to end up with incorrect values in cells or even just forget to apply Data Validation to an important input cell.
Excel has a set of IS functions designed to return TRUE if the cell contents are of a particular type and FALSE if not. IF functions can also be used to help check that specific values are entered.
Figure 1 – Some Excel functions return TRUE or FALSE depending on the criteria and content of the cell
As you can see, you could use this to test whether a user has entered a number or a different type of data in a cell. For example, if cell B2 contains the number 4, =ISBLANK(B2) would return FALSE, because the cell contains something, whereas =ISNUMBER(B2) would return TRUE.
And the IF function could be used to check for specific values such as our month number between 1 and 12:
=IF(AND(B3>=1,B3<=12),”Valid month number entered”,”Invalid month number entered”)
When you have a large amount of data in Excel, it’s particularly hard to see where errors might occur and it’s important to be able to flag and deal with them promptly. ISERROR and IFERROR both help identify where errors have occurred, with IFERROR also allowing you to deal with the error in the same function.
ISERROR returns the value TRUE if the cell referenced contains #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!; otherwise it returns FALSE.
Figure 2 – The function ISERROR returns TRUE if a cell contains any of Excel’s error values
ISERROR() can be combined effectively with an IF function to hide (or flag) Excel errors. Its syntax is: =IF(ISERROR([formula or cell to be checked]), [result if error], [result if not an error])
Figure 3 – ISERROR() inside of an IF function
The function IFERROR achieves the same result more concisely. Its syntax omits the need to repeat the value or reference to return if there’s no error: =IFERROR([formula or cell to be checked], [value_if_error])
Figure 4 – IFERROR() flagging errors for review
Please be aware that IF statements can easily become lengthy and complex, especially if paired with ISERROR (e.g. misplacing a parentheses or comma). Luckily recent versions of Excel color code formulas to help us keep track of cell references and parentheses.
Paolo Lenotti is the Head of Marketing & PR at Filtered.com.