ISBLANK 与空字符串的区别
从未写入过的单元格 Z99，ISBLANK 为 TRUE，和空字符串比较也为 TRUE，LEN 为 0，ISTEXT 为 FALSE。公式返回空文本时看起来同样是空的，但 ISBLANK 为 FALSE，ISTEXT 为 TRUE，LEN 为 0，和空字符串比较仍为 TRUE。微软对 IS 函数的说明是：只有引用空单元格时 ISBLANK 才为 TRUE。
快速解答
从未写入过的单元格 Z99，ISBLANK 为 TRUE，和空字符串比较也为 TRUE，LEN 为 0，ISTEXT 为 FALSE。公式返回空文本时看起来同样是空的，但 ISBLANK 为 FALSE，ISTEXT 为 TRUE，LEN 为 0，和空字符串比较仍为 TRUE。微软对 IS 函数的说明是：只有引用空单元格时 ISBLANK 才为 TRUE。
=COUNTBLANK(B2:B6) → 2
=COUNTA(B2:B6) → 5检查要点
空格和数字 0 在两种判断下都不是空。空格的 LEN 为 1，ISTEXT 为 TRUE。0 的 LEN 为 1，ISTEXT 为 FALSE。用空字符串比较会把从未输入的单元格和返回空文本的公式当成同一类；ISBLANK 只覆盖前者。微软介绍用 IF 检查空白时也写了这两种写法。
边界与兼容性
对包含两个空字符串、一个空格、文本 001 和数字 0 的五格区域，COUNTBLANK 为 2，COUNTA 为 5。单独看 Z99，COUNTBLANK 为 1，COUNTA 为 0。微软说明 COUNTBLANK 会统计返回空文本的公式、但不统计 0；COUNTA 会统计空文本、但不统计真正的空单元格。这和本表一致。通过接口写入空字符串后，ISBLANK 仍为 FALSE，和返回空文本的公式一样；真正的空白我用从未写入的 Z99 来测。
完整英文示例、公式与作者备注
Excel ComparisonA cell can look empty and still fail ISBLANK. I have wasted time on IF(ISBLANK(A2),"please fill this in",…) only to find A2 held a formula that returned "". It looks the same. It is not the same.
I checked four ordinary values, plus a cell I had never touched (Z99).
| What is in the cell | ISBLANK | = "" | LEN | ISTEXT |
|---|---|---|---|---|
Unused cell (Z99) | TRUE | TRUE | 0 | FALSE |
="" | FALSE | TRUE | 0 | TRUE |
| A single space | FALSE | FALSE | 1 | TRUE |
Text 001 | FALSE | FALSE | 3 | TRUE |
The number 0 | FALSE | FALSE | 1 | FALSE |
The unused cell is the only one ISBLANK accepted. Microsoft’s IS functions page is blunt about this: ISBLANK is TRUE when the argument is a reference to an empty cell, otherwise FALSE. A formula that returns empty text is not an empty cell. In this file ISBLANK of ="" was FALSE, ISTEXT was TRUE, and LEN was 0.
Z99="" was also TRUE. So IF(A2="") treats a never-used cell and a formula-empty cell as the same thing. That is often what I want on a form: “nothing useful is here.” IF(ISBLANK(A2)) is the narrower test: “nobody has written a formula or a value into this cell.” Microsoft’s page on using IF to check for a blank shows both patterns; pick the one that matches the data, not the one whose name sounds right.
A space is not empty under either test. LEN was 1. If a paste put " " into a column, both ISBLANK and ="" will say the cell is occupied. Trim first if that is a real risk.
The number 0 is also occupied. ISBLANK was FALSE, 0="" was FALSE, LEN was 1, and ISTEXT was FALSE. Do not use a blank check to look for zeros.
Counting the two kinds of “empty”
I ran COUNTBLANK and COUNTA on B2:B6, a five-cell mix that included two empty strings (one from ="", one I had stored as empty text), a space, the text 001, and the number 0:
=COUNTBLANK(B2:B6) → 2
=COUNTA(B2:B6) → 5Then the unused cell by itself:
=COUNTBLANK(Z99) → 1
=COUNTA(Z99) → 0
=IF(ISBLANK(Z99),"blank","not") → blank
=IF(Z99="","empty","not") → emptyCOUNTBLANK counted both empty strings in B2:B6, and it did not count the 0. That is exactly what Microsoft’s COUNTBLANK page says: formulas that return "" are counted, zeros are not. COUNTA goes the other way: it counts a formula that returns empty text, and it does not count a truly empty cell. COUNTA(Z99) was 0; COUNTA of the five-cell mix was 5, so the empty strings were included.
If you are trying to count “rows someone still has to fill,” COUNTA will treat a helper formula that returns "" as filled. COUNTBLANK or IF(A2="") will treat it as empty. I would use ISBLANK only when I specifically care that the cell has never been written to.
One practical note from the test itself: writing an empty string into a cell through an API left ISBLANK FALSE, ISTEXT TRUE, and LEN 0 — the same profile as ="". A cell that looks blank in the grid is not always an empty cell. I used Z99, which I had never written to, as the genuine blank.
This is a different problem from a #VALUE! on a function that will not accept a blank, or from IFERROR hiding a miss. Those are in VALUE error and IFERROR vs IFNA. And a stored "" can also show up as its own “unique” value in a list; see Unique Values.