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.
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.
When the formula is fixed, the #NULL error disappears. The correct formula should be =SUM(C5:C10).
#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.
When the comma is placed between the two ranges, the #NULL error disappears. The correct formula should be =SUM(C5:C10,F5:F6).
#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.
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)).
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.”
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.”
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.
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.