Excel Formula Master Tricks for Empty Cells, Zeros, Zero Length Text String and ‘Blanks’ EMT 1764
HTML-код
- Опубликовано: 6 июл 2024
- Download Excel File: excelisfun.net/files/EMT1764....
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
I was drawing a "blank" before Mike, but this cleared it up. Thanks for the great video!
; ) Too funny!!!!!!!!!
This video is one I should have seen many years ago...... 🤯🤯🤯🤯... I think I am going to watch several times more, just to go deeper on the difference cases Mike !! thanks so much! 🙏😬👌👌
You are welcome, Skater-Excel Bro : ) : )
Same here with me...
@@fegetables Glad the vid is here for you to learn : )
@@excelisfun I am very grateful to have your generous, very intelligent, and of course fun tutorials since 2011 till now. You have been helping me a lot in my career as Process Engineer (previously) and now as Project Engineer. Hope you and your family have more and more success in life, sir...
A very basic and important subject made so clear with lots of examples. Thank you so much, Mike!
You are welcome so much, Shiffa!!!
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!!
Thanks, Teammate Wayne!!! It is crazy how many rules and exceptions we must know to be awesome with worksheet formulas.
This is a great and in-depth analysis. Thank you Mike for this great video!
You are welcome, Jan!!!!
Awesome! The outcome is : we need practice! Thanks for this great tips.
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
I am glad to help with my over 3,400 videos ; )
Excellent lesson. I had no idea there were so many scenarios. Thanks.
You are welcome! Love the user name: Captain Sawdust : ) : )
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.
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!
That is crazy!!!!! But luckily, we can get ride of the haunted cell with the delete key on the keyboard : )
Great Video 📹 Mike as we typically encounter this issue everytime we build formulas. U always touch the important stuff. Great tips 👌
Glad to touch things that help, Nader!!!!
So brilliant. Thanks Mike for this EXCELlent video.
You are so welcome, Fellow Teacher and Teammate : ) : ) : )
I knew this should be fun. :D Thank you Mike!!!
You are welcome for the fun, Nindzsaaa!!!!
You are always "FUNTastic" 💐
Glad you like the fun : ) : )
"So what's hard about Excel???" "NOTHING!!"
You are a philosopher and artist and a written poet master. I love hanging out with you : )
Very helpful, thanks for the great video.
You are welcome!
Love your videos! Always go back to them for help! Do you have one that helps with fixing this? For example, in most of my formulas I set values equal to "". Is there a way to set values equal to nothing at all? (a true null)? When apply formulas on big tables, I always try to go to the last null value cell, but it always take me to the end of the table (given "" result formulas). Is there fix for that, so my ctrl+ down takes me to where I want to be instead? Thanks again for all your work and educating!!!
This is AWESOME! Thanks for the great tips:):):)
You are welcome, Highline Teammate!!!!!
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.
Love it!!!! Thanks for the hot tip: if(array="","",array). I actually remember learning this from the great Aladdin at the Mr Excel Message Board in about 2005 : ) Go Team!!!!
Awesome thank you Mike
You are welcome, kikti!!!
Boom!Great Explanation On This Rather Confusing Topic!Go Team...Thank You Mike :)
You are welcome for the un-confusing of this topic, darry!!!!
Fantastic explanation. (I say this as as a data scientist consulting and training in data management and business analytics!) New sub!
Glad you like it! If you want data analysis in Excel - this is the channel for you : )
Awesome video 👌 Mr perfect ❤️😍
Glad you like the video, shubham!!!!
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 🤗
Cool. I wonder what the formula result from: excelisfun=RiddlerIfYouThinkYouAreJustice would get : )
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.
So detailed video, thanks.
I met with this confusing situation when i want to choose blanks via go to special. If a blank cell came from a formula like "", i couldn't catch blanks because, excel doesn't think it is a blank cell. You could show this kind of situation too 🙂
Thanks Mike, it's a lot more complicated than I thought,
But still funny 😄
I think if we just always say "Empty Cell" and "Zero Lenth Text String", then look up help for what each functions does when we need it, things are OK. That is at least what I do lol
Thanks Mike. :) :)
You are welcome, John!!!!
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.
You are welcome. It depends on where in the PivotTable? Rows/Columns? Values area? Standard PivotTable? Data Model Power Pivot PivotTable. For example, you might have an empty cell in a Date Field, then in a Standard PivotTable if you drag date to Rows, you get a blank. As another example, in a Data Model PivotTable, if there is a relationship between a lookup table (dimension table) and a transaction table (fact table), and there is an unmatched item in fact table and you drag the dimension table field to Rows area, you get a blank. These PivotTable facts and many, many more are all in my upcoming book.
The (blank) in my pivot table right now, under rows field is driving me nuts cause it's messing up my date format. Ugh!!!
Thank you 😊
You are welcome!!!!
Thanks Mike for your effort. It's really confusing in Excel. I just dunno why the cell with text can show a zero value when I put in say cell B1 with formula =A1 where A1 contains text.
very interesting -- thanks Mike
So funny that you watched this... Why? Because you are the person that many years ago told me that it was not a blank, it was a zero length text string : ) : ) : ) : ) I love being on the Team with you, Crystal : )
oh that is funny, Mike@@excelisfun! I vaguely remember that ... and love your videos! Merry Christmas!
@@LearnAccessByCrystal It was a seminal moment in my Excel career : )
Mike, I need help with a blank cell situation where the cell that is being evaluated is a Data Validation cell. If there isn't a selection made, I want that cell to be considered blank and return nothing. Right now it returns a zero. How do I get it to return a blank. I need the blank because I'm generating an upload form from an input form. Thanks for your help!
Hi, great video… I’m trying to type IF cell G3 is blank, then the cell shows Awaiting Data… I already have three if’s (less than, equal to and greater than… but I need a cell to show awaiting data if another cell is blank) any ideas please ??
Hey! It's nice :) When I use a formula which returns NA() as result then I have the cell brakes line of text from the previous cell. E.g. A is a left cell and B is a right cell. In A here is a long string. In B a formula which returns NA(). But B brakes long string of A.
How to solve it? Is it possible? Thanks in advance.
Thats an amazing video... clears out many things just one question in a lookup function if your resultant is blank ... how you can actually get black? it actually return zero
Here is a video that shows how: ruclips.net/video/rFCWbyFz9l4/видео.html
Excellent content, thanks
Please indicate how to remove zero after logical calculations were made in a cell which is supposed to be " " ,zero text string, when a precedent cell on the formula has evaluated a " " zero text string
How to sum every cell individually to all the cells in the range and the result of this sum display in a different range
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.
Also, your video reminded me of this one by Bob Umlas: ruclips.net/video/Ikt4v86xBJw/видео.html
Thank you, Mike!
Me too. I keep this video close at hand so when I forget and can just look it up : ) I have many other much older videos on this topic also. Like EMT 784 and EMT 972. I use those as lookups too : )
@@excelisfun awesome! Thank you for those references too.
@@CeliaAlvesSolveExcel I tried to put all of the conceptual content into this new one, though... Those videos are old, like 8 - 11 years old. Back when we still used SUMPRODUCT alot : )
@@excelisfun we have come a long way!
8 years ago, I barely used Excel. I probably did not know SUMPRODUCT yet at that time. 😀
for zero I always used the cell custom style 0;0;;@ which removed the zeros. of course it has its limits. then finally we got the show zero values in cells option under advanced settings which I always untick immediately...
Thumbs up!
Thanks, Excel Teammate!!!!!
It's clear. No, I am confused. Ok, now it is clear. Nooo! I am confused. Now I don't know whether I am blank or empty!
Too funny : ) : ) : ) : ) But, wait... now I am confused too... blank or empty : )
How to insert a COUNTIF function to count values once it reaches to the first zero value to stop. And count again the cells between the first zero and second zero. Any help will be appreciated
Thanks boss for this confusing topic.
You are welcome, Syed Hassan!
@@excelisfun, Excellent video and helps in resolving confusion, hope MS will fix it up.
Boss,
New SCAN, REDUCE functions arrived in beta, please make a video on that. byrows, bycol are also new additions, kindly make a video on that too.
@@Al-Ahdal I already did. Here is one about all of them: ruclips.net/video/Eb5sXiLhUd4/видео.html
Here is one about BY ROWS for report: ruclips.net/video/qkTAtVqYXeE/видео.html (you 1st commented on this one; )
OK boss, I might missed it. Thanks for the link.
Why does sometimes a formula in a cell not do anything but if you put your curser in the cell and enter then it does? Is there some weird formatting that's not allowing the formula to work until it is activated by the curser?
I have no idea... : (
Surprising facts
Facts are like that sometimes ; )
My quickie to make sure that "cells of interest" are "truly" empty is to use a DA like Sequence(big number). All situations in your examples return a SPILL error except empty cells and SPILL will never, of course, "assign" a zero to an empty cell.
LOVE that: spill until it bumps into something : ) Thanks for the hot tip, Richard!!!
@@excelisfun you examples in the video are great but the most devastating non-empty situation and for most of us the most frequent is the SPACE character inserted by a random tap of the space bar in an otherwise empty cell rather than by =" ". Both give a blank cell. The spacebar space and ,=" " are TRUE for ISTEXT but then can be distinguished by opposing answers to ISFORMULA.
ISBLANK is not only an inaccurate name but also a cat from another mother. Specifically, EMPTY references cell content while BLANK refers to cell format. Obviously EMPTY refers to cell devoid of content while BLANK refers to a cells appearance. BLANK is normally visually obvious and unambiguous and can be a format for ANY cell content. In the extreme case a cell can be blank by making the cell color transparent or equal to the fill color. In that case no single test will detect except SPILL error or ISBLANK. Otherwise lots of guessing-testing (using each of the IS functions) and even then you are not completely sure.
@@richardhay645 Great distinction for formatted to show nothing: In that case no single test will detect except SPILL error or ISBLANK. Parallel, but related to what you say, "Quad" and "Quad " => that is the killer. I always show this example on the first day that I teach COUNTIFS and SUMIFS...
Not sure a good place to add this, but I hope Excel adds a formula that allows stacking multiple spilled results. In the meantime I've been using this custom function with good success:
Function merge(rng As Range)
Dim col As New Collection
Dim cell As Range
Dim i As Long
For Each cell In rng
If cell.Value "" Then col.Add cell.Value
Next cell
Dim a() As Variant: ReDim a(0 To col.Count - 1)
For i = 1 To col.Count
a(i - 1) = col.Item(i)
Next
merge = Application.Transpose(a)
End Function
Note: multiple ranges need to be inside parenthesis, eg. =merge((A1:A2,B1:B2))
Very, very nice!!!!! Thanks for the post. I use crazy array formulas, but that is just becasue I am not good with VBA ; ) Go team!!!!
Happy Diwali to You and Your Family Sir.
Thank you, Sir!!!!
@@excelisfun Please Sir, Don't put me in shame by calling me "Sir". I owe my career to you, "Andrew Gould" from ("Wise Owl") and "Mosh Hamedani" from ("Programming with Mosh"). You three are the World best teachers, jewel of jewels. I've been your Loyal Subscribers since 2012.
@@kartickchakraborty9135 OK Teammate : ) We are a team because I provide free education and you always click thumbs up ; )
Great video !!✌When it comes to formulas the problem is more simple than it looks.
A blank is a null string("") and a 0 ON The Same Time.
If a cell (A1) is a blank =AND(A1="",A1=0) it will return a TRUE
Simply calling a range in a formula using a variable : =LET(a,range,a), and the range has blanks ,the formulas returns 0's instead
If "a" will be involved in calculations inside a formula, if text calculations, initial blanks will be considered null strings, if numeric, blanks will be considered 0's.
NO result of ANY function or formula, can return blanks. Kind of saying, blanks are living only in ranges.
If we want to keep the initial structure of blanks, as null strings, we only have to use this IF(a="","",a) inside formulas, whenever is needed, could be at the very end of a formula, or before that when "a" calculations are involved.
Like in =LET(a,range,IF(a="","",a))
Or other simple example =LET(a,A1:A6,TRANSPOSE(IF(a="","",a)))
Note: Never use this form =LET(a,A1:A6,t,TRANSPOSE(a),IF(t=0,"",t)) because if the range has initial 0's , will be converted to null strings (""), and we do not want that. ✌
I can a FALSE when ="" is in cell A1 and I try: =AND(A1="",A1=0)
Thanks for this hot tip to keep the "" : =LET(a,range,IF(a="","",a))
Go Team!
@@excelisfun We get a FALSE because if A1="" is not blank anymore, it has text, a null string. Blank is only when both conditions return TRUE ✌😉
@@Excelambda O, I thought you said it would be TRUE. You are using the words like blank and null string... lol
Who are you? and do you need a job???
@@excelisfun -blank has nothing in it (empty cell) ; but, is a null string and a 0 on the SAME time because for a blank =AND(A1="",A=0) always return TRUE
- null string A1="" is not a blank , A1=0 part returns FALSE so =AND(A1="",A=0)=FALSE, so no blank
Many times, even if is not corect, by blank we understand empty cell or cells that contain null strings. For example , in a single cell formula result , if we say, the array result has 3 blanks , it is obvious that they are actually null strings (no formula or function can return a real blank, only null strings).
If we refer to a range that has 3 blanks, we have to be more acurate, since a range can have empty cells or null strings , and they look alike unless we test them.✌😉
You should do a excel format for code officer keeping tract of code violation and property address. Be able to search code# and get list of addresses and or search a address and get a list of violations. I do a year on a sheet. I also count specific code# to get a monthly count. If u need more info let me know. Alot of us code officers just using plain old excel to keep tract.
We also do a rental registration and property inspections excel sheet.
Thank u. Great vids.
Great example of using Excel in a great way!!! Go Team!!!!
@@excelisfun I was asking for help.
@@magoo_1990 I am booked up for months... But no worries, try this awesome Excel question site (great for back and forth dialog to get Excel solutions): mrexcel.com/board
I love you
I love you too! Excel technical knowledge rules!!!!
Unrelated to this video. The formula
=STOCKHISTORY("AAPL","1/20/2021","11/3/2021",0,0,0,1)
yields a 2 col x 200 row array. I want to use a single cell formula to feed those 2 columns into the CORREL function, but simply putting the STOCKHISTORY formula into the function which requires 2 arrays, but that doesn't work. I'm thinking I need to use the BYCOL to get LAMBDA to get CORREL to recognize the two arrays, but I'm not figuring it out. My attempt:
=BYCOL(STOCKHISTORY(A1,"1/20/2021",TODAY(),0,0,0,1),LAMBDA(a,b,CORREL(a,b)))
yields #VALUE! I'm looking for the ROUND(CORREL(a,b)^2,4) value which is the same as the linear trend line R^2 value of the values charted. TIA!
Maybe:
=CORREL(INDEX(STOCKHISTORY("AAPL","1/20/2021","11/3/2021",0,0,0,1),,2),INDEX(STOCKHISTORY("AAPL","1/20/2021","11/3/2021",0,0,0,1),,1))
My question now is, how to set a cell to blank (reads as an empty cell) using a formula.
1. Blank/Empty Cells- Absolutely nothing i.e. new cell before you do anything on it =SUM(ISBLANK(C3:C7)*1)
2. Single Space- Only Single space =COUNTIFS(C3:C7," ")
3. Multiple Space-Only Multiple spaces =COUNT(ISNUMBER(LEN(C5)-LEN(SUBSTITUTE(C5," ","")))*1)
4. Zero Length Text String- Is equal to double quotes =COUNTBLANK(C3:C7)-SUM(ISBLANK(C3:C7)*1)
5. Zero Number- Contains 0 as number =COUNTIFS(C3:C7,0)
🥴🥴🥴still there is a room for improvement 😄😄
I am not sure exactly what you are communicating, but what you say is always true : )
@@excelisfun I thought I was master in excel. When I watch this video I came to know that there is more to learn 😀
@@sandipkh2282 Me too : ) I am just a guy having fun with Excel with so much, much more to learn!!!
OMG
And if you do not choose "show zero values" at advanced options, it is a whole different story.
it is probably because some Excel formulas or filters are looking at cell values, some at whats shown in screen.
Also need to check if special formula is used for blank cells.
I am not sure what you mean by ""show zero values" at advanced options". Can you explain further?
@@excelisfun When "Show a zero in cells that have zero value" option is unselected and blank cells are filtered, excel shows zero cells as well as blank cells.
Similarly, if custom formatting is applied, excel filters cells by their display not by their values.
By the way, I was wrong in my first comment. When I checked in my computer later, this situation does not affect the LEN, ISBLANK, EXACT, and other excel functions. Only filtering evaluates the display value rather than the cell value or data type.
Thanks for this explanatory video.
It is best to check excel models with different possible scenarios including blank, zero, and other values of cells before reporting.
@@MustafaKalafat Thanks for the tips : ) The filter feature is a strange tool - it does things that other tools do not, such as see number formatting. Formulas do not act on number formatting.
All thanks to Microsoft😂
LOL