Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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. So, let’s get started.

Overview Image


Download Practice Workbook

Download this practice file for your own convenience.


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:

Checking Null values

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 imply the ISBLANK function on 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.

ISBLANK Function for null Cells

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.

ISBLANK for Blank cells

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

 Behavior of Null and Blank Cells in Formulas

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.

Using IF and ISBLANK Functions

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:

Showing Actual Value

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.


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: 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/B5

Consequently,  press ENTER and drag it down for other cells.

Applying Division Method for Null vs Blank

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:

Dataset with Null and Blank

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.

Using IF and ISBLANK functions

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.


Practice Section

Therefore, we have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.


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

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