#NULL! Error in Excel — Every Cause, Every Fix
The #NULL! error is Excel's rarest formula error, and 95% of the time it's caused by a single mistake: a space where a comma should be. Here's every cause, five real fix scenarios, and a prevention checklist.
What it means: A formula asked Excel to find the intersection of two ranges, but the ranges don't actually intersect — or more commonly, a space was used between two ranges instead of a comma, which Excel interprets as the intersection operator.
What the #NULL! error actually means
Excel has a lesser-known operator called the intersection operator, and it's just a space character. When you write A1:A10 A5:A15, Excel treats the space between the two ranges as "give me the cells that are in both" — in this case A5:A10.
The #NULL! error appears when that intersection is empty. Two ranges that don't share any cells produce #NULL!. And that's exactly what happens accidentally when you meant to type a comma but hit space instead — because unrelated ranges almost never share cells.
#NULL!, look at the formula and find where a space appears between two references. That's almost certainly the bug. The fix is usually replacing that space with a comma or a colon.
The 5 root causes of #NULL!
A space instead of a comma between arguments
This is the overwhelming majority of #NULL! errors in the wild. You type =SUM(A1:A10 B1:B10) meaning to add both ranges. But the space tells Excel to intersect them — which fails because they don't overlap.
Fix: replace the space with a comma → =SUM(A1:A10, B1:B10).
A space instead of a colon inside a range
Typing =SUM(A1 A10) when you meant =SUM(A1:A10). Excel sees two single-cell references separated by the intersection operator. Unless A1 and A10 are the same cell (they aren't), the intersection is empty.
Deliberate intersection with no overlap
You intentionally used the intersection operator — =Sales January where Sales and January are named ranges — but the two named ranges genuinely don't share any cells. Excel returns #NULL! because there's nothing to return.
Ranges on different worksheets
Two ranges on separate sheets can never intersect. =Sheet1!A1:A10 Sheet2!A1:A10 is always #NULL!. This one usually shows up when someone copies a formula between sheets and doesn't realize the intersection stopped being valid.
Named ranges that were resized or moved
An intersection formula that used to work — =Sales Q1 — starts returning #NULL! because someone edited one of the named ranges and it no longer overlaps with the other. Common in old workbooks that have been through multiple hands.
5 real-world fix scenarios
You're totaling two ranges but typed a space instead of a comma:
=SUM(A1:A10 C1:C10)Result: #NULL!. Excel is trying to intersect columns A and C, which share no cells.
Replace the space with a comma to union the two ranges:
=SUM(A1:A10, C1:C10)Now Excel adds every cell in both ranges. This same fix works for AVERAGE, COUNT, MAX, MIN, and any other function that accepts multiple range arguments.
You typed the range separator wrong:
=AVERAGE(B2 B100)Excel treats this as "the intersection of B2 and B100" — which is empty because they're different cells.
Use the colon operator to define a proper range:
=AVERAGE(B2:B100)This is one of the easiest #NULL! errors to miss because at a glance a space and a colon look similar in the formula bar.
You defined a lookup pattern using named ranges:
=Revenue MarchRevenue covers rows 2–50 and March covers column C. They used to intersect at C2:C50, but someone shrunk Revenue to only rows 60–100.
Either restore the named range or switch to an explicit lookup:
=INDEX(Revenue, MATCH("March", Months, 0))INDEX/MATCH is more resilient — it doesn't rely on the two ranges physically overlapping in the sheet.
You copied a formula from Sheet1 to Sheet2 and left references pointing across sheets:
=Sheet1!A1:A10 Sheet2!A5:A15Ranges on different sheets can't share any cells, so the intersection is empty.
If you meant to combine both, use a comma. If you meant a cross-sheet lookup, use a proper lookup function:
=SUM(Sheet1!A1:A10, Sheet2!A5:A15)Or, for a cross-sheet lookup: =XLOOKUP(A2, Sheet1!A:A, Sheet1!B:B).
You're computing a weighted total and hit space instead of comma:
=SUMPRODUCT(A2:A10 B2:B10)Result: #NULL!. But because SUMPRODUCT often accepts both patterns in different Excel versions, this bug is especially confusing.
Use a comma between the arrays you want to multiply and sum:
=SUMPRODUCT(A2:A10, B2:B10)For clarity, always separate SUMPRODUCT arrays with commas even when they happen to overlap — it makes intent explicit.
Prevention checklist
- Read your formula out loud before pressing Enter — you'll catch missing commas faster than by re-reading silently.
- Use the Formula Bar's expand button to view multi-argument formulas on separate lines. Missing commas jump out visually.
- Prefer typed range separators: colons for ranges, commas for argument lists. Avoid ambiguous spacing.
- Turn on formula auditing arrows (Formulas → Trace Precedents) — a
#NULL!cell will show precedent arrows leading to two non-overlapping ranges. - Avoid the intersection operator unless you know why you're using it. In 2026, dynamic arrays and structured references make intersection nearly obsolete.
- Review named ranges when someone else's workbook throws #NULL! — the ranges may have been resized after the formulas were written.
Version compatibility
The intersection operator (space between references) has existed in Excel since the earliest versions and works identically across all modern Excel platforms. The #NULL! error behavior is unchanged.
Note: Google Sheets doesn't support the intersection operator at all, so #NULL! can't be produced there. Formulas using a space between ranges will typically return #N/A or a different error.
Download the practice workbook
Every scenario above laid out as broken/fixed pairs, plus a diagnostic sheet showing how to spot intersection-operator bugs at a glance.
Related errors
Frequently asked questions
Is the space really an operator in Excel?
Yes. A single space character between two range references is Excel's intersection operator. It returns the cells that appear in both ranges. This has been true since the earliest versions of Excel, but almost no one uses it deliberately — which is why #NULL! is almost always a typo.
How do I use the intersection operator on purpose?
Define two named ranges that overlap. For example, name column B "January" and rows 2–13 "Sales". Then =Sales January returns the cells at the intersection — B2:B13. It's a compact way to look up a row/column crossing without an INDEX formula, but modern Excel makes this rarely worthwhile.
Why does #NULL! sometimes show up in old templates I download?
Older workbooks — especially ones ported from Lotus 1-2-3 or Excel 97 — sometimes used intersection notation deliberately. When those workbooks are modified (rows added, ranges shrunk), the intersection stops working and #NULL! appears. Fix by rewriting with INDEX/MATCH or XLOOKUP.
Can I get #NULL! from a lookup or logical function?
Not directly. #NULL! is specific to the intersection operator. If you see it in a formula containing VLOOKUP or IF, look at the arguments — someone has passed two ranges with a space between them instead of a comma.
Does IFERROR handle #NULL! the same way as other errors?
Yes. =IFERROR(formula, "N/A") catches #NULL! along with all other error types. But like all error suppression, this hides the underlying bug rather than fixing it. Prefer to fix the space-vs-comma issue at the source.
Why does my #NULL! error appear only in certain cells of a copied formula?
Usually because the copy landed the two referenced ranges in positions where one shifted onto a location that doesn't intersect anymore. If you're using named ranges with intersection notation, drag-copying the formula preserves the operator but the relative geometry changes.
Is #NULL! the same as an empty cell?
No — this is a common confusion. An empty cell shows nothing and contains no error. #NULL! is a specific error value indicating a failed intersection. If you want to check for an empty cell, use ISBLANK; if you want to check for the error, use ISERROR or IFERROR.
Catch formula typos before they hit — with the Sheets & Cells AI Add-in
Our Excel Add-in scans every formula for suspicious operators — including the space-instead-of-comma bug that causes 95% of #NULL! errors — and flags them before you press Enter. Right inside Excel.