Microsoft Office Tutorials and References
In Depth Information
Figure 16-19: A single formula transforms the vertical data into rows.
You can easily adapt this technique to work with vertical data that contains a different number of rows. For ex-
ample, if each record contained ten rows of data, the column C header values would be 1, 11, 21, 31, and so on.
The horizontal headers would consist of values 1–10 instead of 1–3.
Notice that the formula uses an absolute reference to cell A1. That reference won't change when the formula is
copied, so all the formulas use cell A1 as the base. If the data begins in a different cell, change $A$1 to the ad-
dress of the first cell.
The formula also uses “mixed” referencing in the second argument of the OFFSET function. The C2 reference
has a dollar sign in front of C, so column C is the absolute part of the reference. In the D1 reference, the dollar
sign is before the 1, so row 1 is the absolute part of the reference. See Chapter 2 for more about absolute and
Filling gaps in an imported report
When you import data, you can sometimes end up with a worksheet that looks something like the one shown in
Figure 16-20. This type of report formatting is common. As you can see, an entry in column A applies to sever-
al rows of data. If you sort this type of list, the missing data messes things up, and you can no longer tell who
sold what when.
Figure 16-20: This report contains gaps in the Sales Rep column.