How to Reverse Transpose in Excel (3 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

We all know about transposing in Excel. What about reverse transpose? It is slightly a different concept. In this web post, we will see How to Reverse Transpose in Excel. For your better understanding, we will use a sample dataset here.

Table of Contents hide

Download Practice Workbook

3 Ways to Reverse Transpose in Excel

Method 1: Reverse Transpose Using Sort

Method 2: Reverse Transpose Using Transpose Function

Method 3: Using INDIRECT Function to Reverse Transpose

Practice Section

Conclusion

Download Practice Workbook

Reverse Transposing.xlsx

3 Ways to Reverse Transpose in Excel

In this article, we will see both traditional and dynamic approaches to reverse transposing in Excel using Sort, Paste Special and INDIRECT functions.

Method 1: Reverse Transpose Using Sort

So, we have a sample dataset containing Name, Age, Gender, and Monthly Income. Suppose, this data is in order according to our guest preference from least to most. First, we will transpose it, and let’s see what happens.

Copy the entire dataset and paste the value in cell F4.

Now, click on Transpose as shown in the image.

But it is not the result we wanted, we wanted it in transposed form but Andrew in the top of the list.

Steps:

At first, we will add an extra column Level, and fill the list by AutoFill option.

Now, select the data including the Header, and go to Data > Sort.

As a result, a dialogue box will pop up, select Level, and sort it from Largest to Smallest.

At this point, our dataset looks like the following image.

Finally, delete the Level column and transpose it, as we had done at the beginning of the method.

That’s it. Simple.

Method 2: Reverse Transpose Using Transpose Function

In this method, we will see the use of the ROW function, TRANSPOSE function and Paste special to reverse transpose in Excel.

Steps:

First, we will transpose the data using Paste Special. Select the data range and press CTRL+C.

Then, click anywhere and press CTRL+ALT+V and a dialogue box will pop up.

From here, click Transpose and then click OK.

But this is not the result we wanted. But don’t worry, we will need these border areas after a while.

Now, add a Helper column and type the following.

=ROW()

Press ENTER key and drag it down to AutoFill the rest of the series.

This indicates the row numbers. At this point select the dataset and go to Data > Sort and a dialogue box will pop up.

From the box, sort Helper from largest to smallest.

Now, our data looks like the following image.

At this point, select the previously transposed data, press Delete then equal to sign (=), and type the following formula.

=TRANSPOSE(B4:E12)

Finally, press ENTER key. In case you are using a lower version of Excel from 2019 press CTRL+SHIFT+ENTER.

Done.

Similar Readings

Method 3: Using INDIRECT Function to Reverse Transpose

This is a dynamic method and pretty interesting. So, we already know how to transpose.

Firstly we will transpose the data and our dataset is the following image.

Here, we will see the use of the INDIRECT and ADDRESS functions.

Steps:

First, click on cell G3 and type the following formula.

=COLUMN()

Then, drag right and AutoFill the series.

And, now, type 4, 5, and 6 in column P as shown in the image, these numbers indicate column and row numbers basically.

At this point, click on cell G7 and type the following formula.

=MAX(G3:O3)

Press ENTER key.

Now, type the following formula in cell H7 and AutoFill rest of the series.

=G7-1

At last, we are done now. At this point, type the following formula in the cell

=INDIRECT(ADDRESS($P4,G$7))

Press ENTER key and fill the rest of the series dragging. Our final dataset will look like the following image.

The ADDRESS function mainly gives us the location of the cell according to the given Row and Column numbers. ADDRESS($P4,G$7) yields output as “$O$4“. The INDIRECT function the gives the value of that cell which is Jason in the above scenario.

Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.

Conclusion

That’s all for the article. These are 3 different methods on How to Reverse Transpose in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.

How to Reverse Transpose in Excel (3 Simple Methods)

Download Practice Workbook

3 Ways to Reverse Transpose in Excel

Method 1: Reverse Transpose Using Sort

Method 2: Reverse Transpose Using Transpose Function

Method 3: Using INDIRECT Function to Reverse Transpose

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply