Microsoft Office Tutorials and References
In Depth Information
Using structured references
Let’s look at an example of a structured reference formula. Figure 12-23 shows a SUM
formula that we created by first typing =SUM( , clicking cell B4, typing another comma, and
finally clicking cell C4. Because the data we want to use resides within a table and our
formula is positioned in one of the same rows, Excel automatically uses structured references
when we use the mouse pointer to select cells while building formulas.
Figure 12-23 We created the formula in cell H4 by clicking to select cells in the table.
The result shown in the formula bar appears to be much more complex than necessary
because we could just type =SUM(B4:C4) to produce the same result in this worksheet. But
the structured formula is still quite easy to create using the mouse, and it has the distinct
advantage of being able to automatically adjust after even the most radical changes to the
table, which ordinary formulas are not nearly as good at accommodating.
Let’s examine a little more closely the structured reference contained within the
parentheses of the SUM function shown in Figure 12-23. The entire reference string shown
here is equivalent to the expression (B4,C4), which combines the cells on both sides of the
comma. The portion of the reference string in bold represents a single, complete structured
Regional13[@[Qtr 1]],Regional13[@[Qtr 2]])
Here’s how the reference string breaks down:
● The first item, Regional13, is the table specifier, which is followed by an opening
bracket. Just like parentheses in functions, brackets in structured references always
come in pairs. The table name is a little bit like a function, in that it always includes
a pair of brackets that enclose the rest of the reference’s components. This tells Excel
that everything within the brackets applies to the Regional13 table.
The second item, @, tells Excel that the following reference components apply only
to those portions of the table that fall in the current row. (Obviously, this doesn’t
work if the formula is located above or below the table.) This represents an
application of implicit intersection. (See “Getting explicit about intersections” earlier in this