How to use the Excel ISBLANK function

Summary

The Excel ISBLANK function returns TRUE when a cell contains is empty, and FALSE when a cell is not empty. For example, if A1 contains "apple", ISBLANK(A1) returns FALSE.

Purpose

Test if a cell is empty

Return value

A logical value (TRUE or FALSE)

Syntax

=ISBLANK (value)

Arguments

value - The value to check.

Version

Excel 2003

Usage notes

Use the ISBLANK function to test if a cell is empty or not.

For example, =ISBLANK(A1) will return TRUE if A1 is empty, and FALSE if A1 contains text a formula (even if the formula returns an empty string "").

It's probably best to think of ISBLANK to mean "is empty" since it can return FALSE when cells look blank but aren't. For example, if A1 contains a space character (" "), or a formula that returns an empty string (""), A1 will look blank , but ISBLANK(A1) will return FALSE in both cases.

ISBLANK formula examples

Get first non-blank value in a list

So, the gist of the problem is this: We want to get the first non-blank cell, but we don't have a direct way to do that in Excel. We could use VLOOKUP with a wildcard * (see link below), but that will only work for...

If not blank multiple cells

In Excel, empty double quotes ("") mean empty string. The symbol is a logical operator that means "not equal to", so the following expression means "A1 is not empty": =A1<>"" // A1 is not empty This...

If cell is not blank

In this example, column D records the date a task was completed. Therefore, if the column contains a date (i.e. is not blank), we can assume the task is complete. The formula in cell E5 uses the IF function to check...

If cell is blank

The logical expression ="" means "is empty". In the example shown, column D contains a date if a task has been completed. In column E, a formula checks for blank cells in column D. If a cell is blank, the result is a...

Return blank if

This formula is based on the IF function, configured with a simple logical test, a value to return when the test is TRUE, and a value to return when the test is FALSE. In plain English: if Value 1 equals 1, return Value...

Highlight blank cells

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. So, in this case the formula =ISBLANK(B4) is evaluated for...

Count line breaks in cell

First, the LEN function counts total characters in the cell B5. Next SUBSTITUTE removes all "line returns" from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows....

Check register balance

The value in G5 is hard-coded. The formula picks up the value in G5, then subtracts the value (if any) in E6 and adds the value (if any) in F6. When the credit or debit values are empty, they behave like zero and have...

Show checkmark if complete

This formula uses the IF function to check for "complete" in column C. When a cell contains "complete", IF returns CHAR(252) which displays a checkmark when the font is "Wingdings". When a cell contains any other...

Add row numbers and skip blanks

In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is: =IF(ISBLANK(C5),"",COUNTA($C$5:C5)) The IF function first checks if cell C5 has...

Calculate days open

In this example, the goal is to calculate the number of days a ticket/case/issue has been open. We start counting on the date a ticket was opened, and stop counting on the date a ticket was closed. If there is no closed...

Only calculate if not blank

The goal of this example is to verify input before calculating a result. The key point to understand is that any valid formula can be substituted. The SUM function is used only as an example. The logic can also be...

Related videos

How to trap errors in formulas

Trapping errors can make your spreadsheets more professional by making them less cluttered and more friendly to use. In this video, we'll look at some ways to trap errors in a formula.

How to join values with the ampersand

In this video we look at how to use the operator for concatenation, the ampersand character, to join values together in a formula.

How to join cell values with CONCATENATE

In this video we'll look at how to use the CONCATENATE function, an alternative to using the ampersand (&) character to join values in a formula.

How to fill in missing data part 2

In this video, we'll look at a way to fill in missing data using a formula created with the IF and ISBLANK functions.

How to build a search box with conditional formatting

In this video, we'll look at a way to create a search box that highlights rows in a table, by using conditional formatting, and a formula that checks several columns at once.

Related functions

Excel ISERR Function

The Excel ISERR function returns TRUE for any error type except the #N/A error. You can use the ISERR function together with the IF function to test for an error and display a custom message, or perform a different calculation if found.

Excel ISERROR Function

The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message, or...

Excel ISEVEN Function

The Excel ISEVEN function returns TRUE when a numeric value is even, and FALSE for odd numbers. ISEVEN will return the #VALUE error when a value is not numeric.

Excel ISFORMULA Function

The Excel ISFORMULA function returns TRUE when a cell contains a formula, and FALSE if not. When a cell contains a formula ISFORMULA will return TRUE regardless of the formula's output or error conditions.

Excel ISLOGICAL Function

The Excel ISLOGICAL function returns TRUE when a cell contains the logical values TRUE or FALSE, and returns FALSE for cells that contain any other value, including empty cells.

Excel ISNA Function

The Excel ISNA function returns TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type. You can use the ISNA function with the IF function test for an error and display a friendly message when it appears...

Excel ISNONTEXT Function

The Excel ISNONTEXT function returns TRUE for any non-text value, for example, a number, a date, a time, etc. The ISNONTEXT function also returns TRUE for blank cells, and for cells with formulas that return non-text results.

Excel ISNUMBER Function

The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number.

Excel ISODD Function

The Excel ISODD function returns TRUE when a numeric value is odd, and FALSE for even numbers. ISODD will return the #VALUE error when a value is not numeric.

Excel ISREF Function

The Excel ISREF returns TRUE when a cell contains a reference and FALSE if not. You can use the ISREF function to check if a cell contains a valid reference.

Excel ISTEXT Function

The Excel ISTEXT function returns TRUE when a cell contains a text, and FALSE if not. You can use the ISTEXT function to check if a cell contains a text value, or a numeric value entered as text.

500 Formulas | 101 Functions

Exceljet

Quick, clean, and to the point

Excel ISBLANK Function

Download 100+ Important Excel Functions

Excel video training