This error is shown when you use an incorrect range operator in a formula, or when you use an intersection operator (space character) between range references to specify an intersection of two ranges that don’t intersect. An intersection is a point in a worksheet where data in two or more ranges cross.
If you’ve used an incorrect range operator, make sure you use:
-
A colon (:) to separate the first cell from the last cell when you refer to a continuous range of cells in a formula. For example, SUM(A1:A10) refers to the range that includes cells A1 through cell A10.
-
A comma (,) as the union operator when you refer to two areas that don't intersect. For example, if the formula sums two ranges, make sure that a comma separates the two ranges (SUM(A1:A10,C1:C10)).
If you get this error because you’ve used a space character between ranges that don’t intersect, change the references so that ranges do intersect.
For example, in the formula =CELL(“address”,(A1:A5 C1:C3)), the ranges A1:A5 and C1:C3 don’t intersect, and the formula returns the #NULL! error. If you change this to =CELL("address",(A1:A5 A3:C3)), the CELL function returns the cell address at which the two ranges intersect—cell A3.
Tip: If error checking is turned on in Excel, you can click