Exceljet

Quick, clean, and to the point

How to fix the #NULL! error

Excel formula: How to fix the #NULL! error
Explanation 

The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references. Technically, the space character is the "range intersect" operator and the the #NULL! error is reporting that the two ranges do not intersect. This is baffling to the average user but in most cases replacing the space with a comma or colon as needed will fix the problem.

Example 1 - space instead of colon

In the screen below, the formula in C9 returns the #NULL error:

=SUM(C3 C7) // returns #NULL!

#NULL! error example - space instead of colon

In this case the input was meant to be the range C3:C7, but the colon did not get typed. Once the colon is added, the error is fixed:

=SUM(C3:C7) // returns 1205

#NULL! error example - space instead of colon FIXED

Example 2 - space instead of comma

In the example below, the formula in C5 returns the #NULL error:

=SUM(C2,F2 I2) // returns #NULL!

#NULL! error example - space instead of comma

Here, a space was typed instead of a comma between F2 and I2.  Once the colon is added, the error is fixed:

=SUM(C2,F2,I2) // returns 1205

#NULL! error example - space instead of comma FIXED

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.