How to Fix #NULL! Errors in Excel: Complete Guide
#NULL! is the rarest Excel error — and also the most confusing, because it's caused by a feature most users don't know exists. Once you understand what triggers it, the fix takes about two seconds.
Quick Fix
#NULL! is almost always a missing comma. Replace the space between ranges with a comma:
=SUM(A1:A5 C1:C5) ' ← causes #NULL! (space = intersection operator) =SUM(A1:A5, C1:C5) ' ← correct (comma separates arguments)
Find all #NULL! errors automatically — upload your spreadsheet and ExcelErrorFinder will locate every intersection error across all sheets.
Free Audit →What Is a #NULL! Error?
A #NULL! error appears when a formula uses the space operator (Excel's range intersection operator) on two ranges that don't overlap — so the intersection is empty, or "null." See Microsoft's #NULL! error documentation for the official definition.
Here's the part most users don't know: in Excel, a space character between two ranges is a valid operator — it means "return the value at the intersection of these two ranges." So =SUM(A1:A10 A5) is actually valid Excel syntax that returns the value of A5 (the intersection of A1:A10 and A5). But =SUM(A1:A5 C1:C5) returns #NULL! because those two ranges share no cells.
The confusion is that almost nobody uses the intersection operator intentionally. In nearly every real case, the space is a typo — you meant to type a comma.
What Causes #NULL! in Practice
A Comma Replaced by a Space (The Almost-Universal Cause)
The most common trigger is a missing comma in a multi-argument formula. When you type:
=SUM(A1:A10 B1:B10) ' #NULL! — space instead of comma
=SUM(A1:A10, B1:B10) ' CorrectThis happens most often when editing a formula and accidentally deleting a comma, or when importing a formula from another system that uses a different separator character.
Missing Colon in a Range Reference
A less common cause: accidentally writing a space where a colon should be in a range reference:
=SUM(A1 A10) ' #NULL! — treated as intersection of A1 and A10
=SUM(A1:A10) ' CorrectCopying Formulas from Non-English Locales
In some European Excel locales, the argument separator is a semicolon (;) rather than a comma. A formula copied from one locale to another may have separators stripped or replaced with spaces during the transfer, producing #NULL! in the destination workbook.
How to Find #NULL! Errors
Press Ctrl+F and search for #NULL! to find every instance on the active sheet. Because #NULL! is always a formula problem (not a data problem), you can also use Formulas → Error Checking from the ribbon — Excel will step through every error cell including #NULL! and show the formula.
The ExcelErrorFinder spreadsheet auditor checks all sheets simultaneously and reports every error with its formula and cell address.
How to Fix #NULL! Errors
Step 1: Click the Error Cell
Click the cell showing #NULL! and look at the formula bar. The formula will contain a space between two ranges where there should be a comma.
Step 2: Identify the Space
Look for a space character between range references. It will often look correct at first glance — spaces are hard to spot inside a formula. Look specifically between ) and the next range, or between two range references in a function's arguments.
Step 3: Replace the Space with a Comma
' Before (causes #NULL!):
=SUM(A1:A5 C3:C8)
' After (correct):
=SUM(A1:A5, C3:C8)Fixing Multiple Instances with Find & Replace
If the same formula pattern appears many times, use Ctrl+H (Find & Replace) with "Search in: Formulas" to find the space-separated version and replace it with the comma-separated version. Be careful — only search for the specific pattern, not just a space, or you'll modify other things.
When the Space Is Intentional
The intersection operator does have a legitimate use case: intersecting named ranges. If your spreadsheet has a named range "Revenue" (a column) and "Q2" (a row), writing =Revenue Q2 returns the value at their intersection. If you see #NULL! in a formula that uses named ranges with spaces between them, the issue is that the named ranges don't actually share any cells — check Formulas → Name Manager to verify the ranges are defined correctly and do overlap.
Frequently Asked Questions
Can #NULL! appear without a space operator?
No. #NULL! is exclusively caused by the space (intersection) operator applied to non-overlapping ranges. If you see a different error that looks similar, it is likely #REF! or #VALUE!.
Why doesn't Excel warn me that I typed a space instead of a comma?
Because the space is valid syntax — Excel is doing exactly what you told it to do. It has no way to know you intended a comma. The formula is syntactically correct; it just produces an unexpected result.
Does #NULL! cascade to other cells?
Yes. Any formula referencing a cell that returns #NULL! will typically also return #NULL! or #VALUE!. Fix the source formula and the cascade disappears.
Frequently Asked Questions
What causes a #NULL! error in Excel?
#NULL! is caused by the space operator — an intentional Excel feature that returns the intersection of two ranges. When you write =SUM(A1:A5 C1:C5) with a space between the ranges instead of a comma, Excel looks for cells that exist in both ranges. If the ranges don't overlap, there is no intersection and Excel returns #NULL!.
How do I fix a #NULL! error?
In almost every case, replace the space between ranges with a comma: change =SUM(A1:A5 C1:C5) to =SUM(A1:A5,C1:C5). The space was almost certainly a typo — you meant to separate two arguments with a comma but accidentally typed a space instead.
Is the space operator ever intentionally used in Excel?
Rarely. The intersection operator is useful in named range formulas — for example, if you have named ranges "Sales" (a column) and "Q1" (a row), writing =Sales Q1 returns the cell where they intersect. Outside of intentional named-range lookups, a space between ranges in a formula is almost always a typo.