The Microsoft Excel ISBLANK formula is an often underused one, but it can be useful for all sorts of dynamic spreadsheets. Whether you want to make sure you didn't miss any data in a lengthy list, or find all the cells that need updating using conditional formatting, the ISBLANK function in Excel is a great way to do it.
Here's how to make the most of the Excel ISBLANK function.
Although the screenshots for this guide are for Excel 365, and the instructions work just the same in Excel 2016 and 2019, the menus may just be laid out slightly differently.
Get Started Using Formulas in ExcelWhat Is the ISBLANK Function?
The ISBLANK formula checks to see if a cell is blank. That is, it looks to see whether there has been any entry in a cell or not (that includes spaces, line breaks, or white text you can't see) and returns a value of false, or true, respectively.
The generic formula for it is:
=ISBLANK(A1)
The A1 here, can be supplanted for any range or cell reference.
How to Use the ISBLANK Function in Excel
There are a number of different ways you can use ISBLANK for all sorts of ends, but a simple example situation is to find whether a range of cells are empty or filled. This can be very useful if you need a database to be complete, but combing through it by hand would take a while.
In this example we'll be using a sample data set that includes a range of Data that could represent anything in reality. In the B column we use the following formula:
=ISBLANK(A2)
Copying and pasting that formula throughout the entire Needs Data Column supplants the cell for the successive cell in the corresponding Data range. This returns a result of False in any rows that do have data, and True in cells that don't suggesting data must be entered.
This is an extremely simple example, but could easily be applied to make sure that a cell is truly empty (rather than just appearing so with spaces or line breaks), or combined with other functions like IF or OR for more expansive and nuanced uses.
How to Use the ISBLANK Function for Conditional Formatting
Determining if a cell is blank can be extremely useful, but if you don't want to have a long list of FALSE and TRUE text in another column, you can always use conditional formatting.
Taking our original example, we can apply the same formula to a conditional formatting rule, which gives us the original list, but with color coded cells to highlight that they are empty.
-
Select the Home tab.
-
In the Styles group, select Conditional Formatting > New Rule.
-
Select Use a formula to determine which cells to format.
-
In the Format values where this formula is true: box, enter =ISBLANK(A2:A33).
The range stated in this formula is for our example. Replace it with your required range.
-
Select Format, then choose an obvious identifying color, or other format change to help highlight cells.
-
Select OK, then select OK again. The formula will then apply to your chosen range. In our case, it highlighted the empty cells red.