Microsoft Office Tutorials and References
In Depth Information
Working with Range and Cell Names
Figure 3-10: This worksheet contains named ranges that correspond to row and column labels.
Excel can also perform implicit intersections, which occur when Excel chooses a value from a
multicell range based on the row or column of the formula that contains the reference. An
example should clear this up. Figure 3-11 shows a worksheet that contains a range (B3:B8) named
MyData. Cell D5 contains the simple formula shown here:
Figure 3-11: Range B3:B8 in this worksheet is named MyData. Cell D5 demonstrates an implicit intersection.
Notice that cell D5 displays the value from MyData that corresponds to the formula’s row.
Similarly, if you enter the same formula into any other cell in rows 3 through 8, the formula
displays the corresponding value from MyData. Excel performs an implicit intersection using the
MyData range and the row that contains the formula. It’s as if the following formula is being
If you enter the formula in a row not occupied by MyData, the formula returns an error because
the implicit intersection returns nothing.