How to Transpose a Table in Excel (5 Suitable Methods)

To transpose tables in Excel, there are several methods available in MS Excel. In this article, I will describe the five suitable methods with examples and explanations to transpose a table in Excel. Just go through the steps and have a sharp look at the screenshots.

Download Practice Book

Download the Excel workbook that we’ve used to prepare this article.

5 Suitable Methods to Transpose a Table in Excel

Method 1: Use Paste Special Tool to Transpose Table in Excel

Let’s get introduced to the dataset first. In the following table, I have used 4 columns with the headers of Product, Price, Quantity, and Total and 6 rows with the headers of some fruits names.

By using the Paste Special option here, I’ll transpose the table which means the rows and columns will be exchanged.

STEPS:

• Firstly, select the whole table using a mouse.
• Press Ctrl + C, a dancing rectangle will appear at the border of the selected range of cells.

• Secondly, activate a new cell where you will transpose the table. Here I have activated cell B12.
• Press Ctrl + Alt + V, and the Paste Special box will pop up.
• Mark the Transpose box and press OK.

• Now, see the table has been transposed to the new location.

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

Method 2: Insert TRANSPOSE Function to Switch Table in Excel

Using a function is one of the easiest methods to perform a task in Excel. Here we shall do it with the Transpose function.

STEPS:

• First of all, active a new cell. I have activated here B12 cell.
• Then, type the formula below:

• Hit Enter to see the result.

• The table is transposed now. See the result below.

Read More: How to Transpose Array in Excel (3 Simple Ways)

Similar Readings

• How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
• How to Convert Columns to Rows in Excel (2 Methods)
• Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
• How to Reverse Transpose in Excel (3 Simple Methods)

Method 3: Use Pivot Table to Transpose a Table in Excel

In this method, I will use the Pivot Table to Transpose the data. It is pretty lengthy but simple.

STEPS:

• In the first place, select the whole table.
• Then, go to the Insert menu ribbon.
• After that, pick the Pivot Table option. A box will pop up.

• Secondly, select Existing Worksheet. Or you can select the other option if you want it in a new sheet.
• Then, pick the location now. Here I have chosen cell B12.
• Afterward, press OK, and a Pivot Table field will appear.

• Thirdly, mark all the fields available. The Pivot table will be completed.

• In the following step, go to the bottom of the Pivot Table field.
• Then, press the Product menu and select Move to Column Labels.

• Finally, press the Values menu and select Move to Row Labels.

• Look, the Pivot Table has been transposed.

Read More: Conditional Transpose in Excel (2 Examples)

Method 4: Utilize Power Query in Excel to Transpose

Power Query is one of the best methods to transpose. Here I will show it in a basic way.

STEPS:

• Firstly, select the whole dataset.
• Then, go to the Data ribbon.
• After that, select From Table/Range.

• Secondly, in the Power Query Editor, go to the Transform ribbon.
• Then, select Use Headers as First Row.

• Afterward, press Transpose option.

• Then, select Use First Row as Headers.

• Next, go to File menu.
• Finally, press Close & Load.

• The table is transposed now, see the image below.

Read More: Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)

Method 5: Direct Reference to Transpose a Table in Excel

It’s like a manual method but pretty simple and time-saving. Here we will use a cell reference to transpose.

STEPS:

• As we want to convert Row 4 to a column, we’ll use the row’s cell references along a new column with some same unique characters before the cell references. I have used “pk”.
• Then, select the new range of cells.
• Afterward, click and hold the bottom right corner of the border and drag it to the right until it completes 6 columns. Because we had 6 rows.

• Now, select the whole new cells.
• Then, press Ctrl + H. A dialog box named Find and Replace will open up.
• Later on, type pk in the Find What box and type = in the Replace with box.
• Finally, press Replace All button.

• Our operation is done, see the screenshot below for the outputs.

Read More: Excel Transpose Formulas Without Changing References (4 Easy Ways)

Conclusion

I hope all of the methods described above will be effective to transpose a table in Excel. Feel free to ask any questions in the comment section and please give me feedback.

Related Articles

• Data clean-up techniques in Excel: Changing vertical data to horizontal data
• How to Swap Rows in Excel (2 Methods)
• Convert Columns to Rows in Excel (2 Methods)
• Excel VBA: Transpose Multiple Rows in Group to Columns
• How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
  
• Excel Transpose Formulas Without Changing References (4 Easy Ways)
• Transpose Multiple Rows in Group to Columns in Excel