#NULL! Error in Excel: What It Means and How to Fix It

The #NULL! error in Excel means a formula asked for the cells where two ranges overlap, and the ranges don't overlap. In practice, it almost always means you typed a space where a comma or a colon should be, as in =SUM(B2:B5 D2:D5). The fix is one character. This guide explains why the error happens, how to fix it, and how to find every #NULL! in a workbook.

What does #NULL! mean?#

#NULL! means "these ranges have nothing in common". Excel has three reference operators, symbols that join cell references together:

The space is the surprising one. Because a space between two references is a real operator, Excel doesn't ignore it. It looks for cells that belong to both ranges, and if there are none, the result is #NULL!.

All the examples use this cost table in A1:D5:

Cause 1: a space instead of a comma#

The most common cause is a missing comma between two ranges. Say you want the total of January and March:

=SUM(B2:B5 D2:D5)

Column B and column D share no cells, so the intersection is empty and the formula returns #NULL!. Replace the space with a comma:

=SUM(B2:B5,D2:D5)

The result is 3,415: 1,775 for January plus 1,640 for March. The comma tells SUM to add both ranges. The SUM function guide shows the other ways to give SUM several ranges, and the complete guide to SUM covers AutoSum and totals across columns.

Cause 2: a space instead of a colon#

The second cause is a missing colon inside a range. Typing a space instead:

=SUM(B2 B5)

asks for the overlap of cell B2 and cell B5. Two different single cells never overlap, so you get #NULL!. Put the colon back:

=SUM(B2:B5)

This returns 1,775, the January total. The same slip can happen in any function that takes a range, such as AVERAGE, COUNT or MAX.

Cause 3: a missing maths operator#

The third cause is a space where a plus, minus or multiply sign should be. To add January and February power costs, you might type:

=B3 C3

Excel reads this as "the overlap of B3 and C3", not "B3 plus C3", and returns #NULL!. Add the operator you meant:

=B3+C3

This returns 345 (180 + 165). The slip is easy to make when you edit a formula and delete one character too many, so check the spot you last changed first.

Spaces that are safe#

Not every space in a formula causes #NULL!. Excel only treats a space as the intersection operator when it sits directly between two references. These all work normally:

• =SUM(B2:B5, D2:D5): the space comes after a comma.
• =SUM( B2:B5 ): the spaces sit next to brackets.
• =B2 + D2: the spaces sit next to an operator.

So you can add spaces to make long formulas easier to read. Just never let a space be the only thing between two references.

How the intersection operator works on purpose#

The space operator does have a real use: it returns the cell where a row range and a column range cross. On the cost table:

=B3:D3 C2:C5

Row 3 (Power) and column C (Feb) cross at C3, so the result is 165. You can also wrap it in a function: =SUM(B2:D5 C2:C5) adds only the cells both ranges share, which is all of column C, and returns 1,485.

This works best with named ranges. If you name B3:D3 "Power" and C2:C5 "Feb", then =Power Feb reads almost like English and returns 165. The guide to named ranges shows how to create them. Most people never need this, which is why a #NULL! error is nearly always a typo rather than an intended intersection.

How to find every #NULL! error in a workbook#

To find all the error cells at once, use Go To Special:

1. Press F5 or Ctrl+G (Mac: Ctrl+G or Fn+F5), then click Special.
2. Choose Formulas and untick everything except Errors.
3. Click OK. Excel selects every cell that shows an error.

Press Tab to move from one selected cell to the next and fix each one. If a total near the bottom shows #NULL!, check the formula itself and every cell it uses, because errors pass up into the formulas that depend on them.

You can also count errors with =SUMPRODUCT(--ISERROR(B2:F20)). If you want to know which error a cell holds, =ERROR.TYPE(F2) returns 1 for #NULL!.

Should you hide #NULL! with IFERROR?#

No. IFERROR would turn #NULL! into a blank or a 0, but the formula would still be wrong: you would just stop seeing it. A #NULL! error has an exact cause and a one-character fix, so correct the formula instead.

#NULL! is one of several Excel errors, and each points to a different problem. A deleted cell gives #REF!, text in a sum gives #VALUE!, and a typo in a function name gives #NAME?. The guides to the #REF! error, the #VALUE! error and the #NAME? error cover each one, and the list of all Excel error types puts them side by side.

Practise it#

Try the exercise on this page: it asks you to fix the exact =SUM(B2:B5 D2:D5) formula from this guide. Then practise SUM in a live spreadsheet, where each formula you type is checked instantly. The Debugging SUM lesson goes further, with missed rows, numbers stored as text and totals that count themselves.