You are currently offline, waiting for your internet to reconnect

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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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

  1. 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
  2. Type the following formula in cell C1:
    =OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
  3. Fill this formula across to column F, and then down to row 3.
  4. 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:
  • 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
mailing labels convert data formula transpose XL2007 XL2003 XL2010 XL2013
Properties

Article ID: 214024 - Last Review: 10/25/2012 17:56:00 - Revision: 7.0

Microsoft Office Excel 2003, Microsoft Office Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013

  • kbformat kbconversion kbhowto KB214024
Feedback