#NULL! error: what it means and how to fix it
Two ranges are joined with a space, and they don't overlap.
In Excel a space between two ranges means “the cells they have in common” (the intersection operator). #NULL! appears when you used a space by mistake, or the ranges don't overlap.
Common causes, ranked
- 1
A space instead of a colon
=SUM(A1 A5)asks for the cells A1 and A5 have in common: none.Fix: Use a colon for a range from one cell to another.
Shows the error
=SUM(A1 A5)Fixed
=SUM(A1:A5) - 2
A space instead of a comma
Two separate ranges need a comma between them.
Fix: Separate ranges with a comma.
Shows the error
=SUM(A1:A10 C1:C10)Fixed
=SUM(A1:A10,C1:C10) - 3
Ranges that really don't intersect
If you meant to use the intersection, the two ranges must share at least one cell.
Fix: Change the references so the ranges overlap.
Should you hide it?
Don't hide #NULL!: it's a typo in the formula. Replace the space with : or ,.
Careful: A hidden typo keeps the total wrong.
Excel vs Google Sheets
Google's list of Sheets error values (ERROR.TYPE) includes #NULL! too. The fix is the same: : for a range, , between ranges.
Sources
Practise this in 5 minutes
Fixing #NULL! is taught in Lesson 5: Ranges and SUM (Unit 2, Ranges and functions), on a live sheet with instant feedback.
Open “Ranges and SUM”Need a quick reference? The formula cheat sheet is free to read and print.