Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Blog How to Identify and Fix the #NULL Error in Excel

How to Identify and Fix the #NULL Error in Excel

If Excel isn’t sure what you want to do or can’t properly complete a calculation, you’re likely to get an error message. There are a variety of error messages that you might see depending on the situation, but a common one is the #NULL error. 

Here are some of the common reasons why the #NULL error might appear in Excel, how you can locate these errors, and the best options to fix the issues. 

While this is generally a simple error to correct, you might need some additional help. Excelchat can always provide one-on-one assistance through its live chat feature. 

Two gears.

Learn how to identify and fix the #NULL error in Excel.

Common Causes of the #NULL Error in Excel

#NULL errors in Excel are not very common. They are generally the result of a typo in a formula or an issue with a range in a formula. These are often simple enough errors to correct if you know where to look for the issues. 

#NULL Error Space Instead of Colon

One of the most common reasons that you’ll get a #NULL error in Excel is because there is a space in a formula where there should be a colon. See the image below in cell C12, the SUM function is missing the colon between C5 and C10 and instead has a space.

Sum example in excel.

When the formula is fixed, the #NULL error disappears. The correct formula should be =SUM(C5:C10).

Sum example in excel.

#NULL Error Space Instead of Comma

Another way that you can get a #NULL error in Excel is if you have a formula that deals with multiple ranges and you forget to put your comma in the designated spot that separates those ranges. If there is a space there instead, Excel won’t know what to do with that and will give you an error. 

For example, the formula in cell C12 is meant to add two ranges together, but the comma is missing between to the two ranges, so there is an error. 

Sum example in excel.

When the comma is placed between the two ranges, the #NULL error disappears. The correct formula should be =SUM(C5:C10,F5:F6).

Sum example in excel.

#NULL Error Incorrect Range Operator

The last way that you might get a #NULL error is when you’ve put a space character between ranges that don’t intersect. 

For example, the CELL function returns the cell address at which two ranges intersect. In this example, the ranges given don’t intersect, so the formula is going to return a #NULL error. The space in the formula is correct and intentional, but the problem is that the two ranges don’t intersect. 

Cell example in excel.

When the formula is corrected, and the two ranges intersect, the #NULL error is fixed and disappears. In this example, the correct formula is =CELL(“address”,(C5:C10 B6:F6)). 

Cell example in excel.

Finding the #NULL Error in Excel

Assuming you have a large spreadsheet, it might be difficult to know how many errors you have and where they’re located. The easiest way to find them all is by using the “Find” function, located on your Home tab in the Editing section. 

Just go to “Find & Select,” and choose “Find.” Type in #NULL and choose “Find All” or “Find Next.”

Find and replace box in excel.

Correcting the #NULL Error in Excel

Fortunately, Excel gives you several tools to fix errors when they occur. If you have error checking turned on, this can be invaluable. A tiny yellow icon will appear to the left of the cell with your error. When you click on the drop-down box (hover over it first), you’ll get several options. Among these are to “Show Calculations Steps,” “Ignore Error,” and “Edit in Formula Bar.”

Sum example in excel.

When working with some errors, it’s possible to “replace” the error with something else to correct it. This is possible with the #NULL error, but it shouldn’t be done as a “Replace All” operation since you don’t know which problem has led to your error. This requires reviewing each instance of the error individually. 

If you’re going to use the “Find and Replace” operation, the better option is to use “Find Next” to determine whether your formula is missing a colon, comma, or something else having to do with a range. 

Find and replace box in excel.

Were you able to solve your #NULL error in Excel? Whether you got it fixed or not, there is always additional help available from Excelchat. 

Maybe you have additional questions related to Excel, want some personalized training, or need help troubleshooting another issue. Through our live chat service, you can get this type of support 24/7 from one of our knowledgeable and friendly Excel experts. 

Try it out now. Your first session is always free. 

 

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc