‘Transpose’ to change the alignment of cell contents in Microsoft Excel

4.7
(19)

Imagine you made a sales report in Microsoft Excel vertically and now you are requested to show the opposite way in columns horizontally. Do you feel like wasted so many hours doing the report? And again, you have to spend a few more hours to prepare the expected report.

Neither re-do nor moving cells here and there manually will help. So what is the solution?

Can a Microsoft Excel report made vertically be rotated to show in columns, quickly?

The answer is, “yes”.

It may be a small feature in Microsoft Excel, yet very useful feature in a similar situation you are in. Most importantly, it works to change a series of contents in Microsoft Excel worksheet aligned from vertical to horizontal or vice versa quickly.

Pr-requisites:

Microsoft Excel version 2013 or higher.

For instance, imagine we have a series of values entered as per below from cells ranging b2:c6 in a sequence.

I-know-how-it-works-Excel-transpose-figure-01

The above example may be very small and you may think what is the big deal converting these contents the opposite way. Certainly, the real reason behind this is that I purposely thought to keep the example very simple thinking you would understand better.

A real-life scenario

When we consider a long list similar to a series of city names, a list of countries, a list of surnames, a sales report, etc in a different scenario, actually the ‘Transpose’ feature can play a vital role.

Even further, let’s consider a real-world example. Assume you have entered a list of product names into a series of cells vertically, say you have entered around 500 or more till now.

Probably this is what will shock you. Let’s assume the order you entered (data) has to be maintained and you entered them vertically. Your Manager comes in and requests you to change the contents from vertical to horizontal, shocked?.

What is the issue? The issue is, you have entered more than 500 product names in order and now you need to change it to the opposite. Frustrated? seems like this is a real need to have such a feature for your rescue.

Sales report example step by step

  1. Highlight the data you need (b2:c6) to transpose (including any column or row headers).
  2. Use Ctrl + C keys to copy it.
  3. Now move to cell address e2 and Right click on the cell and, click “Paste special” option.
    I-know-how-it-works-Excel-transpose-figure-02
  4. The Paste Special Dialog box should appear with “Transpose” checkbox as shown below.
    I-know-how-it-works-Excel-transpose-figure-03
  5. Mark the Transpose check-box and Click OK button to finish. You will see the below results (e2:i3).
    I-know-how-it-works-Excel-transpose-figure-04
  6. Finally highlight and press the Del key to clear the contents in the vertical range of cells (b2:c6) which is no longer required.

Try it out yourself and post your comments below. Furthermore, you may access our Forum section to discuss your questions.

Watch the Microsoft Excel Transpose Video

How useful was this post?

Click on a star to rate it!

Average rating 4.7 / 5. Vote count: 19

Leave a Reply