Solutions Company Statit Training Home
 



Transposing Rows & Columns in Microsoft Excel

Abstract: Statit, like most other statistical packages, assumes that information about an individual or case is contained in the same row. If you are more accustomed to spreadsheets than data tables, this may be opposite the logic you employ. This tip describes a quick method for transposing rows & columns of a Microsoft Excel spreadsheet.

Products: Statit e-QC, Statit e-Server, Statit Custom QC, Statit Express QC

You may not think of it in this way, but each time you build a table of information, you must choose one of two row and column architectures. Statit , like most other statistical packages and reporting tools assumes that information about an individual or case is contained in the same row and that the observation data for each variable are contained in a single column. For example:

Date ID-Code Facility ER Visits
15-Nov-06 PTN-927 GSMH 382
15-Nov-06 PTN-927 GSMC 150
02-Dec-06 Y-1004 GSMH 427
02-Dec-06 Y-1004 GSMC 300

One row gives us all the information we need for a particular number or set of numbers.

Unfortunately, if you are more accustomed to spreadsheets than data tables, you may have employed the opposite logic.

Fortunately, it is easy to transpose rows & columns in Microsoft Excel. To switch rows of cells to columns within MS Excel,

1. Select (block or highlight) the cells that you want to change.
2. Select Edit -> Copy, or right-click the mouse and select Copy. MS Excel "animates" the copy area.
3. Select a single cell that will be the upper-left cell of the paste area. Please remember the paste area must be outside the copy area.
4. On the Edit menu, click Paste Special.
5. Select the Transpose check box toward the lower right side of the dialog box. Data from the top row of the copy area appears in the left column of the paste area, and data from the left column appears in the top row.

Remember Statit expects the data to occupy cells in the upper, left-hand part of the spreadsheet.