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

Get FREE Advanced Excel Exercises with Solutions!

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.


1. Using Paste Special Tool to Transpose a 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.

how to transpose a table in excel

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.

how to transpose a table in excel result

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


2. Inserting TRANSPOSE Function to Switch a Table

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:
=TRANSPOSE(B4:E9)
  • Hit Enter to see the result.

Insert TRANSPOSE Function to Switch Table in Excel

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

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


Similar Readings


3. Transposing a Table Using Excel Pivot Table

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.

Use Pivot Table to Transpose a Table in Excel

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


4. Applying Excel Power Query to Transpose a Table

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.

Using Power Query in Excel to Transpose

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

Method 4: Using Power Query in Excel to Transpose

  • Afterward, press Transpose option.

Method 4: Using Power Query in Excel to Transpose

  • Then, select Use First Row as Headers.

Method 4: Using Power Query in Excel to Transpose

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

Method 4: Using Power Query in Excel to Transpose

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

Method 4: Using Power Query in Excel to Transpose

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


5. Using Direct Cell Reference for Transposing a Table

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.

Direct Reference to Transpose a Table in Excel

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


Download Practice Book

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


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

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.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo