While working with data in Microsoft Excel, you may encounter Null values or Blank cells. There has been a lot of confusion around this about how they behave in formulas or how we can identify them. In this tutorial, we will discuss Null vs Blank in Excel with suitable examples and proper illustrations. So, let’s get started.
What Is Null in Excel?
Generally speaking, Null and Blank sometimes have the same look. You may not differentiate between them visually. But there are some differences. A null value in a cell means that the cell is not totally out of content. It has something but is not shown visually.
A simple way to check the null and blank values is to check with the ISBLANK function. The ISBLANK function checks whether a cell is blank or not.
If the ISBLANK function returns FALSE, it means the cell contains some kind of value.
If the ISBLANK function returns TRUE, it means the cell has nothing in it.
Take a look at this dataset:
Here, you can see some cells that have null values. Null values mean no meaningful values. We are considering 0 (zero) and the hyphen as null values. Apostrophe, space(s), and = “”(null strings) give us blank cells. We can’t see them in a cell. Now, let’s apply the ISBLANK function to the dataset.
📌 Steps:
- Firstly, type the following formula in cell D5.
=ISBLANK(B5)Here, the syntax returns TRUE if the regarding cell is Blank and FALSE if the cell is
- After that, press ENTER.
- Finally, drag the Fill Handle icon over the range of cells D6:D9.
As you can see, our formula is showing FALSE for every cell. That means these cells have null values.
What Is Blank in Excel?
Now, blank or empty cells mean it is devoid of any content. There are no values. Not even a single space. Blank cells and null values look similar. The only way to find them is to use formulas.
Takes a closer look. Both cells look empty. The first one has a null string and the second cell has nothing in it. That’s why the ISBLANK function returned TRUE for the blank cell.
Null vs Blank in Excel: Behavior of Null and Blank Cells in Formulas
Now, null and blank cells can create a lot of differences in the dataset. Our formulas may act differently according to the cell values. So, it is essential to identify whether the cell has a null value or a blank. Using the same formula will return different results.
To demonstrate this, we are going to use this dataset:
We have cells consisting of some null values or blank(s). Our goal is to add two numbers based on the null or blank cells. If the cell is blank, it will add Num1 and Num2.
On the other hand, if the cells are null, it will add Num2 and Num3.
Here, we will use the same formula across the dataset but you will notice it will give us different results.
📌 Steps:
- Firstly, type the following formula in cell C5:
=IF(ISBLANK(B5),SUM($C$11:$C$12),SUM($C$12:$C$13))- Eventually, press ENTER and drag it down the range of cells C6:C9.
As you can see, we used one formula but the result was different. Though all the cells look blank, they had some kind of values.
If you are in confusion thinking about what the value was, look at the following screenshot:
Now, you can clearly see that all the cells were not blank. That’s why we got separate results even after applying the same formula.
Read More: How to Deal with Blank Cells That Are Not Really Blank in Excel
Null vs Blank in Excel: How to Determine Cell Is Blank or Null?
In this section, we are going to show you if the cell is blank or null. If you have already read the previous sections, you have an idea of identify them. Good Job!
Now, we will show you two methods to identify them. We recommend you read and observe the pictures to get a better idea. We hope you will get the point.
Method 1: Dividing Number with Blank/Null Cell
This method is not the optimal way but you can consider the way of finding the difference between null vs blank in Excel. You may not see this method used by anyone. So, I recommend you learn that.
Important:
Now, why the division? We will divide a number with our cell (null or blank). Excel considers blank cells as nothing or 0. So, whenever you divide the number with the blank cell, it will show you the #DIV/0! error. That means the cell had no value.
On the other hand, if you divide the number with a non-blank or null cell, it will show a #VALUE! error. This means the cell had value but the value you are dividing with is not the same type. Follow the below steps.
📌 Steps:
- First, type the following formula in cell C5:
=10/B5Consequently, press ENTER and drag it down for other cells.
As you can see, all the cells were not blank or null. That’s why we got different errors.
Method 2: Using IF and ISBLANK Functions to Find Blank vs Null
At this moment, take a look at this dataset:
Now, if I ask you which cells are blank, can you answer that?
Seeing them visually won’t give you any idea. That’s why we are using a formula to determine that.
📌 Steps:
- Initially, insert the following formula in cell C5:
=IF(ISBLANK(B5),"Blank","Null")Here, the above syntax will show Blank if the cell is blank and Null if the cell is null.
As you can see, we have successfully found the blank and null values from the dataset.
Read More: How to Find Blank Cells in Excel
💬 Things to Remember
✎ Excel sometimes considers null strings as blank cells. So, be sure to check before using any formulas.
Practice Section
Therefore, we have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Download Practice Workbook
Download this practice file for your own convenience.
Conclusion
That’s all about today’s session. And these are some easy methods to show Null vs Blank in Excel Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.
Related Article
- Formula to Return Blank Cell instead of Zero in Excel
- How to Make Empty Cells Blank in Excel
- Return Non Blank Cells from a Range in Excel
- How to Highlight Blank Cells in Excel
- How to Set Cell to Blank in Formula in Excel
<< Go Back to Blank Cells | Excel Cells | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hello,
Thank you for this article! However, the ISBLANK function does not appear to work always. I have a spreadsheet with two columns. Some of the values in the second column look like blanks. The ISBLANK function returns TRUE for all of the cells that look blank. But the values are not all the same. I KNOW this because when I sort on that column, the blanks go to the top, the valid values (like 10, 11, 12) come next, but a bunch of other “blanks” sort to the bottom. When I do a VLOOKOP on this data, I get a blank returned for the actual blanks and I get a zero returned for the ones that are not actually blank – the rows that sort to the bottom. The cells are formatted the same: General. How can this be?Can you help? Thank you so much!
Hi, i was wondering if there is way to force Excel to ignore null cells when applying a sort function?
I have a column with a formula inside, which spits out null under certain conditions. Right now, when sorting this column, excel views these null spaces as “below “a” ” of sorts, automatically placing them all first when sorting a-z.
I’m trying to make this document in a way that even someone completely inept at the program can just fill in without having to adjust any values or formatting.
Tl;Dr : i have null spaces i can’t remove, sort function doesn’t ignore them. How can i make it ignore them/ how can i make a formula spit out a blank instead of a null?
Hello Anthony,
Please, see the forum post to ignore cells when applying the SORT function.
Ignoring Null Cells In The Sort Function
Regards,
Yousuf Shovon
Hello Anthony,
Please, see the below forum post to ignore null cells when applying a SORT function.
https://exceldemy.com/forum/threads/ignoring-null-cells-in-the-sort-function.169/
Regards,
Yousuf Shovon
Hi LESLIE,
Thank you for sharing your valuable feedback. We appreciate it very much. I understand your confusion. It is possible that a cell appears to be empty but the ISBLANK function returns FALSE for one of these 3 reasons:
● A regular space is present in the cell.
● A non-breaking space is present in the cell.
● It contains a zero-length string.
A zero-length string, also known as an empty string, is a string with zero characters; as a result, when a cell contains a zero-length string, the LEN function returns 0. In Excel, both blank and empty cells appear empty; however, blank cells include a formula or value that evaluates to or represents a zero-length string, but empty cells do not.
In this image, you can see that cell C7 appears blank, but it’s not! It contains a zero-length string created by entering a single apostrophe (‘) and formatted like other values: General.
You can find out the Excel cells which contain zero-length strings by applying the following formula:
● Insert the following formula in cell D5 and press Enter.
● Now AutoFill the rest of the column’s cells to apply the same formula.
=IF(AND(LEN(C5)=0,NOT(ISBLANK(C5))),"Zero-length string","Not a zero-length string")After that, you can remove the zero-length strings manually. Just select the cells and press the Delete key. Then sort to get the desired output.
Hopefully, you will be able to solve your problem now. Please feel free to reach out to us with any other questions or you can send us your Excel files as well.
Regards,
Nujat Tasnim
Exceldemy.