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
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