Microsoft Office Tutorials and References
In Depth Information
Tip 52: 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 52-1 shows an example. Column
A contains employee information, and each “record” consists of three consecutive cells in a single
column: Name, Department, and Location. The goal is to convert this data so that each record
appears in a single row, with three columns.
Figure 52-1: Vertical data that needs to be converted to three columns.
You can convert this type of data several ways, but here’s a method that’s fairly easy. It uses a single
formula, which is copied to a range.
Enter the following formula in cell C1, and then copy it down and across.
=INDIRECT(“A” &COLUMN()-2 + (ROW()-1)*3)