Microsoft Office Tutorials and References

In Depth Information

**Using structured references**

ill handle to copy the % of Total formula in cell K4 to the right, and the column specifiers

in the formulas adjusted accordingly.

Figure 12-25
You can drag the ill handle to extend structured reference formulas into adjacent

cells, but they behave a little bit differently from regular formulas.

The results illustrate some interesting structured-reference behavior. Notice that the first

formula shown in cell K4 divides the value in the Qtr 1 column by the value in the Total

column. After we filled to the right, the resulting formula in cell N4 divides the value in the

Qtr 4 column by the value in the Qtr 2 column. How did this happen?

As far as filling cells is concerned, tables act like little traps—you can check in, but you can’t

check out. The formula shown at the top in Figure 12-25 has two column specifiers: Qtr 1

and Total. When we filled to the right, the Qtr 1 reference extended the way we wanted—

to Qtr 2, Qtr 3, and Qtr 4 in each cell to the right. However, the Total reference, instead of

extending to the right (G4, H4, I4) like a regular series ill would, “wrapped” around the

table (2013, Qtr 1 and Qtr 2), resulting in the formula displayed in cell N4 at the bottom of

Figure 12-25. This is interesting behavior, and we’re sure people will figure out ways to put

it to good use.

For more information about using the ill handle, see “Filling cells and creating data series” in

Chapter 8.

What we need is a way to “lock” the Total column reference, but Excel doesn’t offer any way

to create “absolute” column specifiers, as it does with cell references. We can, however,

substitute a cell reference for the entire Total reference, as shown in Figure 12-26. We used a