NULL in Excel

What is #NULL in Excel?

A #NULL error in Excel usually occurs when there is an error in typing or the syntax of a formula. The null error occurs when you miss punctuations, commas, or quotation marks in the syntax. It also appears when you type an incorrect range/cell reference or a reference name that has already been deleted. Though it is not as common as the other errors like “#VALUE!” the #NULL helps you catch and correct these mistakes.

For instance, let us look at a formula to find the sum of the integers specified in the cells below. We must use the formula =SUM(A2:A5) in cell A6. Therefore, if you miss inserting the colon and instead type =SUM(A2 A5) in A6, you get the #NULL! Error.

What is NULL in Excel?

A NULL Error in Excel occurs when there are typing mistakes resulting in an error in the syntax of a formula. For example, =SUM(A2 A3) gives an #NULL! error since the comma (,) is missing.
When the two ranges given for intersection through the intersect operator(single space) do not intersect, we get an #NULL error. These errors can be corrected using the Find and Replace option or enabling the Error checking feature.
We can check for Null cells using the ISBLANK function.

ISBLANK to Find Null Cells in Excel

Now, besides the #NULL error, we also have null cells. A null cell is a blank cell in Excel. There are many ways to find blank cells in Excel and work on them. Let us look at different methods to find Null cells.

One of the methods is through using the ISBLANK Excel function. The syntax of the ISBLANK function is =ISBLANK(value). Here, the argument value indicates the cell reference. The ISBLANK function returns a TRUE if the cell is blank, else it returns FALSE.

Let us look at an example below where we have a few blank cells while some have integers. We must apply the ISBLANK formula to find the blank cells.

Step 1: Type the following formula in cell B2.

=IF(ISBLANK(A2)=TRUE, “Cell is blank”, “Value is “&A2)

Here, we use the ISBLANK formula to check if cell A2 is blank.
If the condition is true, ISBLANK returns TRUE.
If TRUE, we print the message “Cell is blank” using the IF function.
If ISBLANK is FALSE, it prints the value in the cell.

ISBLANK to find Null cells in Excel - Step 1

Step 2: Press Enter. You get the value of 23 as cell A2 is not blank.

ISBLANK to find Null cells in Excel - Step 2

Step 3: Drag the Autofill handle from A2 to A8. You can observe the results for all the cells in this range.

ISBLANK to find Null cells in Excel - Step 3

As observed, the actual values are printed for cells if not null in Excel, while “Cell is blank” is shown for all blank ones. Even the number 0 is not treated as null, and its value is printed. Only empty cells are considered NULL.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Shortcut to Find Null Cells in Excel

We have seen how to track cells that are blank or null in Excel with ISBLANK. There is an easier way to find if a cell is blank or not using a shortcut. Let us take the example of some students who have been present for a particular class. Absent students have not been parked “Present.”. We must use a shortcut to find blank cells and mention that the student is absent.

Step 1: Now, we must find out if the student is present/absent and make some records to be sent for final verification. Type the formula =C2=”” in cell D2.

Here, the double quote “ “ is used to check whether the cell is NULL.

Shortcut to find Null cells in Excel - Step 1

Step 2: Press Enter. You get FALSE because the cell isn’t null.

Shortcut to find Null cells in Excel - Step 2

Step 3: Drag the Autofill handle up to D9. You can find TRUE for all the blank cells and FALSE for all the non-blank ones.

Shortcut to find Null cells in Excel - Step 3

How to Fix Null in Excel?

The #NULL! Error in Excel is uncommon and usually occurs when there are syntax-related issues. Now, if you encounter an #NULL error in your worksheet, how do you fix it? There are a few simple ways to do it.

#1 – Do a Simple Formula Check

Check your formulas for commas, colons, or correct cell ranges. For example, =SUM(A2 B7) will give you an #NULL! Error. To correct the error, insert a colon between the references.
=SUM(A2:B7) will give you the correct answer.
Another common reason for encountering the error is the absence of a comma. For instance, =AVERAGE(C4, C5 C6) will give you an #NULL! Error. Adding a comma, =AVERAGE(C4, C5, C6), can easily correct this error.

Thus, small changes can be made to correct and insert the proper punctuation in the formula to avoid the #NULL! Error.

#2 – Enable the Error Checking Feature

Excel provides plenty of options to understand errors such as #NULL and offers solutions to fix them. For this, you must enable the error-checking feature. First, go to the File tab.

File ——> Options.

Step 1: From here, select Formulas and check the Enable Background Error Checking box under Error Checking.

How to fix NULL in Excel - Enable the error checking feature - Step 1

Step 2: Now, let us look at an example. We have a few integers in cells A2 to A5. We must find their product. Enter the formula =PRODUCT(A2 A5) in cell A6. You get the yellow icon next to the box to indicate the #NULL error.

Enable the error checking feature - Step 2

Step 3: When you place the mouse on it, it shows the fundamental problem with your formula.

Enable the error checking feature - Step 3

Step 4: When you click on the arrow, it offers you some options.

Step 5: We chose the Help on this Error option. It helps you with all the reasons leading to the #NULL error. Here, we find that the ‘:’ is missing. Hence, you can add it to the formula and fix the error.

Enable the error checking feature - Step 5

Step 6: Change the formula in A6 to =PRODUCT(A2:A5) and press Enter.

Enable the error checking feature - Step 6

#3 – Using the Find-Replace option

Another option to fix such errors is the “Find and Replace” method. Check for the cells having the #NULL error and use the “Find Next” operation to check whether your formula in case it is missing a comma or colon or has some error in the cell ranges specified.

Examples

We can look at a few examples of handling blank or null in Excel. But, first, let us look at how we can handle n #Null error.

Example #1

Below is a table containing some numbers in rows. We must find the sum of the values interesting in the ranges we specify. We may use the Intersect operator (single space) in the SUM Excel function for this.

Step 1: We must find the sum of the cells intersecting in the range A3:C5 (red) and B4:C6 (green). The intersecting part of the ranges is specified in yellow.

Step 2: However, we enter the range improperly, for instance, =SUM(A3: C4 B5:C6) in cell B7.Press Enter. You get the #NULL! Error since there are no intersecting cells in the ranges provided.

Step 3: When you click on the arrow of the small yellow triangle, it helps you find the error in your formula and correct it.

Step 4: Correct the formula to =SUM(A3:C4 B4:C6) in cell B7. You get the sum of the four numbers in cells B4:C5 (yellow box).

Example #2

Now that we saw how to fix Null errors, let us look at how to work with null cells. Below are the details of the employees’ whose birthdays they have already celebrated in a particular year. Here, we display null in Excel for those whose birthdays are yet to be celebrated. Next, let us see how to differentiate the two easily.

Step 1: We must find the employees whose birthdays are yet to be celebrated. For this, we should enter the formula =IF(ISBLANK(C2), “To be celebrated”, “Done”) in cell D2.

Here, we check if cell C2 in Column C is blank. If so, the birthday is not yet celebrated, and we print “To be celebrated,” or else we print “Done.”
However, we also want “To be celebrated” in a different color to differentiate easily. Here, we can use Conditional formatting.
Select Conditional Formatting from the Styles group in the Home tab and choose the option New Rule.

Step 2: Choose the option “Format only cells that contain” and edit the Rule Description as follows:

Choose “Specific Text” under “Format only cells with” and enter “To be celebrated” in the text box as we want to change its color. Click on the Format button.

Step 3: Now, choose your desired color from the Font tab and press OK.

Step 4: Now, enter =IF(ISBLANK(C2), “To be celebrated”, “Done”) in D2.

Step 5: Press Enter and drag the Autofill handle from D2 to D11 to apply the formula to all the cells.

As observed, all the employees whose birthdays they are yet to celebrate have the text highlighted in a different color.

Important Things to Note

Ensure that the error-checking feature in Excel is turned on to detect the reasons for the #NULL error in Excel.
Typing errors, such as missing colons, lead to a NULL error.
We can use a shortcut =cell reference=” “ to find null cells in Excel.
Remember, if you type the space bar in a cell and still leave it blank, it is not considered an empty cell.
You can use the LEN Excel function to check for space typed to overcome this issue. It will return null in Excel only for blank cells.

Frequently Asked Questions (FAQs)

1. How to check null in Excel?

You can check for null in Excel using the ISBLANK function or the shortcut =cell reference=” “.
These two methods help you detect null cells and work on them.

2. How to use null in Excel if statement?

When a cell is null, you can use the ISBLANK() function to check its status. The function returns TRUE if the cell is null, else it returns FALSE. This output can be used in the IF statement. For example, =IF(A2=TRUE, “Blank”, “Not blank”)

3. How do you represent null in Excel?

Null is an empty cell that contains no data, including spaces. It can be detected using the ISBLANK function in Excel or the ” ” (double quotes).

4. What is not null in Excel?

A cell that contains blank spaces or any data is not considered NULL in Excel, even if it looks empty. You can use the LEN function to find if a cell contains blank spaces.

Download Template

This article must help understand the NULL in Excel with its characteristics and examples. We can download the template here to use it instantly.

NULL in Excel Download

NULL in Excel