« Flying Blind: The Rise, Fall, and Possible Resurrection of Science Policy Advice in the United States | Main | NYT on e-books: 'An Idea Whose Time Has Come Back' »

Excel TRANSPOSE Function

Suppose you need to convert Excel data reported in this format, where the grid has hours in rows and dates in columns:

Example 1
      1-Nov 2-Nov 3-Nov
10:00     1     5     9
11:00     2     6    10
12:00     3     7    11
 1:00     4     8    12

to just the opposite, i.e., hours in columns and dates in rows:

Example 2
      10:00 11:00 12:00  1:00
1-Nov     1     2     3     4
2-Nov     5     6     7     8
3-Nov     9    10    11    12

This is a minor pain to do and almost entirely undocumented, yet I have real data that I receive in the first format (QuestionPoint usage data) and need to report in the second (for my library's reference report).

The original grid in Example 1 is an array of 4 columns in 5 rows -- i.e, it is a 4x5 array. The converted array in Example 2 is 5x4. This is important to make note of as it will affect the result if done improperly.

  1. In either the same or a separate Excel worksheet, select an array of empty cells that will form your intended result. If your original array is 7x31, your result will be 31x7.

  2. Type =TRANSPOSE( in the formula box. Then select all the cells of the untransposed original.

  3. The transpose formula must be applied to the entire array (not just a cell). You do this by saving the transpose formula to the woksheet with [control][shift][enter]. If you simply hit [enter], you will get an error. However, because it is applied to the entire array, this also means that you can not alter the value a single cell in the transposed array (although changes made to the original array will automatical broadcast to the transposed array).

  4. If you need to do something useful with the resulting data, I have found the easiest way to deal with it is to avoid all the problems that result from the dependency of the transposition. Select the transposed array, copy and paste it from the Excel spreadsheet into an ascii text editor, such as Notepad or NoteTab, and then select and copy that data from Notepad into a new Excel worksheet.

Note: In step 3, although you can not change the value an individual cell, you can change its formatting. For example, I discovered that the dates in the first column were not properly formatted. E.G., "1-Nov" appeared in its raw form of "38292". I simply had to reapply the date format back to these cells to fix it.

Posted by Tom on December 06, 2004