Pivot tables are one of Excel’s most powerful features for summarizing data — but what if you want to pivot a pivot table?
Maybe you’ve built one pivot table for detailed analysis, and now you need to roll those results up again into a higher-level report. Or you’ve created a pivot table in one layout and want to reorganize it without touching the original data source.
In this guide, we’ll explain what “pivot a pivot table” means, why Excel doesn’t make it straightforward, and four different methods to make it happen. We’ll also cover related tips like creating a pivot table from another pivot table, pivot table to table conversion, and how to use Power Query for more advanced workflows.
What Does “Pivot a Pivot Table” Mean?
When Excel users talk about “pivoting a pivot table,” they usually mean one of two things:
- Creating a pivot table from the results of another pivot table
- You want to use your first pivot table’s output as the data source for a new pivot table.
- Example: Summarizing weekly sales data in one pivot, then creating another pivot to roll up those weekly totals into quarterly totals.
- Changing the layout of an existing pivot table
- Rearranging row fields, column fields, and filters to see the data from a different angle without rebuilding the whole thing.
Why Excel Doesn’t Allow a Pivot from a Pivot Directly
By default, Excel won’t let you set another pivot table as the direct source for a new pivot table. This is because pivot tables store their data in a “pivot cache” that isn’t structured for re-pivoting.
But there are workarounds — and there’s also a more modern alternative: data cubes.
📌 For a deeper explanation of how cubes work and why they make reporting faster, check out our article: Introduction to Data Cubes.
Method 1 – Copy, Paste Values, and Create a New Pivot Table
This is the most straightforward way to create a pivot from another pivot.
Steps:
- Select your original pivot table.
- Copy it (Ctrl+C).
- Paste as values (Right-click → Paste Special → Values) into a new sheet or location.
- Select this pasted data.
- Go to Insert → PivotTable and create your new pivot table from it.
When to use it:
- Quick, one-time reporting needs.
- When you want to avoid linking to the original pivot cache.
Method 2 – Use the Pivot Table Wizard (Alt + D + P
)
The old Pivot Table Wizard can still be accessed via keyboard shortcut.
Steps:
- Press Alt + D + P to open the wizard.
- Choose Multiple consolidation ranges.
- Select your pivot table’s cell range.
- Build your second pivot table.
Pros: Keeps everything dynamic if you choose to reference the original pivot table range.
Cons: Limited field naming and formatting control.
Method 3 – Use a Named Range with OFFSET
If your pivot table grows or shrinks, you can make a dynamic named range that always covers its output.
Steps:
- Select the pivot table output range and define it as a named range (Formulas → Name Manager).
- Use a formula like:
=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$3:$3))
- Create a new pivot table using this named range as the source.
When to use it:
- Your pivot table data changes often.
- You want your second pivot to update automatically.
Method 4 – Power Query for Advanced Pivoting
For more complex needs, Power Query lets you load the pivot table’s results as a table, transform it, and create a new pivot table.
Steps:
- Select your pivot table and Copy → Paste Values into a new sheet.
- Load this pasted table into Power Query (Data → Get & Transform).
- In Power Query, reshape the data as needed.
- Load it back into Excel and create a new pivot table.
Pros: Automates refresh and transformation.
Cons: Requires a bit more setup.
Data Cubes – A Smarter Alternative
A data cube is a way of storing your data in multiple dimensions — for example, by Project, Month, Cost Type, and Region all at once.
Instead of being locked to a single flat table, data cubes let you:
- Slice and dice across dimensions instantly
- Drill down for detail or roll up for summaries
- Create multiple pivots from the same source without the manual copy-paste step
With a cube, pivoting a pivot table isn’t really a separate step — you just create another view on the same cube.
📌 For a deeper explanation of how cubes work and why they make reporting faster, check out our article: Introduction to Data Cubes.
Example: Financial Reporting with Multiple Pivot Layers
Imagine you have a detailed pivot table showing revenue by Project → Month. Your CFO asks for a quarterly roll-up by Region.
Instead of going back to the raw data, you can:
- Copy the existing pivot table’s output as values.
- Create a new pivot table from it with Region in rows and Quarter in columns.
- Format and present — done in minutes.
If your data were in a data cube, this step would be even faster — you’d just create a new pivot view without touching the original.
📌 For a deeper explanation of how data cubes work in financial reporting faster, check out our article: Data Cubes in Finance 2025
When PivotXL Makes This Even Easier
All of the above methods work, but they’re manual.
With PivotXL, you can:
- Store your financial data in a multi-dimensional cube format.
- Build multi-level pivots without copy-paste steps.
- Keep all pivots linked to live data.
- Automate refreshes across multiple pivot layers.
If you often need to pivot a pivot table for reporting, PivotXL removes the manual work while keeping everything in Excel.