I see #NULL! instead of content in some cells

Cause: The formula contains an incorrect range operator.

Solution:    To refer to a range of adjacent 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 through cell A10.

Solution:    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 as in the following example, =SUM(A1:A10,C1:C10).

Cause: The formula contains references to named ranges that do not intersect.

Solution:    Change the range references so that they intersect.

1. On the Formulas tab, under Function, click Formula Builder.

   

2. In the Arguments section of the Formula Builder, click any argument. Excel highlights each cell or range of cells that are referenced by the formula with a different color.

   Color codes show that the formula in cell D9 refers to cells B9 and C9

   

3. Do one of the following:

To	Do this
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.
Include more or fewer cells in a reference	Use the drag handle in the lower-right corner of the border to adjust the range.
Enter a new reference	In the Formula Builder, select the argument that contains the reference, and then type a new one.

1. Press RETURN .

Cause: The formula contains references to unnamed ranges that do not intersect.

Solution:    Change the range references so that they intersect.

1. On the Formulas tab, under Function, click Formula Builder.

   

2. In the Arguments section of the Formula Builder, click any argument. Excel highlights each cell or range of cells that are referenced by the formula with a different color.

   Color codes show that the formula in cell D9 refers to cells B9 and C9

   

3. Do one of the following:

To	Do this
Replace references with names	Select the range of cells that contains formulas in which you want to make replacements.
Include more or fewer cells in a reference	Use the drag handle in the lower-right corner of the border to adjust the range.
Change the references to names in all formulas on the sheet	Select any cell on the sheet.

1. On the Insert menu, point to Name, and then click Apply.

2. In the Apply names box, click one or more names.

See also

Hide error values and error indicators