digestVidSign in
summary

excelisfun - Excel Formula Master Tricks for Empty Cells, Zeros, Zero Length Text String and ‘Blanks’ EMT 1764

Thumbnail of the video “excelisfun - Excel Formula Master Tricks for Empty Cells, Zeros, Zero Length Text String and ‘Blanks’ EMT 1764”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Science Technology
Tags: science, technology
Views: 2 · Last viewed: 2026-10-16 00:53 UTC

Short Summary

Main recommendation: Treat an empty cell, a zero length text string (""), and a cell with a space as different things, and use the specific Excel function or operator that matches the case you need, since Excel's use of the word "blank" is inconsistent. The video explains that "blank" is used by Microsoft to mean different things across ISBLANK, COUNTBLANK, Paste Special Skip Blanks, and the Filter menu. It shows how to test each case so your formulas count and compare the right cells.

Key Points

  • An empty cell evaluates to zero inside a formula, so comparing an empty cell to zero with the equal sign returns TRUE.
  • A zero length text string ("") is text with length zero; ISTEXT tests for text, and LEN tests for length, and AND combines both into one check.
  • Text can never equal a number, but a zero length text string compared with an empty cell using the equal sign returns TRUE, even though ISTEXT on the empty cell returns FALSE.
  • Microsoft's use of "blank" is inconsistent: ISBLANK matches only empty cells, while COUNTBLANK and the Filter Blanks option also match zero length text strings (and the Filter option can also match a space).

Details Worth Noting

  • Concrete instructions:
    • To test for a zero length text string, use ISTEXT for the text test, LEN for the length test (LEN should equal 0), and combine them with AND: AND(ISTEXT(cell), LEN(cell)=0).
    • To check for an empty cell only, use ISBLANK(cell).
    • To count everything that is not empty in COUNTIFS, SUMIFS, or MAXIFS, use the criteria "<>" (not equal to empty). To count only empty cells, use "=" as the criteria.
    • To test a cell for either empty or zero length text string in a logical formula, use cell="" (double quotes with nothing between them). To test for not empty or not zero length text string, use cell<>"".
    • To paste values while keeping existing data in cells that are empty in the source, use Paste Special and check Skip Blanks; the video shows that this skips only truly empty source cells, not zero length text strings.
  • The video demonstrates that a space typed in a cell is text with length 1, so it behaves differently from a true zero length text string.
  • The video notes that older Excel Help text stated the Filter Blanks option is available only when the range contains at least one blank cell.
  • The video states that the speaker could not find current-version Help on the Filter Blanks behavior, though he did find 2010-version Help describing it.

Bottom Line

Excel does not use one definition of "blank," so any formula that depends on blanks should test explicitly for empty cells, zero length text strings, or spaces rather than relying on a single word or menu option.