Buy yourself some time: Switch rows of data into columns without retyping
by Julie Duncan
Application:
Microsoft Excel 2000/2002/2003/2004/2007
Operating Systems:
Microsoft Windows, Macintosh
When you need to change data from columns to rows or vice versa, spending the time to retype everything is a waste. By using the TRANSPOSE function instead, you can save keystrokes, and Excel will link your transposed data to its source.
To transpose data without duplicating our efforts, we’ll:
• Get a handle on the TRANSPOSE function by exploring its syntax.
• Create linked data that resides in two locations, one row-based and one column-based.
Sometimes a worksheet you’ve already created has the potential to perform a new function in a different worksheet, but you need to tweak it — for example, you need the new worksheet to be row-oriented rather than column-oriented. You may worry that copying and pasting, or retyping, is the only way to make this happen. We’ll show you how you can use the TRANSPOSE function to make Excel do double duty — not you.
When TRANSPOSE trumps Paste Special
Using TRANSPOSE is not the quickest way to transpose data. As we describe in the section “Transpose data when you paste it” below you can use Excel’s Paste feature to transpose information. However, both methods have their benefits in certain situations. Here are some of the times when TRANSPOSE works and pasting falls short:
• You want your transposed data to be linked to its non-transposed source. This means that if you change the non-transposed data, the corresponding transposed data automatically changes.
• You want to transpose data without displaying it. This behind-the-scenes transposing works well when you only need your transposed data for a calculation.
Transpose data when you paste it
If you want to transpose row-based data to columns, or vice versa, consider using the easiest method: Paste Special. This method works well in the following situations:
• You want to retain the selection’s formatting (shading, font, etc.) when you transpose it.
• Your selection contains some blank cells. (In contrast, using TRANSPOSE leaves a zero in any blank cells. We’ll show you a workaround for this problem later in the article.)
To transpose data from rows to columns:
1. Select the cells in the row (or rows) you wish to transpose.
