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