← Back to Error Directory

How to Fix #NULL! Error in Excel & Google Sheets

The rarest and most misunderstood spreadsheet error — here's exactly what triggers it and how the fix is almost always a single missing character.

Table of Contents

1. What #NULL! Actually Means

Of all the spreadsheet errors, #NULL! is the one most frequently misdiagnosed — its name implies a missing or empty value, but that is not what it means at all. #NULL! is a pure syntax error: it fires when a formula uses the intersection operator (a single space) between two cell ranges that do not physically overlap on the worksheet.

Unlike #VALUE! or #NUM!, which relate to the calculation itself, #NULL! occurs at the parsing stage — Excel understands exactly what you're asking for (the overlapping cells between two ranges), it just can't find any, because there aren't any.

2. The Intersection Operator — Why a Space Breaks Your Formula

Excel has three range operators, and most users only know about two of them:

OperatorSymbolMeaningExample
Range:All cells between two referencesA1:A10
Union,Combines multiple separate rangesA1:A10,C1:C10
Intersection (space)Only cells common to both rangesA1:C10 B5:D15

That third operator is the trap. If you type a space where you meant a comma — usually while listing multiple ranges inside SUM(), AVERAGE(), or similar — Excel doesn't throw a "did you mean a comma?" warning. It silently interprets your space as an intersection request, tries to find overlapping cells, and if none exist, returns #NULL!.

3. Interactive Demo: Space vs Comma

This simulator shows exactly how the same two ranges behave differently depending on which operator separates them.

Try Range A = "B1:D10" and Range B = "C1:C5" with the space operator — since column C overlaps both, it won't error. Now try A1:A10 and C1:C10 — no shared column, so it throws #NULL!.

4. The Three Root Causes of #NULL!

Cause 1: An accidental space between two ranges in a function argument

=SUM(A1:A10 C1:C10)     → #NULL! (missing comma, read as intersection)
=SUM(A1:A10,C1:C10)     → Correct (union — sums both ranges)

This is by far the most common cause — a typo where a comma should have been typed, most often when quickly copying a formula and expanding it to include another column.

Cause 2: Genuinely requesting an intersection that doesn't exist

=B2:B10 D2:D10     → #NULL! (columns B and D never intersect)
=B2:D10 C1:C5      → Returns C2:C5 (the actual overlapping cells)

Sometimes the intersection operator is used intentionally — for instance, to pull a specific cell from a table using row and column headers — but the two ranges specified don't actually cross paths.

Cause 3: A malformed named range reference

If a named range is deleted or renamed but a formula still references the old name alongside another range with a space between them, Excel may interpret it as a failed intersection attempt rather than a simple broken reference (which would normally show #REF!).

5. Step-by-Step Fixes

  1. Locate the space. Click into the formula bar and scan character-by-character for a single space between two range references inside a function's argument list.
  2. Determine intent. Ask whether you meant to combine two ranges (use a comma) or genuinely find their overlap (keep the space, but verify the ranges actually cross).
  3. Replace or correct. Change the space to a comma for union behavior, or adjust one of the ranges so a real intersection exists.
  4. Verify with a simpler test. Temporarily replace the full formula with just =A1:A10 C1:C10 in a blank cell to confirm which specific range pair is causing the failure before reintroducing the full formula complexity.

6. #NULL! With Named Ranges

When using named ranges, the intersection trap is easier to miss because the space between two names doesn't visually stand out as a range operator the way it does between cell references:

=SalesQ1 SalesQ2     → #NULL! if the named ranges point to non-overlapping cell blocks
=SalesQ1+SalesQ2     → Correct, if you meant to add their totals together
=SUM(SalesQ1,SalesQ2) → Correct, if you meant to sum both ranges combined

If you're intentionally using named ranges for an intersection lookup (a legitimate and fast alternative to INDEX/MATCH in some models), name your ranges descriptively enough that the intersection is obvious at a glance — e.g., RegionNorth and ProductWidgets intersecting at a specific cell in a matrix layout.

7. Google Sheets Behavior

Google Sheets does not support the intersection operator (space) at all in the same way Excel does. If you paste an Excel formula containing an accidental space-as-intersection into Google Sheets, Sheets will typically throw a generic #ERROR! ("Formula parse error") rather than the specific #NULL! label, because Sheets doesn't recognize the space as a valid operator in the first place. This is one of the few genuine syntax incompatibilities between the two platforms.

8. Preventing #NULL! Going Forward

9. Frequently Asked Questions

What causes the #NULL! error in Excel?

The #NULL! error happens when a formula uses the space character between two ranges that don't actually intersect. Excel treats a space between range references as the intersection operator, and if there's no overlapping cell between them, it returns #NULL! instead of a value.

Is #NULL! caused by a missing value in a cell?

No — despite the name, #NULL! has nothing to do with empty cells or missing data. It is purely a syntax error caused by an accidental space acting as the range intersection operator between two ranges that share no common cells.

How do I fix a #NULL! error caused by a missing comma?

Check every function argument list for a missing comma between ranges. A formula like =SUM(A1:A10 C1:C10) is missing a comma and will throw #NULL!; correcting it to =SUM(A1:A10,C1:C10) resolves the error immediately.

Does Google Sheets have a #NULL! error?

Google Sheets does not implement the space intersection operator the same way Excel does, so the same mistake usually surfaces as a generic formula parse error rather than a specific #NULL! label.

Related Guides

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.