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
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...
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 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:...
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<>"...
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 shown, the formula in D10 is:
{=INDEX(B3...
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...
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),"")...
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...
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),"")...
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...
To test multiple cells, and return the value from the first non-blank cell, you can use a formula based on the IF function. In the example shown, column D holds task complete dates. The formula in cell F5 is:
=IF(B5...
Related videos
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.
In this video, we'll look at a way to fill in missing data using a formula created with the IF and ISBLANK functions.
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.
In this video, we look at how to use the operator for concatenation, the ampersand character, to join values together in a formula.
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.
Related functions
