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

This video exposes the misleading Microsoft terminology for ‘Blanks’. This video examines empty cells, cells with double quotes (zero length text strings) and all the ramifications for Excel worksheet formulas. See the functions: ISTEXT, ISBLANK, COUNTBLANKS, LEN, AND, SUMIFS and COUNTIFS. Learn about the features Filter and Paste Special Skip Blanks. Learn about logical formulas and comparative operators to deal with empty cells, zero values and zero length text strings.
(00:00) Introduction
(00:36) Look at empty cells and zero length text strings and a space.
(01:18) In a formula, an empty cell always evaluates to zero.
(01:39) Empty cell and zero compared with equal sign is TRUE.
(02:00) A 'zero length text string', "", is text with zero length.
(02:04) To check if zero length text string is text, use ISTEXT function.
(02:32) To check if zero length text string has zero length, use LEN (length) function.
(02:50) To check if zero length text string is both text and zero length, use: AND function
(03:18) Text can never equate to a number.
(02:30) Text is not equal to an empty cell. But, with a comparative operator, they are!?!?!?
(04:06) 'Null text string' or 'blank' are not accurate descriptions of a 'zero length text string'.
(04:23) Microsoft helps to cause this confusion with Help:
(04:28) ISBLANK function: Blank = "empty cell". Function checks for empty cell.
(04:50) COUNTBLANK function: Blank = "empty cell or zero length text string".
(05:18) Paste Special 'Skip Blanks': Blank = "empty cell".
(06:09) Filter: Blank = "empty cell or zero length text string or space".
(06:44) Criteria in SUMIFS, COUNTIFS and the like: 1) "NOT" will return a TRUE for any cell that is NOT "empty". "=" will return a TRUE for any cell that is "empty".
(07:46) Logical formulas.
(08:00) The Logical Test: C37="", asks: "Is cell empty or Zero Length Text String"
(08:09) The Logical Test: C37=NOT"", asks: "Is cell NOT empty or NOT Zero Length Text String"
(08:33) Formula to check for empty cell.
(09:08) Formula to check if cell is not empty
(09:27) Summary of video.
(09:56) Closing and video links

[spilledgraphics]

This video is one I should have seen many years 🤯🤯🤯🤯... I think I am going to watch several times more, just to go deeper on the difference cases Mike !! thanks so much! 🙏😬👌👌

[chrism]

I was drawing a "blank" before Mike, but this cleared it up. Thanks for the great video!

[asifpervaiz]

i try to watch your videos on daily basis. i was of the view that there are probably 1500 excel videos of your. i am surprised to see your latest video . your all videos are great

[excelrobot]

Mike, you are amazing! I will add one more super crazy behavior on this topic. If you take your zero length string formula and Copy Paste Values it over itself, you will create the most baffling scenario. The resulting cell will have no formula, appear completely blank, but still exhibit all the characteristics of their being a zero length string in the cell! So you can’t even trust your own eyes to tell if a cell is blank, a formula is the only way to be 100% sure! It’s almost like the cell is haunted… Happy Halloween!

[Softwaretrain]

Woow again deep dive into great topic.
I want to add one bonus to this topic.

If you use filter function you may see blank cell become zero and sometimes it's annoying.
We just need to put all array in If function because Excel doesn't consider zero length string as 0.

For example:
=filter(if(array="", "", array), criteria)

This formula will return blank as blank, not zero.

Thanks for your training.

[funky]

On a positive note, your voice is soo catchy, I imitate the emphasis notes by you, it is quite similar to the new Batman trailer where the Riddler says, "if you think you are justice", this voice modulation reminds me yours 🤗

[KT-djiy]

Excellent, excellent review! I was just composing a question for a forum about this but you've saved me the trouble.
Unfortunately you have also exposed SO MANY other places where this can be an issue that I'm now rocking back and forwards in the corner of my office, sobbing inconsolably. I mean, that comparative operator inconsistency you describe at 3:31; WTF!?

[MrBradWilliams]

Thanks for all the great content. Here's a question for you: How do you return an empty cell with a formula? For example, say you're consolidating different parts of the sheet into one summary area. If you copy the source lines, and paste special - skip blanks & values to some other destination, you get what you'd expect. If you copy the cells that refer to the source cells and paste special - skip blanks & values to some other destination, you paste a "blank" cell over your final destination's cell contents.

[richardhay]

"So what's hard about Excel???" "NOTHING!!"

[captainsawdust]

Excellent lesson. I had no idea there were so many scenarios. Thanks.

[wayneedmondson]

Hi Mike. Awesome! Thanks for the thorough explanation of the options and tricks and traps when it comes to blank/empty/etc. cells. As always.. great stuff :)) Thumbs up!!

[shiffamohammed]

A very basic and important subject made so clear with lots of examples. Thank you so much, Mike!

[vosk]

Great video. Yeah, this problem has existed since the beginning. One other thing to note: I find it very odd that ISBLANK and COUNTBLANK evaluate to different answers when referencing a cell with a zero length text string. ISBLANK returns false while COUNTBLANK returns a 1 (or TRUE). More EXCEL misbehavior with respect to blank/empty cells. I've had to use the COUNTBLANK fx a few times now to properly return a series of trues and falses.

[CeliaAlvesSolveExcel]

Mike, you got me dizzy starting my day. This is a huge problem when we are trying to account for all the possibilities in a problem. And we didn't even explore VBA. I wonder what in VBA, equivalent to the formulas, behaves differently. I will keep this video on my list so that I can refer to it when needed.
Doubts about this topic show up every now and then.

[ivanmamchych]

Thank you, Mike. Especially for the trick with empty/non empty criteria for SIMIFS and COUNTIFS. Perhaps one day you will also explain behaviour of Pivot Tables when they sometimes return nothing and sometimes "(blank)" values.

[zaighamuddinfarooqui]

You are always "FUNTastic" 💐

[JanBolhuis]

This is a great and in-depth analysis. Thank you Mike for this great video!

[nadermounir]

Great Video 📹 Mike as we typically encounter this issue everytime we build formulas. U always touch the important stuff. Great tips 👌

[sevagj.b]

Thanks Mike, it's a lot more complicated than I thought,
But still funny 😄

[SyedMuzammilMahasanShahi]

So brilliant. Thanks Mike for this EXCELlent video.