ISBLANK Generator
Test a cell with ISBLANK, ISNUMBER, ISTEXT, or ISERROR and return your own result with IF.
ISBLANK
Test a cell with ISBLANK, ISNUMBER, ISTEXT, or ISERROR and return your own result with IF.
A single cell. ISBLANK expects one cell, not a range.
Returned when the cell is truly empty. Text needs quotes.
Returned when the cell has content - a cell reference, a number, or quoted text.
=IF(ISBLANK(A2), "Missing", A2)Worked Example
Column B holds invoice dates, and B2 was left empty because no date was entered on that row.
=IF(ISBLANK(B2), "Missing", B2)Returns: Missing - and on rows where B does hold a date, the formula returns that date instead. Format the result cell as a date if it comes back as a serial number like 46023.
Checks Before You Paste
- •ISBLANK is strict: a cell holding a formula that returns "" is not blank, so ISBLANK gives FALSE and you land in the wrong branch. When the cell may hold such a formula, test with =IF(A2="","Missing",A2) or =IF(LEN(A2)=0,"Missing",A2) instead.
- •Swap the test without changing the shape of the formula: ISNUMBER(A2), ISTEXT(A2), ISERROR(A2), ISNA(A2), or ISNUMBER(SEARCH("abc",A2)) for a case-insensitive contains check.
- •For plain error handling, =IFERROR(A2/B2,0) beats =IF(ISERROR(A2/B2),0,A2/B2): it is shorter and evaluates the expression once instead of twice. Keep ISERROR for cases where the error test is only one part of a larger condition.
How ISBLANK works
Availability: ISBLANK, ISNUMBER, ISTEXT, ISNONTEXT, ISERROR, ISERR, ISNA, ISLOGICAL, ISREF, ISEVEN and ISODD are in every version of Excel and in Google Sheets. ISFORMULA was added in Excel 2013 and is also in Sheets. ISOMITTED, which tests a missing LAMBDA argument, is Microsoft 365 only.
The IS family are the yes/no questions of Excel. Each takes one value and returns TRUE or FALSE, and on their own they are rarely the answer - they are the test inside something else. Wrapped in IF they choose between two outcomes; used as a conditional formatting rule they decide whether a cell gets highlighted; multiplied together inside SUMPRODUCT they filter rows.
ISBLANK is the strictest of them and the most misunderstood. It returns TRUE only for a cell that is genuinely empty - no value, no formula. A cell holding =IF(A2="", "", A2) contains a formula, so ISBLANK says FALSE even though the cell shows nothing, and a cell someone cleared with the space bar contains a one-character text value, so ISBLANK says FALSE there too. When the cell might hold either, test with =IF(A2="", ...) or =IF(LEN(A2)=0, ...) instead, both of which treat a real blank and an empty string the same way.
ISNUMBER is TRUE for dates and times as well as quantities, because Excel stores both as numbers - a cell showing 01/03/2026 is the number 46082 underneath. That makes ISNUMBER a good detector of numbers stored as text, which is what breaks lookups and SUM totals, and a poor detector of "is this a date". For the latter, check the cell's format with CELL("format", A2) or rely on the data's own structure.
The three error tests are not interchangeable. ISERROR is TRUE for every error type including #N/A. ISERR is TRUE for every error except #N/A, which is the one you want when a failed lookup is expected but a #DIV/0! genuinely is not. ISNA is TRUE only for #N/A. Choosing ISERROR everywhere is how a real fault gets silently converted into a friendly message and never investigated. For plain error suppression, IFERROR is shorter and evaluates the expression once instead of twice; keep the IS tests for cases where the error check is only part of a larger condition.
Syntax
=IF(ISBLANK(value), value_if_blank, value_if_not_blank)| Argument | Required | What it does |
|---|---|---|
value | Required | The single cell or expression being tested. Every IS function takes exactly one argument and returns TRUE or FALSE. Swap the test freely without changing the shape of the formula: ISNUMBER(A2), ISTEXT(A2), ISERROR(A2/B2), ISNA(VLOOKUP(...)). |
value_if_blank | Optional | IF's second argument - what to return when the test is TRUE. Text needs double quotes. Omitting it entirely makes IF return the logical value FALSE rather than nothing, so supply "" when you want an apparently empty result. |
value_if_not_blank | Optional | What to return when the test is FALSE - typically the cell itself, another formula, or quoted text. Omit it and IF returns FALSE, which is almost never what a report wants to display. |
More worked examples
C2 holds an order quantity that arrived from a CSV import, and some rows came through as text rather than numbers.
=IF(ISNUMBER(C2), "OK", "Stored as text")Returns: Stored as text on the affected rows, OK on the rest.
This is the cheapest way to find the rows that will be skipped by SUM. ISNUMBER is TRUE for dates too, so on a mixed column it flags text-dates in the same pass.
A lookup against a price list where a missing part number is expected and harmless, but a division error in the price column would not be.
=IF(ISNA(VLOOKUP(A2, $D$2:$E$99, 2, FALSE)), "Not on list", VLOOKUP(A2, $D$2:$E$99, 2, FALSE))Returns: Not on list for an unmatched part number, the price for a match, and a visible #DIV/0! if the price cell itself is broken.
ISNA rather than ISERROR is the point: an IFERROR or ISERROR wrapper here would hide the #DIV/0! behind the same friendly message and the broken price would never be noticed.
Highlighting rows in a form where the name in column B has been entered but the date in column C is still missing.
=AND($B2<>"", ISBLANK($C2))Returns: TRUE for the incomplete rows, which conditional formatting then shades.
Entered as a New Rule > Use a formula, applied to $B$2:$C$200. The mixed reference $C2 locks the column but lets the row move down the range. If column C contains formulas rather than typed dates, use LEN($C2)=0 instead of ISBLANK.
Common mistakes
- ISBLANK returns FALSE on a cell that looks empty
- A formula returning "" and a cell cleared with the space bar both count as content. The IF lands in the wrong branch and the missing values go unreported. Test with =IF(LEN(A2)=0, ...) when the cell may hold either, and use =IF(TRIM(A2)="", ...) if stray spaces are also in play.
- Giving ISBLANK a range instead of a cell
- =ISBLANK(A2:A100) does not tell you whether the range is empty. In Excel 2019 and earlier it resolves by implicit intersection to a single row, or returns #VALUE! if the formula sits outside the range's rows; in Microsoft 365 it spills a column of TRUE/FALSE. To ask whether anything is in the range, use =COUNTA(A2:A100)=0.
- ISERROR swallowing the errors you needed to see
- =IF(ISERROR(VLOOKUP(...)), "", VLOOKUP(...)) hides #REF! from a deleted column and #VALUE! from a corrupted argument as readily as it hides an expected #N/A. Use ISNA when a missing match is the only acceptable failure, or ISERR when #N/A is the one thing you want to let through.
- ISNUMBER cannot distinguish a date from a quantity
- Dates and times are numbers in Excel, so ISNUMBER is TRUE for both 46082 and 12.5. A validation rule built on ISNUMBER alone accepts a date where a quantity belongs. Add a range check - =AND(ISNUMBER(A2), A2<1000) - or test the cell's number format with CELL("format", A2).
- IF(ISERROR(x), y, x) evaluating the expression twice
- Writing the lookup once in the test and again in the result doubles the calculation work and doubles the maintenance - change one and forget the other and the formula returns a stale value with no error to warn you. =IFERROR(x, y) evaluates x once. Reach for ISERROR only when the error test is combined with other conditions.
Frequently Asked Questions
Because the cell is not empty in Excel's sense. If it holds a formula - even one returning "" - it holds a formula, and ISBLANK is FALSE. If someone pressed the space bar to clear it, it holds a one-character text value, and ISBLANK is FALSE again. =LEN(A2)=0 is TRUE for both of those and for a genuine blank, which is usually the test people actually wanted.
ISERROR is TRUE for all error types. ISERR is TRUE for all of them except #N/A. ISNA is TRUE for #N/A only. The distinction matters on lookups, where #N/A means the item was not found - normal and expected - while #REF! or #VALUE! means the formula itself is broken and someone should look at it.
Yes. Excel stores a date as a serial number counting days from 1 January 1900, so 1 March 2026 is the number 46082 and ISNUMBER reports TRUE. The same goes for times, which are stored as the fractional part of a day. There is no ISDATE function; testing =AND(ISNUMBER(A2), CELL("format", A2)<>"G") is the closest practical approximation.
Select the range, choose Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula written for the top-left cell of the selection with the row left relative, such as =ISBLANK($C2). Excel applies it across the range by shifting the row. If the column contains formulas that can return "", use =LEN($C2)=0 instead or nothing will highlight.
Not directly. ISBLANK takes a single value, and handing it a range either intersects to one row or spills an array of results. To ask whether a range is entirely empty use =COUNTA(A2:A100)=0; to ask whether it contains at least one blank use =COUNTBLANK(A2:A100)>0, remembering that COUNTBLANK counts formula-produced empty strings as blank while ISBLANK does not.
Related Tools
IFERROR Generator
Replace an error with your own value in one function rather than two.
IF Formula Generator
Build the IF wrapper that turns any IS test into a readable result.
COUNTA Generator
Count the non-empty cells across a range instead of testing one at a time.
Remove Blank Rows
Delete the empty rows from an uploaded workbook once you have found them.