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

Step 1:

➤ Select the whole table using a mouse.

➤ Press Ctrl+C, a dancing rectangle will appear at the border of the selected range of cells.

Step 2:

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

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

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.

Steps: 

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

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.

Step 1:

➤ Select the whole table.

➤ Go to Insert menu ribbon

➤ Pick the Pivot Table option. A box will pop up.

Step 2:

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

Step 3:

➤ Mark all the fields available. The Pivot table will be completed.

Step 4: 

➤ Go to the bottom of the Pivot Table field.

➤ Press the Product menu and select Move to Column Labels.

Step 5: 

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

Step 1:

➤ Select the whole dataset

➤ Go to Data ribbon

➤ Select From Table. A dialog box will appear.

Step 2:

➤ From the box press OK. A new window named Power Query Editor will appear.

Step 3: 

➤ Go to Transform ribbon.

➤ Select Use Headers as First Row.

Step 4: 

➤ Press Transpose option.

Step 5: 

➤ Then select Use First Row as Headers.

Step 6:

➤ Next Go to File menu

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

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.

Step 2: 

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

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