Null vs Blank in Excel

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.


Download Practice Workbook

Download this practice workbook


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 contents. 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:

dataset of null vs blank in excel

Here, you can see some cells that have null values. Null values mean no meaningful values. We are considering 0 (zero) and 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 imply the ISBLANK function on the dataset.

📌 Steps

Type the following formula in Cell D5:

=ISBLANK(B5)

formula to find blank or null in excel

After that, press Enter.

result of isblank function

Finally, drag the Fill handle icon over the range of cells D6:D10

null vs blank cells in excel

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.

checking blank cells in excel

Take 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: The 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:

dataset of null vs blank in excel

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))

if, sum and isblank formula in excel

Then, press Enter.

result of the function

After that, drag the Fill handle icon over the range of cells C6:C9

drag the fill handle to copy the formula

As you can see, we used one formula but the result is different. Though all the cells look blank, there was some kind of values in them.

If you are in confusion thinking what was the value, look at the following screenshot:

actual values of null and blank cells in excel

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.


Similar Readings:


Null vs Blank: How to Determine Cell is Blank or Null?

In this section, we are going to show you find if the cell is blank or null. If you have already read the previous sections, you got an idea of identifying 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: Divide a Number with The Blank/Null Cell

This method is not the optimal way but you can consider the way of finding the difference of 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 error “#DIV/0!”. 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.

To demonstrate, we are going to use this dataset:

dataset of null vs blank in excel

📌 Steps

First, type the following formula in Cell C5:

=10/B5

divide by the blank or null cell

Then, press Enter.

result after division

After that, drag the Fill handle icon over the range of cells C6:C9

drag the fill handle to copy the formula

As you can see, all the cells were not blank or null. That’s why we got different errors.

Method 2: Use the IF and ISBLANK Function to Find Blank vs Null

Take a look at this dataset:

dataset of null vs blank cells in excel

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

First, type the following formula in Cell C5:

=IF(ISBLANK(B5),"Blank","Null")

formula to find blank or null in excel

Then, press Enter.

result of the formula

After that, drag the Fill handle icon over the range of cells C6:C9

final result of null vs blank in excel

As you can see, we have successfully found the blank and null values from the dataset.


💬 Things to Remember

Excel sometimes considers null strings as blank cells. So, be sure to check before using any formulas.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge on null VS blank in  Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.


Related Article

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo