How to use the Transpose paste Function in Excel - Photo by Chase Clark on Unsplash There are many times when I walk the floor of a company. One where I have done some training a few days or weeks before, where I see that people don’t know that there is even a feature called “Transpose”. I see them struggling to re-create the spreadsheet they had into one that has turned by 90 degrees. They often make mistakes that retyping can bring. They get frustrated and hurry as time is always of the essence, which leads to more errors.

Transpose is a lovely feature that can save a lot of time by moving everything from vertical to horizontal or horizontal to vertical. Why do we need this? Well, when you start to plan a spreadsheet, you don’t always know what you might do to progress it. Sometimes you start in one direction and find you need more columns or more rows than you have allocated at the start. Or simply that it is more natural to move in the other direction first when completing or reading the data in the spreadsheet.

Please do not start typing all the components again from scratch in the correct location.

How to use transpose

As an example spreadsheet. example

Then you realise that you would prefer the Quarters to run down the page. After all, you have over a million and half rows and only 17,000 columns.

  • Select the whole area. [A1:F6]
  • Copy this area.
  • Select a new starting cell. This will be the top left of the area so please make sure you have enough empty cells for this transposed area. i.e. 6 rows in this example.
  • Right mouse click on that cell.
  • From the list in the paste options, select “Transpose”. Transpose icon

This is the result you will see. Result

All the elements are there in the correct position, and the formulae are calculating the correct results.

You can select and copy any area and can transpose that. You cannot, however, select multiple areas at the same time to be transposed.

Formatting across sheets in Excel

Creating several sheets in Excel with the same titles and calculations simultaneously

How to change case in Excel




Please enter your comment!
Please enter your name here