Microsoft Office Tutorials and References
In Depth Information
Converting a Vertical Range to a Table
Converting a Vertical Range to a Table
Often, tabular data is imported into Excel as a single column. Figure 72-1 shows an example.
Column A contains name and address information, and each “record” consists of five rows.
Figure 72-1: This vertical range of data needs to be converted to a table.
Excel doesn’t provide a direct way to convert such a range to a more usable table. But a few
clever formulas will do the job. Insert the following formulas in range C1:G1:
C1: =INDIRECT(“A” & ROW()*5-4)
D1: =INDIRECT(“A” & ROW()*5-3)
E1: =INDIRECT(“A” & ROW()*5-2)
F1: =INDIRECT(“A” & ROW()*5-1)
G1: =INDIRECT(“A” & ROW()*5-0)
After you enter the formulas, copy them to the rows below to accommodate the amount of data
in column A. Figure 72-2 shows the result.
Search JabSto ::

Custom Search