Select the product you need help with

How to convert multiple rows and columns to columns and rows in Excel

Article ID: 214024 - View products that this article applies to.

This article was previously published under Q214024

Summary

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).

Back to the top | Give Feedback

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.

Example

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
Type the following formula in cell C1:
=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
Fill this formula across to column F, and then down to row 3.
Adjust the column sizes as necessary. Note that the data is now displayed in cells C1 through F3 as follows:

   Smith, John     111 Pine St.    San Diego, CA   (555) 128-549
   Jones, Sue      222 Oak Ln.     New York, NY    (555) 238-1845
   Anderson, Tom   333 Cherry Ave. Chicago, IL     (555) 581-4914

The formula can be interpreted as

OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()-f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))

where: