Mahbubur Rahman
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.
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.
- 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.
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.
- 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.
Read More: How to Reverse Order of Columns Horizontally in Excel
Similar Readings
- Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)
- How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
- Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
- How to Swap Rows in Excel (2 Methods)
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.
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
- How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
- How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
- Excel VBA: Transpose Multiple Rows in Group to Columns
- Convert Columns to Rows in Excel Using Power Query
- How to Convert Columns to Rows in Excel Based On Cell Value
- VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)
- How to Convert Column to Comma Separated List With Single Quotes
