ExcelInsider Team
A Pivot Table is a tool for summarizing and analyzing data, but its default orientation might not always fit your needs. Transposing a Pivot Table allows you to swap its rows and columns, providing a new perspective on your data. In this article, we will explore three different methods to transpose a Pivot Table, each with its own advantages.
➤ Select the destination cell where you want the transposed data.
➤ Enter the Formula:
=TRANSPOSE(A3:C8)
➤ Press ENTER, and you will get the Pivot Table data transposed.
Using Paste Special Tool to Transpose Pivot Table
This method is the simplest way to transpose a PivotTable if you don’t need the new table to be dynamic. Here we will use the Paste Special tool to transpose data.
Suppose we have a dataset of some Products, Units Sold, Unit Price, and Total Price. Now, we will transpose the data using the Paste Special tool.
➤ Select your entire data range and go to the Insert tab.
➤ Click on PivotTable.
➤ From the new window, choose New Worksheet in the dialog box and press OK.
Thus, the Pivot Table will be created.
➤ Select the entire PivotTable, and copy it by pressing Ctrl + C .
➤ Select an empty cell where you want the transposed data.
➤ Go to the Home tab, click the dropdown arrow under Paste, and select Paste Special.
The Paste Special dialog box will appear.
➤ Checkmark the Transpose box at the bottom right.
➤ Click OK.
As a result, you will get the transposed data for the Pivot Table
Applying TRANSPOSE Function
The TRANSPOSE function in Excel is a dynamic tool that flips the orientation of a range or array, converting rows into columns and vice versa.
➤ Select a cell, write down the formula below, and press ENTER.
=TRANSPOSE(A3:C8)
As a result, you will get the transposed data for the Pivot Table.
Rearranging PivotTable Fields
This is the most common method for transposing a Pivot Table, as it doesn’t create a new table but rather modifies the structure of the existing one. This method is used when you want to keep the full functionality of the PivotTable.
➤ Go to the PivotTable Analyze tab, click Show, and then select Field List.
➤ In the PivotTable Fields pane, go to the Rows area and click the drop-down arrow on the Product field.
➤ Select Move to Column Labels.
➤ Now, locate the Values field, and click the drop-down arrow on the Values field.
➤ Select Move to Row Labels.
Thus, you will get the Pivot Table rearranged where the products as columns and the summary values for units sold and total price in rows.
Frequently Asked Questions
Can I directly transpose a Pivot Table in Excel?
No, Excel does not have a direct “Transpose Pivot Table” button. However, you can use tools like Paste Special or manually rearrange fields.
Will transposing a Pivot Table keep it dynamic?
If you use the Paste Special tool, the data becomes static and will not update with the source. To keep it dynamic, adjust the field layout in the Pivot Table.
Will slicers and filters still work after transposing?
If you transpose by rearranging fields (drag & drop), slicers and filters will continue to work. If you use the Paste Special tool, slicers will not affect the transposed data.
Concluding Words
Above, we have explored all the methods to transpose a Pivot Table in Excel. For transposing data, you can use the Paste Special tool or the TRANSPOSE function. Similarly, by dragging fields between the Rows and Columns areas, you can instantly rearrange your Pivot Table. If you have any questions, feel free to leave them in the comments below.
