How to Reverse Transpose in Excel (3 Simple Methods)

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

 

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo