To transpose tables in Excel there are several methods available in MS Excel. In this article, I will describe the five simple and quick methods with examples and explanations. 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 Ways 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 that means the rows and columns will be exchanged.
➤ Select the whole table using a mouse.
➤ Press Ctrl+C, a dancing rectangle will appear at the border of the selected range of cells.
➤ Active a new cell where you will transpose the table. Here I have activated cell B12
➤ Press Ctrl+Alt+V, 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.
Method 2: Insert Transpose Function in Excel 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.
➤ Active a new cell. I have activated here B12 cell.
➤ Type =TRANSPOSE(
➤ Soon select the range of the table by dragging with a mouse or by typing manually. Here the range is B4:E9
➤ End the function with “)” and hit the Enter button.
The table is transposed now. See the result below.
- 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
➤ Select the whole table.
➤ Go to Insert menu ribbon
➤ Pick the Pivot Table option. A box will pop up.
➤ Select Existing Worksheet. Or you can select the other option if you want it in a new sheet.
➤ Pick location now. Here I have chosen cell B12.
➤ Press OK, a Pivot Table field will appear.
➤ Mark all the fields available. The Pivot table will be completed.
➤ Go to the bottom of the Pivot Table field.
➤ Press the Product menu and select Move to Column Labels.
➤ 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: Using 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.
➤ Select the whole dataset
➤ Go to Data ribbon
➤ Select From Table. A dialog box will appear.
➤ From the box press OK. A new window named Power Query Editor will appear.
➤ Go to Transform ribbon.
➤ Select Use Headers as First Row.
➤ Press Transpose option.
➤ Then select Use First Row as Headers.
➤ Next Go to File menu
➤ Press Close & Load
The table is transposed now, see the image below.
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.
Step 1: 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.
➤ 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.
➤ Press Ctrl+H. A dialog box named Find and Replace will open up.
➤ Type pk in the Find What box and type = in the Replace with box.
➤ Press Replace All button.
Our operation is done, see the screenshot below for the outputs.
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