Excel #NULL! Error Fix: Find and Correct Range References

To fix Excel’s #NULL! error, check for a space between cell ranges: Excel treats that space as an intersection operator, and it returns the error when the ranges do not overlap. Replace the space with a comma to use both ranges, a colon to include the cells between endpoints, or correct the references so they intersect—whichever matches your intended calculation.

What the #NULL! error means

#NULL! is Excel’s way of reporting that a formula asked for the intersection of two or more cell ranges but those ranges share no cells. In Excel formulas, a space can act as an intersection operator. That meaning is easy to miss because spaces usually separate words in ordinary text, but inside a formula they can perform a calculation.

For example, a formula that intersects A1:A3 with C1:C3 has no common cells: the first range is in column A and the second is in column C. Excel cannot calculate a sum over an empty intersection, so it reports #NULL!. The error is not a message that a cell is blank, and it does not mean the value zero. It points to how the formula’s references are written.

The term “null” can also be confusing if you have encountered null values in databases or programming. In this Excel error, the practical issue is specifically that the referenced ranges have no cells in common. The formula may be syntactically understandable to Excel while still asking for an impossible intersection.

Fix the formula according to what you intended

First decide what the formula should include. If you meant to add values from two separate ranges, separate them with a comma inside the function. If you meant to include one continuous block of cells, use a colon between the endpoints. Keep a space only when you deliberately want the cells shared by both ranges.

Add two separate ranges: use a comma between the range arguments.
Use one continuous range: use a colon to mark its first and last cells.
Use only shared cells: keep the intersection operator, but make sure the ranges overlap.

With values in A1:A3 and C1:C3, and a formula intended to add both separate groups, use =SUM(A1:A3,C1:C3). This sums the values in both ranges; it does not include the blank column B between them. If the values in A1:A3 are 2, 3, and 4, and C1:C3 contain 5, 6, and 7, the result is 27.

With values arranged across A1:C3 and an intended calculation covering the entire rectangle, use =SUM(A1:C3). This includes every cell from A1 through C3, including the cells in column B. A colon describes a continuous range, so use it only if including those intervening cells is correct.

With values in B2:C3 that total 14, and a formula intended to sum only the overlap between two rectangles, use =SUM(A1:C3 B2:D4). The ranges share B2:C3, so the space intentionally selects those four cells. The result is 14 for values 2, 3, 4, and 5 in that overlap.

Why a space can cause the error

Excel supports three reference operators: the colon creates a range, the comma combines references, and a space finds their intersection. The space operator is comparatively uncommon, which is why a stray space can be mistaken for harmless formatting. In a formula, however, it can change the meaning entirely.

Suppose a formula was meant to add two groups of cells, but a comma was replaced with a space during editing. Excel then tries to find cells that belong to both groups rather than adding each group. If the groups are on separate parts of the worksheet, there is no overlap and the result is #NULL!. If they do overlap, Excel may return a number without an error—but that number could still be wrong because it sums only the shared cells.

A space is not always the cause. A mistaken row or column number, an incorrect range endpoint, or a reference to the wrong worksheet can also leave the formula with no overlap. Check the references themselves, not just the character between them. A formula copied from another row may still point to the previous row, for example, and no longer intersect the intended range.

How to locate the problem in a workbook

If you know which cell shows #NULL!, select it and inspect the formula bar. Look for spaces between references, then compare each range’s row and column boundaries. The letters identify columns and the numbers identify rows: A1:A5 and A6:A10 are both in column A, but they have no cells in common.

On Excel for Microsoft 365 on Windows, the Evaluate Formula tool can help you see how Excel processes a formula. Select the cell, then choose Formulas > Evaluate Formula. Use Evaluate to step through the calculation and identify the point where the reference produces an error. This tool is useful for long formulas, nested functions, and references that are difficult to follow by eye.

For a broader check, use Find to search the workbook for #NULL!. Search for the error text rather than a particular formula fragment, since formulas can be structured in many ways. Check each result individually: a workbook may contain multiple errors with different causes. You can also use Formulas > Trace Precedents after selecting an error cell to see which cells feed into the formula. The feature helps map dependencies, but you still need to decide whether those inputs match the intended calculation.

Excel for the web lets you select the cell and inspect its formula in the formula bar; if a desktop auditing tool is unavailable in your browser, opening the workbook in the desktop app gives you access to its formula-auditing commands. On Mac, ribbon labels and placement can differ slightly from Windows, but the same principle applies: inspect the formula and verify whether its referenced ranges overlap.

Check that the correction preserves the intended result

Removing the error is not enough if the revised formula now calculates the wrong thing. Before changing an operator, write down which cells the answer should include. A comma and a colon are not interchangeable: a comma can pass separate arguments to a function, while a colon defines all cells between two endpoints.

For example, suppose monthly figures are in B2:B13 and a separate adjustment is in D2. If the desired total is the monthly figures plus that one adjustment, adding a range that stretches from B2 to D13 would pull in unrelated cells in columns C and D. A continuous range may eliminate an error yet quietly alter the calculation. Choose references based on the worksheet layout, and compare the corrected result against a small manual check or a known total.

When the formula is complicated, simplify it temporarily or evaluate its parts in separate cells. Check the exact start and end of each range, and confirm that the worksheet names are correct. If the formula is copied, inspect the references in both the original and copied cells; relative references can shift as a formula is filled down or across. Restore or remove any temporary checks once the logic is clear.

Related errors and misconceptions

#NULL! is different from #VALUE!, which generally signals an unsuitable value or argument for an operation, and from #REF!, which indicates an invalid cell reference. It is also different from #N/A, often used when a lookup cannot find a match. Each error points to a different category of problem, so replacing the error with zero or wrapping the formula in error-handling logic can hide the real issue rather than fix it.

Another common misconception is that any space in a formula is wrong. Spaces can be part of the intersection operator, and in some formulas an intersection is exactly what is wanted. The key question is whether the references overlap and whether using only that overlap matches the calculation. Do not remove spaces mechanically from formulas that intentionally select shared cells.

Likewise, a formula that returns a number is not necessarily correct. Two ranges can overlap even when you intended to add them separately. In that case Excel will calculate the intersection without displaying an error. Verify the cells included in the result, especially when a formula uses a space between references.

Preventing future #NULL! errors

When building a formula, select ranges directly with the mouse or keyboard and review the formula bar before pressing Enter. Be especially careful when editing a formula by hand, pasting one from another workbook, or adding a second range to a function. A tiny change from comma to space can change the operation without making the formula look dramatically different.

Use clear, separate ranges for separate groups of data.
Check whether each range overlaps before using a space between references.
After copying formulas, confirm that relative references shifted as intended.
Compare important totals with a simple independent calculation or a known result.
When sharing a workbook, explain unusual formulas that intentionally use intersections.

For recurring workbooks, consistent layout also reduces mistakes. Keep inputs and adjustments in clearly identified columns, avoid inserting unrelated data inside a range that is meant to be continuous, and label totals so reviewers can see what they should include. These habits do not prevent every formula error, but they make an accidental reference change easier to spot.

Frequently Asked Questions

What does #NULL! mean in Excel?
It means Excel tried to find the intersection of referenced ranges and found no cells they share. Inspect the formula’s ranges and the operator between them.

How do I fix #NULL! in a SUM formula?
Decide whether you want separate ranges, one continuous range, or only overlapping cells. Use commas for separate SUM arguments, a colon for a continuous range, or a space only for an intentional intersection.

Can a space in an Excel formula cause #NULL!?
Yes. A space between references can act as Excel’s intersection operator. If those references do not overlap, the formula can return #NULL!.

Does #NULL! mean a cell is empty?
No. It does not report an empty cell. It reports that the formula’s intersecting ranges have no cells in common. Blank cells may be present in those ranges, but they are not what this error means.

Why does my formula return a number instead of #NULL! when I use a space?
The ranges may overlap, so Excel can calculate their intersection. Check whether the result uses only the shared cells; a valid number can still be the wrong answer if you meant to include both ranges.

Will IFERROR fix the #NULL! error?
It can replace an error result with another value, but it does not correct the references or the calculation. Fix the formula’s range logic first; use error handling only when a fallback result is genuinely appropriate.

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.