Microsoft KB Archive/214024

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

Article ID: 214024

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214024





For a Microsoft Excel 98 version of this article, see 192357.



For a Microsoft Excel 97 version of this article, see 116289.



SUMMARY
When you use the Microsoft Excel products listed at the beginning of this article, you can use a worksheet formula to covert data that spans multiple rows and columns to a database format (columnar).



MORE INFORMATION
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:

<pre class="fixed_text">  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:
 * f_row = row number of this offset formula
 * f_col = column number of this offset formula
 * rows_in_set = number of rows that make one record of data
 * col_in_set = number of columns of data

Additional query words: mailing labels convert data formula transpose XL2007 XL2000 inf XL2002 XL2003

Keywords: kbformat kbconversion kbhowto KB214024

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.