Show All

Hide All

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

Symptom

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

Causes

• You may have used an incorrect range operator.
• Ranges do not intersect.

Resolution

• Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error , 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 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)).
• 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

  

   The first cell reference is B3, the color is blue, and the cell range has a blue border with square corners.
   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.