Fixes / Excel · Checked

Excel #NULL! error: what it means and how to fix it

Short answer

#NULL! means your formula has a space between two cell references. Excel reads that space as the intersection operator, and the two ranges don't overlap. The usual cause is a typo. Replace the space with a colon for a continuous range (=SUM(A1:A10)) or a comma for separate cells or ranges (=SUM(A1:A5,C1:C5)).

What this message means

In Excel formulas, a space between two references is an operator. It is called the intersection operator, and it returns only the cells the two ranges have in common. For example, =SUM(B7:D7 C6:C8) returns C7 because that is the one cell in both ranges. If the ranges share no cells, Excel has nothing to return and shows #NULL!. Most of the time nobody meant to use intersection at all. The space was typed where a colon (A1:A10, a range) or a comma (A1,B1, a list) should be. #NULL! can also show up in a cell whose formula is fine, because it points to another cell that already shows #NULL!.

Common causes

CauseHow to tell
A space typed instead of a colon inside a rangeThe formula contains something like =SUM(A2 A10) or =AVERAGE(C3 C7), with two single cells separated by a space where you meant a from-to range.
A space typed instead of a comma between separate cells or rangesThe formula lists several references and one gap has a space instead of a comma, for example =SUM(C2,F2 I2) or =SUM(A2:A3 B2:B3).
A deliberate intersection where the ranges don't overlapThe formula intersects ranges or named ranges on purpose (for example =Jan Sales or =CELL("address",(A1:A5 C1:C3))), but the rows and columns don't cross. This often happens after rows or columns are inserted, deleted or a named range is redefined.
The error is carried in from another cellThe formula in the selected cell has no stray spaces, but it refers to a cell that also shows #NULL!. Trace Error draws an arrow to that source cell.

How to fix it

1. Replace the space with a colon for a continuous range

  1. Click the cell showing #NULL! and look at the formula in the formula bar.
  2. Find two references separated only by a space, such as A2 A10.
  3. Replace the space with a colon so it reads A2:A10.
  4. Press Enter. The cell should now show a result, for example =SUM(A2:A10).

2. Replace the space with a comma for separate cells or ranges

  1. Click the cell and look at the formula in the formula bar.
  2. Find the spot where two separate references are joined by a space, such as =SUM(C2,F2 I2) or =SUM(A2:A3 B2:B3).
  3. Replace that space with a comma: =SUM(C2,F2,I2) or =SUM(A2:A3,B2:B3).
  4. If your Excel uses a semicolon as the list separator (common with many European regional settings, and the other arguments in the formula are already separated by semicolons), type a semicolon instead.
  5. Press Enter.

3. Use the green triangle and Show Calculation Steps

Excel for Windows (Microsoft 365, 2021, 2019, 2016). Evaluate Formula may be missing in Excel for the web and some Mac versions. There, check the formula bar by hand.

  1. Click the cell with #NULL!. If background error checking is on, a small green triangle appears in its top-left corner, and a warning icon appears next to the cell.
  2. Click the warning icon and choose Show Calculation Steps if it is offered. This opens the Evaluate Formula window at the part that fails.
  3. Alternatively, select the cell and go to Formulas > Formula Auditing > Evaluate Formula, then click Evaluate repeatedly until the step that turns into #NULL! is shown.
  4. Fix the reference at that step with a colon or comma, then press Enter.

4. Find where the error starts when the formula looks correct

  1. Select the cell showing #NULL!.
  2. Go to Formulas > Formula Auditing > Error Checking (arrow) > Trace Error. A red arrow points to the cell the error comes from.
  3. Open that source cell and fix its formula with a colon or comma.
  4. When the source cell calculates, every cell that depends on it updates.
  5. To find every error on the sheet, press Ctrl+G (Mac: Control+G), click Special, choose Formulas, tick only Errors, and click OK.

5. Make a deliberate intersection actually overlap

  1. Work out which cells each side of the space refers to. For named ranges, press Ctrl+F3 on Windows (Mac: Formulas > Define Name) to see what each name covers.
  2. Change one of the ranges so the two cross. For example, =CELL("address",(A1:A5 C1:C3)) fails, but =CELL("address",(A1:A5 A3:C3)) returns A3.
  3. If inserting or deleting rows or columns shifted a named range, edit the name so it covers the correct row or column again.
  4. If you don't need intersection, rewrite the formula with INDEX/MATCH or XLOOKUP. These are easier to read and don't break when layouts change.

6. Hide the error only if the blank result is expected

  1. Use this only after checking that #NULL! is expected (for example, an intersection lookup that sometimes has no match).
  2. Wrap the formula in IFERROR, for example =IFERROR(Jan Sales,"") or =IFERROR(Jan Sales,0).
  3. Be aware that IFERROR also hides every other error in that formula. To handle only #NULL!, test for it with =IF(ERROR.TYPE(A1)=1,"",A1). ERROR.TYPE returns 1 for #NULL!, and for a cell with no error it returns #N/A.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

Copy the formula into a blank cell and rebuild it one reference at a time. Add the next piece only after the previous one calculates, so you see exactly which reference brings back #NULL!. If the workbook came from someone else or from a template that intersects named ranges, ask the author what each name should cover, since a shifted name is hard to spot. In a managed workplace, your IT or Microsoft 365 support team can review the file. You can also post the exact formula on the Microsoft Community forum or Microsoft Tech Community Excel forum. Replies usually come quickly when the formula is included.

Questions people ask

What does #NULL! mean in Excel?

It means the formula asked for the overlap of two ranges that don't overlap. Excel treats a space between two references as the intersection operator, so an accidental space between references almost always causes it.

What is the difference between #NULL! and #N/A?

#NULL! is a reference problem: two ranges separated by a space don't intersect. #N/A means a lookup function such as VLOOKUP, XLOOKUP or MATCH couldn't find the value it was looking for.

Why does =SUM(A1 A10) give #NULL! but =SUM(A1:A10) works?

The colon means "every cell from A1 to A10". The space means "only the cells that are in both A1 and A10", and there are none. That empty result is shown as #NULL!.

How do I hide #NULL! errors in Excel?

Wrap the formula in IFERROR, for example =IFERROR(your_formula,""). It's usually better to fix the space typo, because IFERROR also hides real mistakes.

Can #NULL! come from a cell with a correct formula?

Yes. If the formula refers to another cell that shows #NULL!, the error passes through. Use Formulas > Error Checking > Trace Error to jump to the cell where it starts.

Sources

Related errors