Microsoft Office Tutorials and References
In Depth Information
Figure 16-17: Vertical data that needs to be converted to three columns.
There are several ways to convert this type of data, but here's a method that's fairly easy. It requires a small
amount of set-up, but the work is done with a single formula, which is copied to a range.
Start by creating some numeric vertical and horizontal “headers,” as shown in Figure 16-18. Column C contains
numbers that correspond to the first row of each data item (in this case, the Name). In this example, I put the
following values in column C: 1, 4, 7, 10, 13, 16, and 19. You can use a simple formula to generate this series of
The horizontal range of headers consists of consecutive integers, starting with 1. In this example, each record
contains three rows of data, so the horizontal header contains 1, 2, and 3.