Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Information Under review

ISBLANK

ISLEEG in Dutch Excel

Tells you whether a cell is truly empty: TRUE if nothing at all is in it, FALSE for anything else, including a space or empty text.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: E2 holds the formula
Row	A	B	C	D	E
1	Entry	Journal	Amount	Approved by	Approver missing?
2	25-0501	MEM	€18,250.00€ 18.250,00		TRUETRUE=ISBLANK(D2)=ISBLANK(D2)
3	25-0502	MEM	€4,300.00€ 4.300,00	SvdV
4	25-0503	INK	€920.50€ 920,50	AY
5	25-0504	MEM	€61,000.00€ 61.000,00	TB
6	25-0505	BNK	€1,575.00€ 1.575,00

Result: TRUE

=ISBLANK(D2)

=ISBLANK(D2)

What ISBLANK does and when to use it

What it does: it tells you whether a cell is truly empty, for example whether a journal entry has an approver.

When to use it: for completeness tests on extracts, such as a missing approver or an empty cost center, and when you must tell an empty cell from a typed 0. For cells that only look empty (a space, or empty text from a formula), test TRIM(x)="" instead.

How Excel evaluates it

Excel takes the reference you give and looks inside that cell. If the cell has no content of any kind, no number, no text, no formula, ISBLANK returns TRUE.

If the cell holds anything, ISBLANK returns FALSE. It never compares values, it only checks whether the cell has content.

Watch out: A cell that looks empty is not blank if it holds a space, an apostrophe or a formula whose result is empty text: ISBLANK returns FALSE for it.

Mental model

A cell is a drawer. ISBLANK asks whether the drawer is empty. A drawer that holds a blank sheet of paper (a space, or empty text) is not empty, even though nothing is written on the paper.

Terms used on this page: reference, cell

Syntax

Official ISBLANK documentation

ISBLANK(value)

ISBLANK(value)

Arguments
Argument	Type	Meaning
`value`	Required	The cell to test, usually a cell reference.

Return value:TRUE for a truly empty cell, FALSE for anything else

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Spot a journal entry without an approver

In the XAF auditfile of Veldhuis Installatie BV every entry carries the initials of the person who approved it. For your journal entry testing you want to know per entry whether the approver column is empty. One formula, copied down, gives a TRUE or FALSE for each line.

Sheet excerpt: E2 holds the formula
Row	A	B	C	D	E
1	Entry	Journal	Amount	Approved by	Approver missing?
2	25-0501	MEM	€18,250.00€ 18.250,00		TRUETRUE=ISBLANK(D2)=ISBLANK(D2)
3	25-0502	MEM	€4,300.00€ 4.300,00	SvdV
4	25-0503	INK	€920.50€ 920,50	AY
5	25-0504	MEM	€61,000.00€ 61.000,00	TB
6	25-0505	BNK	€1,575.00€ 1.575,00
7

Formula cell (E2)Input

Formula in E2

=ISBLANK(D2)

=ISBLANK(D2)

Result: TRUE

Step by step

ISBLANK has one argument, value. Here it is D2, the Approved by cell of entry 25-0501.
Excel looks inside D2. There is no number, no text and no formula in it, so the cell is truly empty and ISBLANK returns TRUE.
Copied down, E3 looks at D3, which holds SvdV. The cell has content, so E3 returns FALSE. E4 and E5 return FALSE as well (AY and TB).
E6 looks at D6, which is empty again, so it returns TRUE. Entries 25-0501 (a manual entry of 18,250.0018.250,00) and 25-0505 are the two to follow up.

Watch out: If someone typed a single space in D2, the cell looks exactly the same but ISBLANK returns FALSE. The Advanced example shows how to find those cells.

Separate a missing book value from a book value of 0

The client sent a fixed asset register (activastaat) with the book value at 31 December 2025. A fully depreciated asset correctly shows 0, but a few lines were never filled in. You want a label per asset: Filled in or Missing, so that only the blanks go on the list of questions for the client.

Sheet excerpt: D2 holds the formula
Row	A	B	C	D
1	Asset	Description	Book value 31 Dec 2025	Status
2	FA-0101	Delivery van		Missing=IF(ISBLANK(C2), "Missing", "Filled in")=IF(ISBLANK(C2); "Missing"; "Filled in")
3	FA-0102	Workshop lathe	€0.00€ 0,00
4	FA-0103	Forklift	€8,450.50€ 8.450,50
5	FA-0104	Pallet racking
6	FA-0105	Office furniture	€12,300.00€ 12.300,00
7

Formula cell (D2)InputNote: C2 and C5 are empty. C3 holds a typed 0, shown as 0.00.

Formula in D2

=IF(ISBLANK(C2), "Missing", "Filled in")

=IF(ISBLANK(C2); "Missing"; "Filled in")

Result: Missing

Step by step

IF evaluates its first argument first: ISBLANK(C2). C2, the book value of the delivery van, has nothing in it, so ISBLANK returns TRUE.
The test is TRUE, so IF returns its second argument, the text Missing, in D2. The text Filled in is not used for this row.
D3 looks at C3, the lathe. C3 holds a typed 0. A 0 is a value, so ISBLANK returns FALSE and IF returns Filled in. A fully depreciated asset is not reported as missing.
D4 returns Filled in (8,450.508.450,50), D5 returns Missing and D6 returns Filled in (12,300.0012.300,00). FA-0101 and FA-0104 are the two assets to ask the client about.

Watch out: If a pasted-as-values extract leaves empty text in a cell, ISBLANK returns FALSE and the asset is labeled Filled in although nothing is there. Spot-check the labels against the book value column before you rely on them.

More on this example: why it works, 1 variation

Why it works

ISBLANK gives IF a clean TRUE or FALSE, and it treats a typed 0 as content. If you tested C2=0 instead, Excel would treat the empty cell as 0 in the comparison, and the lathe would be reported as missing along with the two real gaps.

Variations

=IF(C2="", "Missing", "Filled in")

=IF(C2=""; "Missing"; "Filled in")

Comparing with empty text treats a truly empty cell and a cell with empty text alike, and keeps a typed 0 as filled in. Prefer it when the extract may contain empty text.

Advanced

Count cells that look empty but are not blank

A list of 8 manual entries has an Approved by column. A colleague counted the empty cells by eye and got 4, but ISBLANK flags only some of them. Before you report a completeness finding you want to know how many cells look empty without being blank, because those are the ones a plain ISBLANK test would miss.

Sheet excerpt: D2 holds the formula
Row	A	B	D
1	Entry	Approved by	Hidden gaps
2	25-0731	JK	22=SUMPRODUCT(--(TRIM(B2:B9) = "")) - SUMPRODUCT(--ISBLANK(B2:B9))=SUMPRODUCT(--(TRIM(B2:B9) = "")) - SUMPRODUCT(--ISBLANK(B2:B9))
3	25-0732
4	25-0733
5	25-0734	MV
6	25-0735
7	25-0736	RB
8	25-0737
9	25-0738	JK
10

Formula cell (D2)InputMarks: extra spaceNote: B3 and B8 are empty. B4 holds a single space. B6 holds empty text, as left behind by a formula that returned "" and was pasted as values.

Formula in D2

=SUMPRODUCT(--(TRIM(B2:B9) = "")) - SUMPRODUCT(--ISBLANK(B2:B9))

=SUMPRODUCT(--(TRIM(B2:B9) = "")) - SUMPRODUCT(--ISBLANK(B2:B9))

Result: 22

Step by step

Excel evaluates the first SUMPRODUCT from the inside out. TRIM(B2:B9) removes spaces from each of the 8 cells and returns JK, an empty text for B3, an empty text for B4, MV, an empty text for B6, RB, an empty text for B8 and JK.
The comparison = "" turns that into FALSE, TRUE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE. The double minus changes TRUE to 1 and FALSE to 0, and SUMPRODUCT adds them: 4. Four cells look empty.
ISBLANK(B2:B9) tests the same 8 cells for real emptiness: FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE. Only B3 and B8 have no content at all, so the second SUMPRODUCT returns 2.
The subtraction is 4 - 2 = 2. Two cells look empty without being blank: B4 with the space and B6 with the empty text.
The finding is 4 entries without a visible approver, of which 2 would be missed by a test built on ISBLANK alone. Change B4 to JK and the result drops to 1.

Watch out: TRIM does not remove a non-breaking space (CHAR(160)), which often arrives from web and ERP exports. Such a cell passes as filled in both tests, and the formula silently returns a number that is too low.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

The two tests answer different questions about the same cells: TRIM(x)="" asks whether the cell looks empty, ISBLANK asks whether it is empty. Their difference isolates the cells that pass the first test but fail the second. SUMPRODUCT handles the ranges in every Excel version, without array entry.

More to watch out for

ISBLANK(B2:B9) on its own in a cell spills 8 answers in Microsoft 365 and returns one answer for its own row in older versions. Keep it inside SUMPRODUCT, as here.

Variations

=SUMPRODUCT(--(LEN(TRIM(B2:B9)) = 0))

=SUMPRODUCT(--(LEN(TRIM(B2:B9)) = 0))

The same count of 4 cells that look empty, written with LEN. Some reviewers read it more naturally than a comparison with empty text.

=COUNTBLANK(B2:B9)

=COUNTBLANK(B2:B9)

Returns 3: it counts B3, B8 and also the empty text in B6, but not the space in B4. It sits between the other two answers, so do not mix it up with ISBLANK.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Tell apart not listed, no approver and approved, in one lookup

For a selected entry number the sheet must show one of three outcomes:

the entry is not in the approval list
the entry is listed but nobody approved it
the approver's initials

The second outcome is the audit finding, so it must not be confused with the first or hidden behind a 0. The formula has to stay readable for the reviewer after you.

Sheet excerpt: F2 holds the formula
Row	A	B	C	E	F
1	Entry	Journal	Approved by	Entry	Result
2	25-0620	VRK	MV	25-0624	No approver=LET( pos, IFNA(MATCH(E2, A2:A8, 0), 0), IF(pos = 0, "Not in list", IF(ISBLANK(INDEX(C2:C8, pos)), "No approver", INDEX(C2:C8, pos))) )=LET( pos; IFNA(MATCH(E2; A2:A8; 0); 0); IF(pos = 0; "Not in list"; IF(ISBLANK(INDEX(C2:C8; pos)); "No approver"; INDEX(C2:C8; pos))) )
3	25-0621	MEM	JK
4	25-0624	MEM
5	25-0627	INK	RB
6	25-0630	MEM	JK
7	25-0633	BNK	MV
8	25-0636	MEM	RB
9

Formula cell (F2)Input

Formula in F2

=LET(
  pos, IFNA(MATCH(E2, A2:A8, 0), 0),
  IF(pos = 0, "Not in list",
    IF(ISBLANK(INDEX(C2:C8, pos)), "No approver", INDEX(C2:C8, pos)))
)

=LET(
  pos; IFNA(MATCH(E2; A2:A8; 0); 0);
  IF(pos = 0; "Not in list";
    IF(ISBLANK(INDEX(C2:C8; pos)); "No approver"; INDEX(C2:C8; pos)))
)

Result: No approver

Step by step

LET defines pos first. MATCH looks for 25-0624 in A2:A8 with match type 0 (exact) and finds it in the third cell, A4, so MATCH returns 3. IFNA only steps in when MATCH fails, so pos is 3.
The outer IF tests pos = 0. That is 3 = 0, which is FALSE, so Excel moves on to the inner IF.
The inner IF first evaluates ISBLANK(INDEX(C2:C8, 3)). INDEX hands back the cell itself, C4, not a copy of its value. C4 is empty, so ISBLANK returns TRUE.
The inner IF returns its second argument: No approver. The third argument, INDEX(C2:C8, pos), is never evaluated for this entry.
Change E2 to 25-0621 and ISBLANK returns FALSE, so the formula shows JK. Change it to 25-0999 and MATCH fails, IFNA turns that into 0, and the formula shows Not in list.

Watch out: The mirror-image mistake: put VLOOKUP inside ISBLANK, or add &"" to the lookup, and ISBLANK always returns FALSE, because a value has been handed over, not the cell. The empty approver then shows as 0 or as empty text, and the finding disappears without an error.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

ISBLANK needs a cell, not a value. INDEX returns a reference to the cell it points at, so ISBLANK can still see that C4 is empty. Without that, the empty cell would arrive as a 0 and the finding would disappear.

IFNA turns a failed MATCH into 0 so that 'not listed' and 'listed' are separated before the blank test. Do not use this construction for a one-off look at a single entry: a plain INDEX and MATCH, or XLOOKUP with &"" added, is shorter there, but it cannot tell a space or empty text from a truly empty cell.

More to watch out for

If an approver cell holds only a space, ISBLANK returns FALSE and the formula shows an empty-looking name instead of No approver. Test TRIM(INDEX(C2:C8, pos)) = "" when the extract may contain spaces.
MATCH takes the first of duplicate entry numbers. If a number appears twice and only the second line is blank, the formula does not see it.

Variations

=LET(
  pos, IFNA(MATCH(E2, A2:A8, 0), 0),
  IF(pos = 0, "Not in list",
    IF(TRIM(INDEX(C2:C8, pos)) = "", "No approver", INDEX(C2:C8, pos)))
)

=LET(
  pos; IFNA(MATCH(E2; A2:A8; 0); 0);
  IF(pos = 0; "Not in list";
    IF(TRIM(INDEX(C2:C8; pos)) = ""; "No approver"; INDEX(C2:C8; pos)))
)

Treats a space and empty text as 'no approver' too. Choose this when 'looks empty' is the audit question; it works on values, so it also survives a lookup function.

Common mistakes

Common mistakes
Symptom	Cause	Fix
FALSE on a cell that looks empty	ISBLANK returns FALSE on a cell that looks empty, for example after a formula was pasted as values and left empty text behind.	Decide what you mean. For 'truly empty' keep ISBLANK. For 'looks empty' use TRIM(D2)="", which is TRUE for all of these cases. The Advanced example counts the difference.
A real 0 is reported as missing	You find blanks with D2=0 and a cell holding a real 0 is reported as missing too, because Excel treats an empty cell as 0 in a comparison.	Use ISBLANK(D2). It separates a blank from a typed 0, which matters when 0 is a valid answer such as a fully depreciated asset.
A list of TRUE and FALSE, not a count	ISBLANK(D2:D9) is meant to count the blanks, but you get a list of TRUE and FALSE (Microsoft 365) or a single answer for the row the formula sits on (older versions).	ISBLANK tests one cell at a time. To count, use SUMPRODUCT(--ISBLANK(D2:D9)) or COUNTBLANK(D2:D9).