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.

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

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.

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

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.

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

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

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

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.

Method 2: Insert Transpose Function in Excel Table

The table is transposed now. See the result below.

Method 2: Insert Transpose Function in Excel Table

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


Similar Readings


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.

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

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.

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

Step 3:

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

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

Step 4: 

➤ Go to the bottom of the Pivot Table field.

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

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

Step 5: 

➤ Press the Values menu and select Move to Row Labels.

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

Look the Pivot Table has been transposed.

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

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.

Method 4: Using Power Query in Excel to Transpose

Step 2:

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

Method 4: Using Power Query in Excel to Transpose

Step 3: 

➤ Go to Transform ribbon.

➤ Select Use Headers as First Row.

Method 4: Using Power Query in Excel to Transpose

Step 4: 

➤ Press Transpose option.

Method 4: Using Power Query in Excel to Transpose

Step 5: 

➤ Then select Use First Row as Headers.

Method 4: Using Power Query in Excel to Transpose

Step 6:

➤ Next Go to File menu

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


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.

Method 5: Direct Reference to Transpose a Table in Excel

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.

Method 5: Direct Reference to Transpose a Table in Excel

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

Method 5: Direct Reference to Transpose a Table in Excel

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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo