Null Error in Excel: Causes and Fixes

In Excel, the #NULL! error usually means a formula asks for the intersection of two cell ranges that do not overlap. Check for an accidental space between references, then replace it with the operator you intended: a colon for a range, a comma for separate ranges, or a space only when you specifically want their intersection.

What the #NULL! error means

Excel treats a space between two cell references as a range-intersection operator. It returns the cells that belong to both referenced ranges. If the ranges have no cells in common, Excel cannot produce an intersection and displays #NULL!. The error is about how the formula combines references; it does not ordinarily mean that a cell contains a special null value or that the workbook’s data has been erased.

For example, the ranges A1:A3 and C1:C3 occupy separate columns. They do not overlap, so asking Excel for their intersection has no result. By contrast, ranges A1:A3 and A3:C3 share cell A3, so their intersection is A3. A shared cell can be blank or contain a value; if the reference itself is valid, a blank shared cell is not the same thing as a missing intersection.

The message can be confusing because a space often looks like ordinary typing rather than an instruction. A formula may have been created by editing, copying, or joining references, and one stray space can change what Excel is being asked to calculate.

How Excel’s reference operators work

Excel has three operators for combining cell references. They have different jobs, even though the colon and comma may also appear as punctuation in other parts of a formula:

Colon (:) identifies a continuous range. A1:A3 means A1 through A3.
Comma (,) combines separate references as a union in a formula. A1:A3,C1:C3 refers to both ranges, not just cells shared by them.
Space means intersection: it selects cells that are in both references.

These are reference operators, not interchangeable separators. If you mean to include two separate ranges in a function, a comma is generally the intended separator in standard Microsoft 365 Excel settings. If you insert a space instead, Excel interprets it as a request for their overlap. Regional settings can use a different character, such as a semicolon, to separate function arguments; that setting does not turn a space into a union operator.

Intersection is useful in specialized formulas, but most everyday formulas do not need it. That is why an accidental space is a common explanation for #NULL!, especially in a SUM formula that was meant to include two nonadjacent ranges.

Common formulas that produce or avoid #NULL!

Suppose A1:A3 contains 10, 20, and 30, while C1:C3 contains 1, 2, and 3. The following formula asks for an intersection that does not exist:

With those values in A1:A3 and C1:C3, this formula returns #NULL! because the ranges have no shared cells: =SUM(A1:A3 C1:C3)

If the intention was to add both ranges, use a union inside SUM instead. With the same values, the result is 66: 10 + 20 + 30 + 1 + 2 + 3.

With A1:A3 containing 10, 20, and 30 and C1:C3 containing 1, 2, and 3, this formula sums both nonadjacent ranges and returns 66: =SUM(A1:A3,C1:C3)

An intersection can also be intentional. If A1 is 10, A2 is 20, and A3 is 30, then A1:A3 and A3:C3 share A3. The formula below sums that one shared cell, so the result is 30. The values of B3 and C3 do not affect the result.

With A1=10, A2=20, and A3=30, this formula returns 30 because A3 is the ranges’ only intersection: =SUM(A1:A3 A3:C3)

These examples show why simply deleting every space is not a reliable fix. First decide whether the formula should use one continuous range, separate ranges, or their shared cells. Then use the operator that matches that intention.

How to find the source of the error

Start with the cell displaying #NULL! and inspect its formula in the formula bar. Look closely between each pair of cell or range references. A space between two references may be the intersection operator, even if it was entered accidentally. Also check whether a range begins or ends in the wrong row or column, leaving the two intended ranges with no overlap.

Select the cell showing the error and read the complete formula in the formula bar.
Identify the references immediately before and after any suspicious space.
Compare their actual row and column boundaries. Ask whether they overlap and whether that overlap is what the calculation needs.
Use the Formula Bar to edit the operator, or use Formulas > Evaluate Formula to inspect how Excel evaluates the expression step by step.
Recalculate or press Enter after editing, then check that the displayed result makes sense for the data.

Excel’s error-checking tools may also help you locate a problematic formula. Select the affected cell and look for the error-checking options on the Formulas tab. The exact prompts depend on the formula and workbook, so treat a suggested correction as a clue rather than accepting it without checking the intended calculation.

How to fix it without changing the data

Choose a correction based on what the formula was supposed to do. If it should cover every cell from a starting point to an ending point, use a colon. If it should include two distinct ranges, use the appropriate argument separator. If it should use only cells common to two ranges, keep the intersection operator but adjust the references so that they overlap.

For one continuous block: change a mistaken space to a colon, such as changing the intended reference from A1 A3 to A1:A3.
For separate blocks in a function: separate the range arguments with the list separator Excel expects on that computer. In standard settings, SUM(A1:A3,C1:C3) includes both ranges.
For an intended overlap: revise the range boundaries so at least one cell belongs to both references. Confirm that the shared cells are the ones you actually want calculated.
For a copied formula: inspect whether relative references shifted when the formula was filled or pasted. Correct the references or use absolute references where appropriate, then verify other copied formulas too.

Do not hide the error with IFERROR merely to make the cell look clean until you know why it occurred. IFERROR can replace an error with a blank or another value, but it does not correct an unintended calculation. Hiding #NULL! could make a report appear complete while omitting a sum or other result that should be investigated.

Why a comma, semicolon, or space can be confusing

In a formula such as SUM(A1:A3,C1:C3), the comma separates function arguments and tells SUM to process two ranges. Some Excel installations use a semicolon instead because of regional list-separator settings. If a formula pasted from another computer is rejected, check the separator convention on the current installation rather than replacing punctuation at random.

The space between references has a separate meaning: it requests their intersection. It is not a substitute for the comma or semicolon used to separate arguments. A formula can therefore be syntactically meaningful but still return #NULL! because the ranges do not overlap. That distinction matters: changing a separator may fix the formula’s meaning, while changing the regional argument separator may be necessary merely to make pasted syntax acceptable.

When you are unsure which separator your Excel expects, enter a simple function using the Insert Function dialog or inspect a working formula in the same workbook. Excel for the web and desktop Excel share the same basic reference-operator meanings, though the ribbon layout and available editing tools can differ.

How #NULL! differs from other Excel errors

#NULL! specifically points to a failed intersection of references. Other Excel errors describe different problems, so replacing a formula’s space with a comma will not fix every error that looks similar.

#REF! means a reference is invalid, often because a referenced row, column, or cell was deleted.
#VALUE! commonly indicates that a formula received a value of the wrong type for the operation.
#NAME? means Excel does not recognize text in the formula, such as a misspelled function name or an unquoted text entry.
#DIV/0! means a formula attempts to divide by zero or by a blank cell treated as zero in that calculation.
#N/A commonly indicates that a lookup or other operation did not find an available result.

A blank-looking result is not necessarily an error, either. A formula may return an empty string, or a cell may simply be empty. Conversely, replacing #NULL! with a blank does not prove that the referenced ranges intersect. Diagnose the formula’s intended logic before deciding that an error should be suppressed.

Ways to prevent accidental intersection errors

When entering formulas, use the mouse to select ranges if typing their addresses makes it easy to leave an unintended space. After selecting the first range, type the intended operator explicitly before selecting or entering the next reference. In long formulas, add parentheses where they make the calculation’s structure easier to read, and break complex work into helper cells when that makes the references clearer.

Before filling a formula down or across, test it in one row and compare its result with a small hand calculation. Then inspect how the references move in a copied formula. This is particularly useful when combining ranges from different columns or tables: a shifted boundary can remove the overlap that existed in the original formula.

Finally, keep the original formula or a copy of the workbook when changing an important model. A visible error is often safer than a plausible-looking but incorrect number. The aim is not just to remove #NULL!, but to make sure the revised formula answers the question the worksheet is meant to answer.

Frequently Asked Questions

What does #NULL! mean in Excel?
It usually means Excel tried to find the intersection of two references, but the ranges have no cells in common. Check for a space between references and confirm whether intersection was intended.

How do I fix #NULL! in a SUM formula?
Check the references inside SUM. If you want to add separate ranges, separate them with the argument separator your Excel installation uses, usually a comma in standard settings. If you want one continuous block, use a colon between its endpoints.

Can a blank cell cause #NULL!?
A blank cell alone does not normally cause this error. The key issue is whether the referenced ranges intersect. If they do intersect at a blank cell, a function such as SUM can process that reference without turning the missing value into #NULL!.

Does #NULL! mean my Excel file is corrupted?
Usually not. It most often points to the formula’s reference operators or range boundaries. Inspect the formula first; if many unrelated formulas show errors or the workbook behaves unexpectedly, investigate those symptoms separately.

Why does a formula work on one computer but not another?
Regional settings can change the separator used between function arguments, such as a comma versus a semicolon. That is different from the space used as the range-intersection operator. Check the formula syntax and the computer’s separator settings before changing the calculation.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.