How to transpose data in Excel

The Transpose Range tool flips rows and columns in your table. If you need to reshape your worksheet for further processing, use this add-in's functionality and get quick results. Follow the steps below to convert rows to columns or columns to rows in your Excel table.

How to transpose a range in Excel

Open the workbook with your data to transpose and click the Transpose icon in the Transform group on the Ablebits Tools tab in Excel:
Run Transpose.

You can see the Transpose Range dialogue window with the following options:
Transpose Excel range.

  1. By default, the add-in selects the whole dataset in your worksheet. You can select any range to transpose.
  2. To get the entire dataset selected automatically, click the Expand selection icon.
  3. You can also change the range by clicking the Select range icon and choosing it manually.
  4. Decide how you want to paste the results:
    • Paste all. If your original table contains formulas, they will be pasted together with their values.
    • Paste values only. Select this radio button if you don't want to transfer formulas into the resulting table.
    • Create links to source data. This will automatically update the results when the source data changes.
  5. Now you can pick the location for the resulting table:
    • Select the New worksheet radio button to get the table in the existing workbook.
    • Click New workbook to create another file.

Click the Transpose button to see the results.

Responses

Is there a way to transpose or pivot cells in a column to be arranged into a row in the same sheet, I need the data to stay in the same worksheet.

Hello Sarah,

For us to be able to help you better, please send us a small sample workbook with 2 sheets to support@ablebits.com: 1 - your source data and 2 - the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
Note! The result sheet is of great importance and often gives us a better understanding of your task rather than any text description. Please don't forget to include it.

We'll look into your task and see if our software can help.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.