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.

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

If cell is not blank

To take an action only when the cell is not blank (not empty), you can use a formula based on the IF function. In the example shown, column D holds task complete dates. The formula in cell E5 is: =IF(D5<>"...

Show checkmark if complete

To show a checkmark when a task is complete, you can use a formula based on the IF and CHAR functions. In the example shown, the formula in D5, copied down, is: =IF(C5="complete",CHAR(252),"")...

Check register balance

To set a check register formula that calculates a running balance, you can use a formula based on simple addition and subtraction. In the example shown, the formula in G6 is: =G5-E6+F6 How this formula works The...

Get first non-blank value in a list

To get the first non-blank value (text or number) in a in a one-column range you can use an array formula based on the INDEX, MATCH, and ISBLANK functions. In the example the formula we're using is: {=INDEX(B3:...

Return blank if

To return a blank result using the IF function, you can use an empty string (""). In the example shown, the formula in D5 (copied down) is: =IF(B5=1,C5,"") How this formula works This formula is based on...

Count total words in a cell

To count the total words in a cell, you can use a formula based on the LEN and SUBSTITUTE functions. In the example shown, C3 contains this formula: =LEN(TRIM(B3))-LEN(SUBSTITUTE(B3," ",""))+1 How...

Highlight blank cells

If you want to highlight cells that are blank or empty with conditional formatting, you can do so with a simple formula based on the ISBLANK function. For example, if you want to highlight blank cells in the range B4:...

Count line breaks in cell

To count total lines in a cell, you can use a formula based on the LEN, SUBSTITUTE, and CHAR functions. In the example, we are using this formula: =LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),""))+1 How the...

If cell is blank

To test a cell and take some action if the cell is blank (empty), you can use the IF function. The formula in cell E5 is: =IF(D5="","Open","Closed") How this formula works The logical...

Only calculate if not blank

To run a formula only when one or more cells are not blank, you can use the IF function with an appropriate logical criteria. In the example shown, the formula in E5 is: =IF(COUNT(C5:C7)=3,SUM(C5:C7),"")...

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 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.

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 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.

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.

Good links

Microsoft ISBLANK function documentation

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.