Excel 2010: Checking Logical & Syntax Errors
While getting your head down on a huge spreadsheet which contain maze of values, data, text, etc, then it is quite possible that you missed out logical & syntax errors. As logical errors can not be identified by Excel compiler, you need to write some formulas which can point out any blank space, negative value, or even #NA! and #DIV/0! errors. Thanks to Excel built-in functions which can easily get rid of these anomalies in the spreadsheet. This post will cover how to use Excel built-in functions to identify errors.
Launch Excel 2010 spreadsheet on which you want to check data for errors. We have included a table having single column, as shown in the screenshot below.
Now we will be checking the values in a column, if they are Negative, Positive or zero, or cell is BLANK. Now for this, we will create a new row label at location B1, by the name of Check, and we will be writing formula in the first row of this field.
We will simply write IF function, encompassing different scenarios.
=IF(ISBLANK(A2),”Blank”,IF(A2<0,”Negative”,”Positive or zero”))
We have mainly used two IFs in the formula, First one checks for Blank cell, the second one checks for negative and Positive values. However, you can add another IF condition, especially for zero.
It will check the value in location A2, and upon the criteria set in the formula, yield the result, as shown in the screenshot below.
Now drag the plus sign at the end of the cell, towards the end of the column to apply it over.
Where the value is less than zero, it shows Negative, where we have left blank cell , it shows Blank, and for rest of the type of data, it shows positive or zero. For error handling we will be including a formula which identify the errors in the datasheet and show keyword Error in the cells.
We will be checking a number for division by zero and which eventually produces the result #DIV/0!, we will only be checking this message when it erupts. So, we will write a simple formula which will check for error and when it encounters an error it will show TRUE, in it’s corresponding cell.
=ISERR(A2)
As in formula we are checking values in field Values for any error.
Now drag the plus sign at the end of the cell towards the end of the column to apply it over.
We have included a new column with label Errors in the table. In this field, we will be checking if there is any cell containing TRUE in its next field. Where it will find TRUE that’s mean the Values column contain an error.
The formula goes like this;
=IF(D2=TRUE, “Error”,””)
This will show Error where it will find TRUE, and leave cell blank if the statement is FALSE .
So after applying it over the whole Errors column, the spreadsheet will look something similar to what is shown in the screenshot below.
This will show Error only if #DIV/?0!, and #NAME? appears in the Value field.
You can also check out previously reviewed guides on Data Validation In Excel 2010 & Password Protection in Excel 2010
