Correct a #NULL! error

This error occurs when you specify an intersection of two areas (ranges) on a worksheet that do not intersect. The intersection operator is a space character between references.


Microsoft Excel displays #NULL! in one or more cells on a worksheet.


  • You may have used an incorrect range operator.

  • The ranges that you specified in a formula do not intersect. For example, the ranges in the formula =SUM(A1:B3 C1:D5) do not intersect, so the formula returns a #NULL! error.


  • Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error Button Image , click Show Calculation Steps if it appears, and then click the resolution that is appropriate for your data.

    Tip: Review the following resolutions to help determine which option to click.

  • Make sure that you use a correct range operator by doing the following:

    • To refer to a contiguous range of cells, use a colon (:) to separate the reference to the first cell in the range from the reference to the last cell in the range. For example, SUM(A1:A10) refers to the range that includes cells A1 through cell A10.

    • To refer to two areas that don't intersect, use the union operator, the comma (,). For example, if the formula sums two ranges, make sure that a comma separates the two ranges (SUM(A1:A10,C1:C10)).

  • Change the reference so that the ranges intersect. An intersection is a point in a worksheet where data in two or more ranges cross, or "intersect." An example of a formula that includes intersecting ranges is =CELL("address",(A1:A5 A3:C3)). In this example, the CELL function returns the cell address at which the two ranges intersect — A3.

    When you enter or edit a formula, cell references and the borders around the corresponding cells are color-coded.

    Color-coded cell references

    Range Finder color-codes precedent cells

    1. The first cell reference is B3, the color is blue, and the cell range has a blue border with square corners.

    2. The second cell reference is C3, the color is green, and the cell range has a green border with square corners.

    • If there are no squares at each corner of the color-coded border, the reference is to a named range.

    • If there are squares at each corner of the color-coded border, the reference is not to a named range.

      Do one of the following:

    • Change references that are not to a named range by doing the following:

      1. Double-click the cell that contains the formula you want to change. Excel highlights each cell or range of cells with a different color.

      2. Do one of the following:

        • To move a cell or range reference to a different cell or range, drag the color-coded border of the cell or range to the new cell or range.

        • To include more or fewer cells in a reference, drag a corner of the border.

        • In the formula, select the reference, and type a new one.

      3. Press ENTER.

    • Change references that are to a named range by doing the following:

      1. Do one of the following:

        • Select the range of cells that contains formulas in which you want to replace references with names.

        • Select a single cell to change the references to names in all formulas on the worksheet.

      2. On the Formulas tab, in the Defined Names group, click the arrow next to Define Name, and then click Apply Names.

      3. In the Apply Names box, click one or more names.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.