13. Information Functions
Information functions such as ISBLANK(), ISERROR() and ISTEXT() can be used to test the quality of an imported dataset. Find out how to use these helpful functions in this lesson.
- Enable you to check the status of a cell
- Accept a cell as an input and return TRUE/FALSE as an output
- Often located inside IF functions as logical tests
- Typically used to check the quality and integrity of an imported dataset
- Some useful functions include:
--- ISBLANK( )
--- ISERROR( )
--- ISNUMBER( )
CTRL + SHIFT + →: Select all cells within data region
CTRL + SHIFT + L: Add filters to a dataset
F2: Jump back inside a formula
Information functions are a very useful, but underused feature in Excel.
They allow you to check the status of a cell.
For example, you can check if a cell is blank, has an error, is in a number format, or a text format.
To see the full list of information functions, simply go to the Formulas tab in the ribbon, go to More Functions, and go to the Information drop down.
And this gives you a full list of all the information functions.
Almost all information functions tend to work in the same way.
The function accepts a cell, and returns true if the cell corresponds to that function, and otherwise it returns false.
Let's see an information function in action, which is blank.
So in our data set, we have a couple of order quantities that are blank, and hence the order dollar amount is blank.
If the order quantity is blank, an error has occurred, and we will need to recheck the original order.
To check if an order quantity cell is blank, we're going to use the function IS BLANK.
So I'll write “=isblank” and open a bracket.
I'll then pass in the order quantity, close the bracket and press Enter.
And because the order quantity is obviously not blank for this entry, the information function returns false.
If I auto fill for the remaining entries, you can see that we have true values when the order quantity is blank.
Although an output column showing true or false is okay, I'd prefer to have different values in the order status column.
And to do this, I'll combine an Information function with an IF function.
In reality, you'll often do this when using information functions, because a true-false output is rarely the ideal format for what you're trying to convey.
So I'll write “=F” and open a bracket.
And the logical test will simply be the information function.
If the cell is blank, I'm going to write “Re-check order quantity”.
And I'll do this in inverted commas.
And if the order quantity is not blank, I'll simply write "Ok".
I'll then close the bracket and press Enter.
And if I now copy for the remaining cells using auto fill, this gives me a much nicer output format than true or false.
Information functions are primarily used for checking the quality of a data set.
If you're importing data sets from sources such as a CSV or a Text file, you may often find blank rows, errors, or incorrect data types in a particular column.
Information functions are a great way for identifying these problem entries.
In this example, I've used IS BLANK to identify orders which do not have a quantity attached.
Once you run the information function, you can then use a filter to isolate the problem entries.
So in this example, I'll select the full data set, Ctrl + Shift + L to add filters, and then prioritize the entries with a re-check order quantity.
And for these three entries, I can now contact the customer and confirm the quantity in their original order.
Obviously, this technique becomes even more useful the larger the data set.