How to convert multiple rows and columns to columns and rows in Excel
Content provided by Microsoft
Applies to: Microsoft Office Excel 2003Microsoft Office Excel 2007Microsoft Excel 2010Excel 2013More
When you use the Microsoft Excel products listed at the bottom of this article, you can use a worksheet formula to covert data that spans multiple rows and columns to a database format (columnar).
The following example converts every four rows of data in a column to four columns of data in a single row (similar to a database field and record layout). This is a similar scenario as that which you experience when you open a worksheet or text file that contains data in a mailing label format.
In a new worksheet, type the following data:
A1: Smith, John A2: 111 Pine St. A3: San Diego, CA A4: (555) 128-549 A5: Jones, Sue A6: 222 Oak Ln. A7: New York, NY A8: (555) 238-1845 A9: Anderson, Tom A10: 333 Cherry Ave. A11: Chicago, IL A12: (555) 581-4914