This error occurs when you specify an intersection of two areas that do not intersect. The intersection operator is a space between references.

  1. Optionally, click the cell that displays the error, click the button that appears button image, and then click Show Calculation Steps if it appears.
  2. Review the following possible causes and solutions.

    Using an incorrect range operator

    • 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 from cell A1 to cell A10 inclusive.

    • 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)).

    Ranges do not intersect

    Change the reference so that it intersects.

    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

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

    callout 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, then the reference is to a named range.
    • If there are squares at each corner of the color-coded border, then the reference is not to a named range.

    Do one of the following:

    Change references that are not to a named range

    1. Double-click the cell that contains the formula you want to change. Microsoft Office 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

    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 Defined Name, and then click Apply Names.
    3. In the Apply Names box, click one or more names.