#NULL! Error In Excel – How To Fix

The #NULL! error is less common than other errors like #VALUE! or #NUM! errors. The error is basically Excel telling you that you’ve supplied an invalid range operator in the formula you’ve used in a particular cell. In most cases, you’ll see a #NULL! error when you’ve inserted a space, comma, or semicolon incorrectly in a formula. But what exactly is the #NULL! error?

What is #NULL! Error in Excel?

The #NULL! error signifies that you’ve committed a typing error while inserting your formula. In most cases, fixing #NULL! error just requires that you verify the accuracy of the formula. Before you go ahead and check a formula though, you’ll need to know the potential causes of a #NULL! error.

Why Does it Appear?

#NULL! error occurs when you’ve entered the name of the range, haven’t added double quotes for a text string, or referred to a range name that doesn’t exist or has been deleted. It can also occur when you’ve used an intersection operator between two non-intersecting range references.

For instance, say you want to sum the cell range A3 to A7 and you’ve used the following formula:

You’ll receive a #NULL! error because there’s no colon between the cell references. If you’re wondering how to get rid of the #NULL! error, we’re going to walk you through a couple of examples so you can address the #NULL! error for pretty much any formula.

Examples

Now, let's go through a few examples and try to understand how to fix the #NULL! error.

1. Skipping a Colon

Let’s talk about the formula we just discussed. Notice how the range entered in the SUM function formula has no colon, but instead has a space in between the first and last cell reference. To fix the formula, you only need to enter a colon between those cell references, like so:

Just doing this will fix the #NULL! error for this formula.

2. Skipping a Comma

Excel also returns a #NULL! error when you skip a comma and enter a space character instead. For instance, say you’re summing two cell ranges, A3:A7 and B3:B7, but you incorrectly enter the following formula:

The cell ranges inside the SUM function haven’t been separated by a comma. Instead, there’s a space character between them. That’s no good; you’re going to get a #NULL! error.

The solution, of course, is just to add the comma, like so:

You’ll now see the correct output as 30, which is the summation of both the cell ranges you’ve entered.

3. Space Characters Between Non-Intersecting Ranges

Whenever you enter a space character into your Excel formula, Excel reads it as an intersect operator. So, what’s an intersect operator?

Well, say that you have numeric data in three columns A, B, and C. You want to sum only that data that intersects between column A, column B, and row 5. Now, this may be simple on small data sets because you can just sum up the fifth row for columns A and B. On larger data sets though, you’ll need a more robust, scalable formula. So, how do you do this with a formula where there’s minimal manual effort?

Tricky, huh?

Well, not if you know how to use the intersect operator. Here’s the formula you could use for this summation:

What the formula does is that it looks at the data in cells starting from cell A3, right down to cell B7. Then it screens these cells to verify intersection with cells A5, B5, and C5, since we’ve used the space character (i.e., intersect operator). It then sums up all values that are in cells that intersect. In this case, the sum would be 6.

However, this formula would return a #NULL! error had you used this formula:

Why?

Well, none of the cell ranges are intersecting based on the supplied data. This example is fundamentally different from the previous two because you haven’t committed any errors in typing the formula. Instead, you’ve supplied the data such that there’s no intersection of data.

It’s helpful to look at the cells highlighted by Excel as you type the formula. If the cell ranges don’t intersect, you’ll be able to eyeball the #NULL! error from a mile away. However, if you do get the #NULL! error, you know how to fix it.

Okay, so that’s all you need to know to fix the #NULL! error on your spreadsheet. If you have a couple of spreadsheets lined up with #NULL! errors, see if you can resolve them by using the examples given here as a reference.

Remember, there are usually (though not always) two reasons that cause a #NULL! error: incorrectly inserting a space character instead of another operator, or using the intersect operator for ranges that don’t intersect. In other cases, you should check if you’ve typed the cell range name incorrectly, if you’ve referenced a range that doesn’t exist, or you didn’t add double quotes around a test string. When you’re done, we’ll have some more Excel tutorials ready for you to practice.