#NULL!
#NULL! Error in Excel — Causes & Fixes
Excel returns #NULL! when two range references in a formula are separated by a space instead of a comma or colon, resulting in an empty intersection.
Why this happens
Common causes of #NULL!
Space used instead of a comma
=SUM(A1:A10 B1:B10) uses a space between the two ranges. Excel interprets the space as an intersection operator and returns #NULL! if the ranges do not overlap.
Space used instead of a colon
=SUM(A1 A10) uses a space where a colon is needed. The correct syntax is =SUM(A1:A10).
Non-overlapping intersection
The space operator returns the cell at the intersection of two ranges. =SUM(A1:A5 C1:C5) returns #NULL! because rows A and C do not intersect.
How to fix it
Step-by-step fixes for #NULL!
Replace the space with a comma
To sum multiple separate ranges, use a comma: =SUM(A1:A10,B1:B10). The comma is the union operator in Excel.
Replace the space with a colon
To reference a continuous range, use a colon: =SUM(A1:A10). Remove any accidental spaces inside the range reference.
Frequently asked questions
When is the space operator actually useful?
The space (intersection) operator is useful when you have named ranges that overlap and you want the value at their intersection. For example, if 'Sales' names column B and 'Q1' names row 3, then =Sales Q1 returns the value at B3.
How do I quickly find #NULL! errors in a large workbook?
Press Ctrl+F, search for #NULL!, and click Find All. Alternatively, use Formulas > Error Checking to step through each error.
Related function references
These functions appear most often in formulas that produce #NULL!.
Other Excel errors