Have you ever wanted to completely rearrange the data you have in Excel so that your rows become columns and your columns become rows? Maybe you receive data that’s not formatted the way you need it or maybe you just changed your mind about how you want a spreadsheet arranged. Either way, Paste Special – Transpose is your answer.
All you have to do is highlight the cells you want to rearrange by selecting them with your mouse (or try clicking anywhere in the table and type Ctrl+* on your keyboard). Right-click the selection and choose Copy. Then right-click where you want to place the transposed data (anywhere outside of the already highlighted cells) choose Paste Special, then Transpose. The data will be pasted into the new locations with the former rows now in columns and the former columns in rows.
If you select and copy entire columns and then try to transpose them you’ll get an error. That’s because the maximum number of rows in a worksheet exceeds the maximum number of columns. But you can select entire rows and transpose them if it’s easier than selecting the exact cells in your table.
This is tip was updated January 2017, originally posted November 2010.
