How to transpose data in Excel

The Excel's 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 pane with the following options:
Transpose Excel range.

  1. By default, the add-in highlights the whole range with data in your worksheet. You can select the needed range in your Excel worksheet or enter it manually in this field.
  2. To get the used range highlighted again, select a single cell in the table and 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 an easy way to transpose multiple rows of data into a single cell (as opposed to transposing into the same number of columns as rows)?

Reply

Hi Rob,
Thank you for your comment. If I have understood your task correctly, the Merge Cells tool should help you: https://www.ablebits.com/excel-merge-cells/index.php

Please do not hesitate to contact us with any other questions.

Reply

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard