[Excel] Error: #NULL! — How to Fix It

Summary

The #NULL! error means Excel couldn’t find a valid intersection between two ranges in your formula. It’s triggered by using a space between range references — which Excel interprets as an intersection operator. When those ranges don’t overlap, the result is #NULL!. The fix is to replace the space with a comma (for union) or a colon (for range extension), or ensure the intersection actually exists. Applies to Excel 2007+, 2016, and Microsoft 365.

Context

In Excel syntax, a single space between range references means “take the intersection of these ranges.” For example, =SUM(A1:A5 B1:B5) asks Excel to sum only the cells common to both ranges. If those ranges don’t overlap, Excel can’t resolve a valid cell reference and returns #NULL!. This often happens when users accidentally insert a space instead of a comma or colon, or when formulas are copied from older versions or regional Excel setups that use different argument separators.

Probable Cause

  • Space used instead of a comma or colon. A1:A5 C1:C5 is invalid because there’s no intersection between the two ranges.
  • Non-overlapping ranges with intersection operator. The ranges don’t share any common cells.
  • Typo in named ranges or sheet references. A missing or wrong sheet name can lead to invalid intersections.
  • Regional format mismatch or legacy syntax. Copying formulas between locales or from web sources may introduce incorrect separators.

Quick Fix

  1. Inspect the formula for spaces between ranges. Excel interprets this as intersection. If you meant a union or range, replace appropriately.
=SUM(A1:A5, C1:C5)   # Correct union (comma)
=SUM(A1:C5)           # Correct continuous range (colon)
  1. If you actually intended intersection, make sure the ranges overlap.
=SUM(A1:C5 B2:B10)   # Returns intersection (valid if overlap exists)
  1. Check for missing sheet or named range references.
=Sheet1!A1:A5 Sheet2!B1:B5  # ❌ invalid intersection across sheets
  1. Use the Formula Evaluator. Step through (Formulas → Evaluate Formula) to see where #NULL! arises.
  2. Rebuild complex formulas. Start from smaller valid pieces and add back ranges carefully to avoid syntax mistakes.

Full Example

Scenario 1: Accidental space in a SUM formula.

=SUM(A1:A5 C1:C5)
→ #NULL!

Fix: Replace the space with a comma to combine both ranges.

=SUM(A1:A5, C1:C5)

Scenario 2: Intended intersection but no overlapping cells.

=AVERAGE(A1:A5 C10:C15)
→ #NULL!

Fix: Adjust ranges to overlap if an intersection was intended.

=AVERAGE(A1:A15 C1:C15)

Scenario 3: Named range reference typo.

=SUM(SalesRegion East)
→ #NULL!

Fix: Rename or correct the named range reference (e.g., =SUM(SalesRegion_East)).

Pitfalls & Debug

  • Symptom → #NULL! after copy-pasting formula. Fix → Replace spaces with commas or semicolons depending on your regional separator.
  • Symptom → Array formula fails with intersection. Fix → Use explicit cell references or structured table references.
  • Symptom → Formula references multiple sheets. Fix → Intersections can’t cross sheets; reference valid single-sheet ranges.
  • Symptom → Copied formulas from websites show unexpected errors. Fix → Re-enter manually using Insert Function to rebuild syntax.
  • Symptom → Named ranges return #NULL!. Fix → Validate names in Name Manager (Ctrl + F3).

Validation & Next Steps

After fixing syntax, validate the formula behavior:

Formulas → Error Checking
Formulas → Evaluate Formula

Ensure no unexpected intersections remain. Prefer structured tables or defined names to minimize syntax errors. Avoid manual spaces in formulas unless intentionally performing an intersection between overlapping ranges.

Sources

https://support.microsoft.com/en-us/office/how-to-correct-a-null-error

https://support.microsoft.com/en-us/office/formula-reference-operators

https://support.microsoft.com/en-us/office/use-named-ranges-in-formulas

Labels

Tool/Excel, OS/Cross-platform, Topic/Formula Syntax & Range Operations