Transpose Excel data from rows to columns, or vice versa

When you need to flip-flop data in an Excel worksheet, don’t waste time doing it manually. Excel offers a handy Transpose option that will quickly take care of the task.

Here’s a tip that eliminates the need to rekey data. Suppose you’ve entered your data with three column headings running across Row 1 and four row headings running down Column A, like the ones shown in Figure A.

Figure A

After working with the data for a while, you decide you’d rather have the current set of row labels (months) running across the columns. Whatever you do, don’t even think about rekeying the data.

You’ll find the best solution on the Paste Special menu. Start by selecting and copying your entire data range. Click on a new location in your sheet, then go to Edit | Paste Special and select the Transpose check box, as shown in Figure B. Click OK, and Excel will transpose the column and row labels and data, as shown in Figure C.

Figure B

Figure C

Note

You aren’t limited to using the Paste Special | Transpose option to rearrange multiple rows and columns of data. It works just as well when you need to turn a single row of labels into a column, or vice versa.