While working with data in Microsoft Excel, you may encounter Null values or Blank cells.
What Is Null in Excel?
A null value in a cell means that the cell is not totally out of content. It has content that is not displayed.
This is the sample dataset:
Steps:
- Enter the following formula in D5.
=ISBLANK(B5)- Press ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
The formula returns FALSE for every cell: these cells have null values.
What Is Blank in Excel?
Blank or empty cells have no content.
In the dataset above, the first one has a null string and the second cell is blank: the ISBLANK function returns TRUE.
Null vs Blank in Excel: Behavior of Null and Blank Cells in Formulas
Null and blank cells interfere with formulas.
The dataset above contains null values and blank(s).
Add Num1 and Num2 to blank cells and Num2 and Num3 to cells that are null.
The same formula will be used.
Steps:
- Enter the following formula in C5:
=IF(ISBLANK(B5),SUM($C$11:$C$12),SUM($C$12:$C$13))- Press ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
The result was different: all cells look blank, but some had values.
Read More: How to Deal with Blank Cells That Are Not Really Blank in Excel
Null vs Blank in Excel: How to Determine if a Cell Is Blank or Null?
Method 1 – Dividing the Number with Blank/Null Cell
Excel considers blank cells as nothing or 0. If you divide a number by the blank cell, it will show the #DIV/0! error.
If you divide a number by a null cell, it will show the #VALUE! error.
Steps:
- Enter the following formula in C5:
=10/B5- Press ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
Cells display different errors.
Method 2 – Using the IF and the ISBLANK Functions to Find Blank vs Null
Steps:
- Enter the following formula in C5:
=IF(ISBLANK(B5),"Blank","Null")The above syntax will show Blank if the cell is blank and Null if the cell is null.
Read More: How to Find Blank Cells in Excel
Things to Remember
Excel sometimes considers null strings as blank cells.
Practice Section
Practice here.
Download Practice Workbook
Download the practice file.
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 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.