formulastreak

#NULL! error: what it means and how to fix it

#NULL!

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. 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. 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. 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”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.