#NULL! error in Excel
A formula asked for the overlap of two ranges that have no cells in common, usually because a space was typed in place of a comma or colon.
What #NULL! means
#NULL! is the least common error and has one of the clearest explanations. Excel has three reference operators: the colon makes a range (A1:A3), the comma unites references (A1:A3,C1:C3), and a space intersects them, returning only the cells that appear in both.
When a space ends up where a comma or colon was meant, the formula asks for the intersection of two ranges that do not overlap. The intersection is empty and Excel returns #NULL!.
The fix is usually one character. Look at the separators between references in the formula bar and replace the space with the one you meant.
A space is easy to type by accident and hard to see in a long formula, especially one pasted from an email or a web page, where a line break can turn into a space. If you pasted a formula and it fails with #NULL!, scan the separators first. Everything else in the formula is probably fine.
Causes of #NULL! and how to fix each
1. A space typed where a comma belongs
In Excel a space between two references is the intersection operator: it returns the cells the two ranges share. Type A1:A2 B1:B2 meaning "these two ranges" and Excel looks for cells in both, finds none, and returns #NULL!.
| A | B | C | |
|---|---|---|---|
| 1 | 1 | 2 | #NULL! |
| 2 | 3 | 4 |
Fix. Replace the space with a comma to add the ranges together, or with a colon to make one continuous range.
| A | B | C | |
|---|---|---|---|
| 1 | 1 | 2 | 10 |
| 2 | 3 | 4 |
SUM now adds 1 and 3 from column A with 2 and 4 from column B.
2. An intersection that finds nothing
The space operator is sometimes used on purpose, to pick one cell where a column range and a row range cross. If the ranges do not actually overlap, the intersection is empty and the result is #NULL!.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 1 | 2 | #NULL! | |||
| 2 | 3 | 4 | 5 | 6 | ||
| 3 | 7 | 8 |
Fix. Check that the two ranges cross. If the column should be B and the row should be 2, the pair B1:B3 A2:D2 shares cell B2 and returns its value. If you never meant an intersection, use a comma or a different function.
3. A colon typed as a space inside one range
The same mistake can happen inside a single range. A1 A3 with a space between the two cells does not mean "A1 through A3". It means the cells the two references share, and a single cell and another single cell share nothing.
| A | B | |
|---|---|---|
| 1 | 1 | #NULL! |
| 2 | 2 | |
| 3 | 3 |
Fix. Type a colon between the corners: =SUM(A1:A3). Clicking and dragging across the cells makes Excel write the colon for you.
Trapping #NULL! in a formula
You rarely need to trap #NULL!. It almost always means a typo, so the right response is to correct the separator, and wrapping it in IFERROR would hide a formula that returns nothing useful. On the rare model that uses intersections deliberately, a fallback with IFERROR is reasonable as long as you know a miss is possible.
#NULL! on Excel job tests
If a prompt asks you to repair a broken workbook and a cell shows #NULL!, the fix is a single character: a comma or colon in place of a space.
A lookup done as a row-and-column intersection (Table1 Jan) is an older technique that few prompts ask for. Knowing that the space is an operator explains the error and is usually all that is needed.
Related guides
#NULL! questions
- What does #NULL! mean in Excel?
- Two ranges were joined with the space intersection operator and have no cells in common. Most of the time a comma or colon was meant.
- Is the space really an operator in Excel?
- Yes. A space between two references returns their intersection. It is rarely used on purpose but explains the error.
- Is #NULL! the same as a blank cell?
- No. A blank cell has no value.
#NULL!is an error value caused by an empty intersection.
Practice working with errors
The free 5-item sampler uses the same kind of formulas in a working grid, and it shows you the error value when a formula goes wrong.
Try a 5-item Excel test sample