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.

How to Reverse Transpose in Excel


Download Practice Workbook


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.

How to Reverse Transpose in Excel methods

  • 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.

How to Reverse Transpose in Excel by SORT

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

How to Reverse Transpose in Excel by 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.

How to Reverse Transpose in Excel by SORT

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

How to Reverse Transpose in Excel by SORT

That’s it. Simple.

Read More: Excel Paste Transpose Shortcut: 4 Easy Ways to Use


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.

How to Reverse Transpose in Excel by paste spcial an transpose function

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

  • From here, click Transpose and then click OK.

How to Reverse Transpose in Excel by SORT

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.

How to Reverse Transpose in Excel by Transpose function

  • 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)

How to Reverse Transpose in Excel by paste and transpose

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

Done.

Read More: How to Reverse Order of Columns Horizontally in Excel


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.

How to Reverse Transpose in Excel by INDIRECT FUNCTION

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.

How to Reverse Transpose in Excel by INDIRECT FUNCTION

  • 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.

How to Reverse Transpose in Excel

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.

Read More: How to Transpose Rows to Columns Based on Criteria in Excel (2 Ways)


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo