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

reference:

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

chapter.)

●