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.