Master the ISBLANK Function in Google Sheets

Published on

ISBLANK and COUNTBLANK are two functions that handle blank cells in Google Sheets. The former is an information function, while the latter is a mathematical function.

While the ISBLANK function checks a cell and returns TRUE if the cell is empty and FALSE otherwise, COUNTBLANK returns the count of blank cells in a range.

As this isn’t a comparison between ISBLANK and COUNTBLANK, let’s move on to discussing the usage of the ISBLANK function in Google Sheets.

Syntax:

ISBLANK(value)

Where value is the value to check.

Examples

Assume cell A1 contains a value. The following formula will return FALSE:

=ISBLANK(A1)

If you delete the value in cell A1, the formula will return TRUE.

The ISBLANK function in Google Sheets can check blank cells in a range and return TRUE or FALSE values corresponding to the values in the range. Here is one such example:

ISBLANK function in an array

How can I check if a pair of cells is not empty?

Suppose you want to verify that cells A1 and B1 are not empty and perform a task if both cells contain values. You can use either of these formulas:

=IF((ISBLANK(A1)+ISBLANK(B1)),, "do this task")
=IF(OR(ISBLANK(A1), ISBLANK(B1)),, "do this task")

Why Does the ISBLANK Function Return FALSE Even Though the Reference Cell is Empty?

Many of you may have noticed that ISBLANK returns FALSE even when the cell appears to be empty.

This discrepancy may be due to empty strings or hidden characters in the referenced cell. This often occurs when importing data into your sheet or when your formula returns empty strings. Here is an example of the latter scenario where we use an IF logical test.

The following formula in cell B1 returns an empty string (“”) if A1 contains “Verified”; otherwise, it returns “Please verify”.

=IF(A1="Verified", "", "Please verify")

If cell A1 currently contains “Verified” and you check cell B1 using the ISBLANK function, it will return FALSE.

Using ISBLANK function in data validation in Google Sheets

This explains why ISBLANK returns FALSE even though the source cell appears to be empty.

The correct way to use the IF logical test to return a blank cell is as follows:

=IF(A1="Verified", , "Please verify")

ISBLANK Function in Data Validation

Data validation aids in maintaining data consistency. Here are two examples demonstrating how to utilize the ISBLANK function in data validation in Google Sheets:

Example 1: Allow entering values in a column if rows in another column have values.

You can implement the following rule to only allow entering values in column B if column A has values.

=ISBLANK(A1)=FALSE

To apply this rule:

  • Click Data > Data validation.
  • Then click Add rule in the “Data validation rules” sidebar panel.
  • Under “Apply to range,” enter the range to validate starting from cell B1. It can be just B1, or B1:B100 (from B1 up to the row you want).
  • Under “Criteria,” select “Custom formula is”.
  • Copy-paste the above formula into the given field.
  • Check “Reject input” under “Advanced Options”.
  • Click Done.

Example 2: Allow entering values in every other row of a column that corresponds to another column.

This is a slightly more advanced data validation rule.

=AND(ISODD(ROW(A1)), ISBLANK(A1)=FALSE)

By replacing the previous rule with this one, it will permit you to enter values in B1, B3, B5, etc., provided A1, A3, A5, etc., have values.

ISBLANK Function in Conditional Formatting

Similarly to data validation, you can utilize the ISBLANK function in conditional formatting. Here is how to highlight all blank cells in a range.

Highlighting all empty cells in a range with conditional formatting

Before writing the formula, you should decide the area to highlight. It can be a single cell or a range of cells. Whichever the case, the formula should reference the very first cell in the range.

Steps:

  • Click Format > Conditional Formatting.
  • Under “Apply to Range,” enter A1 or A1:C20 (the cell or cell range that you want to highlight).
  • Under “Format Rules,” select “Custom formula is”.
  • Enter =ISBLANK(A1) in the provided field.
  • Select a formatting style and click Done.

Resources

Here are some additional resources related to managing blank cells in Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

6 COMMENTS

  1. I have 3 cells with costs.

    1 cell has the original cost, and the other 2 “could” have discount costs ($ and %) OR will be BLANK if not used.

    If there is no discount on the original cost, I want the original cost to be displayed in the totals cell.

    What would the formula be?

    D3 and D4 are the outcomes for each discount ($ and %), whereas B3 is the original cost.

    Thank you for your help.

  2. How do you use it on multiple cells at once?

    For example, if any cells of A1, A2, and A3 are blank, then return TRUE, else FALSE.

    How can you do that?

  3. If my findings are correct, conditional formatting in a cell causes ISBLANK() to return FALSE.

    For example, I use conditional formatting to gray out a cell that is empty. In the case of an empty cell, LEN() returns zero and ISBLANK() returns FALSE.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.