# How to Reverse Transpose in Excel (3 Methods)

## Dataset Overview

Weâ€™ll work with a sample dataset containing columns for Name, Age, Gender, and Monthly Income.

### Method 1 – Reverse Transpose Using Sort

• Copy the entire dataset and paste it as values in cell F4.

• Click on TransposeÂ (as shown in the image).

However, this wonâ€™t give us the desired result; we want Andrew at the top of the list.

• Add an extra column called LevelÂ and fill it using the AutoFill option.

• Select the data (including the header) and go toÂ Data > Sort.

• In the dialog box, choose Level and sort it from Largest to Smallest.

• Now our dataset should look like the following image:

• Finally, delete the LevelÂ column and transpose the data again.

### Method 2: Reverse Transpose Using Transpose Function

• Transpose the data usingÂ Paste Special:
• Select the data range and pressÂ CTRL+C.

• Click anywhere and pressÂ CTRL+ALT+VÂ to open the dialog box.

• Choose TransposeÂ and clickÂ OK.

Although this wonâ€™t give us the desired result, weâ€™ll need these border areas later.

• Add a Helper column with the formula:
`=ROW()`

• Press ENTER key and drag it down to AutoFill the rest of the seriesÂ (indicating row numbers).

• Select the dataset and go toÂ Data > Sort.

• Sort the Helper column from Largest to Smallest.

• Now our data should resemble the following image.

• Select the previously transposed data, press Delete, then enter the following formula:
`=TRANSPOSE(B4:E12)`

• PressÂ ENTER. If youâ€™re using a lower version of Excel (before 2019), useÂ CTRL+SHIFT+ENTER.

### Method 3: Using INDIRECT Function to Reverse Transpose

• Weâ€™ll start by transposing the data (as shown in the image).

• Click on cell G3 and enter the formula:
=`COLUMN()`

• Drag right and use AutoFill to complete the series.

• In column P, type the numbers 4, 5, and 6 (indicating column and row numbers).

• Click on cell G7 and enter the formula:
`=MAX(G3:O3)`

• Press ENTER.

• In cell H7, enter the formula:
`=G7-1`
• Press Enter and AutoFill the rest of the series.

• In another cell, enter the formula:
`=INDIRECT(ADDRESS(\$P4,G\$7))`

• PressÂ ENTERÂ and fill the rest of the series by dragging.

The ADDRESS function provides the cell location based on the given row and column numbers. For example, ADDRESS(\$P4,G\$7) yields the output \$O\$4. The INDIRECT function retrieves the value from that cell, which in this scenario would be Jason.

## Practice Section

Weâ€™ve attached a practice workbook for you to practice these methods.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF