Sign in or start a free trial to avail of this feature.
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.
Overview of Information Functions (00:03)
Information functions let you check the status of a cell. For example, you can check if a cell is blank, if it contains a number or text and so on. The functions take a cell as their argument, and return True if the criteria is met, and False otherwise.
Using ISBLANK (00:44)
ISBLANK returns True if a cell has no value, and returns False if the cell has a value. Here, we use ISBLANK to identify blank order quantities, which indicate an error has occurred.
We often use information functions as part of another function. In this lesson, we use ISBLANK as the logical test in an if function. We can then specify the text to return if a cell is blank or not blank. This allows to have more intuitive values in the output than True and False.
Adding a Filter (02:36)
We generally use information functions to quickly identify issues or errors in a data set. Once you’ve run an information function, you can create a filter to isolate the rows of interest. To do this, select the data set, apply filters with Ctrl + Shift + L, and select the category of interest from the filter. Here, we use this method to quickly identify the orders where we need to contact the customer and obtain further information.
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.