OmniDocPivot Table of Pivot Tables: Summarize Summary Data
Learn how to build a pivot table of pivot tables in Excel by flattening a summary, rebuilding a second level, and checking totals so the numbers match.
You have built a pivot table that totals sales by region, and now you need another layer: totals by division, where each division contains several regions. Or you have a monthly summary and want a yearly view built from it. This is a common point where people search for an excel pivot table of pivot tables, because the obvious move, pointing a new pivot at the existing one, does not work.
The good news is that you rarely need to fight Excel to get the answer. You have three workable routes: flatten the first summary and pivot the flat copy, pull specific numbers into a fixed layout with formulas, or go back to the original data and add the grouping level there. Each route fits a different situation, and the right choice depends on whether your source data still changes and whether your second-level report must update on its own.
This guide explains which route to use, how to flatten a pivot output step by step, and how to check that your second-level totals are correct.
How do you pivot a pivot table in Excel? The short answer
Excel does not let you select a pivot table as the source of another pivot table, so you either build the second summary from the original data or from a flat copy of the first summary's values. Choose the route that matches how your data and report will change.
| Your situation | What to do | Why |
|---|---|---|
| You still have the original data and need a different grouping | Add the extra grouping field to one pivot, or build a second pivot from the source | The numbers stay linked to the source and refresh with it |
| You only have the first summary, and the source is gone or locked | Paste the summary as values, clean it, and build a new pivot from that range | Excel can pivot a flat range, but not a pivot's live layout |
| You need a fixed report with a few specific totals | Use GETPIVOTDATA formulas that reference the first pivot | Formulas target named fields and items, so they keep working if rows move |
| The summary includes averages | Rebuild averages from sums and counts | Averaging averages gives wrong results when groups have different sizes |
| You want the second level to update from several related tables | Add the tables to the Data Model and define measures | Relationships let one report draw on several tables without copying |
Before you start
Check these items before you change anything. Each one prevents a common error in the second-level summary.
- Confirm the source is a clean table. Each column needs one header in one row, with no blank columns inside the data. Stray headers become fake categories in the new pivot.
- Decide which aggregation the second level needs. Totals and counts combine cleanly across levels. Averages and percentages do not, so plan to rebuild them from their parts.
- Keep a copy of the workbook. Pasting values overwrites formulas and layout in the target area. A backup lets you compare results if something goes wrong.
- Note the layout of the first pivot. Subtotals, grand totals, and compact layout add rows and labels that you must remove before building the second pivot.
- Decide whether the result must update automatically. A pasted copy is a snapshot. If the source changes often, prefer grouping the original data or using formulas.
Flattening the first summary and building the second pivot, step by step
Use this method when you only have the first pivot's output, or when you want a second-level pivot built on a clean copy of it. The steps assume the first pivot shows the categories you want to group.
- Refresh the first pivot. Update it from the source so the values reflect current data. Copying stale numbers gives you a second level that matches an out-of-date first level.
- Change the first pivot to tabular layout with subtotals off. This gives each row a visible value in every label column, which makes the copy easier to clean.
- Select the pivot's output range and copy it. Include the header row and the row labels, but exclude any filter buttons or report titles above the table.
- Paste as values on a new sheet. Values-only paste removes the pivot link, so the copy will not change when you refresh or rearrange the original pivot.
- Remove grand totals, blank rows, and repeated subtotal labels. Leftover totals will be counted again in the second pivot, which inflates every number.
- Fill down the category labels. In tabular layout, a group name often appears only on its first row. Fill it down so every row has a complete label for the second-level grouping.
- Create a new pivot table from the cleaned range. Put it on another sheet, then place the higher-level category in rows and the numeric fields in values, using Sum for totals and counts.
- Compare the grand total with the original pivot. If the two totals match, the copy and grouping are correct.
Check the result
Once the second-level pivot is built, run these checks. Most problems come from leftover totals or from averages that were combined incorrectly.
| Check | How to see it | If it's wrong |
|---|---|---|
| Grand total matches the original | Compare the total in the new pivot with the total in the first pivot | Look for subtotal or total rows that were left in the flat range and remove them |
| Individual groups match the source | Filter one category in the new pivot and compare its rows with the source data | Refresh the original pivot and paste the values again, since the copy is static |
| No averages were combined | Check which summary function each value field uses | Rebuild the average as total divided by count, using sums from the flat range |
| Every row has a complete label | Scan the label column for blank cells | Fill down the missing labels, then refresh the new pivot |
| Numbers did not double | Look for the same category appearing twice | Check for duplicate rows from pasting the copy into the same range twice |
❓ FAQ
1. Can a pivot table use another pivot table as its source?
No. Excel requires a range or a table of data as the source, and a pivot's layout is not a clean range. Build the second summary from the original data or from a values-only copy of the first pivot's output.
2. Why does my second-level pivot show a total that is too high?
Subtotal and grand total rows from the first pivot were probably left in the copied range. Excel counted them as ordinary data. Remove those rows, rebuild the new pivot, and compare its total with the original.
3. Will the second-level summary update when the source data changes?
Not if you pasted values. A values copy is a snapshot. To keep it current, refresh the original pivot and paste again, or group the source data directly so the pivot refreshes with it.
4. Can I build a summary of a summary with formulas instead?
Yes, for a fixed report. GETPIVOTDATA reads a value from a pivot by its field and item names, so you can arrange totals in any grid. The formulas stay correct when the pivot rows move, but they will not add new categories automatically.
📝 Summary
A pivot table cannot be the source of another pivot table, so build the second level from the original data or from a clean values-only copy of the first summary. Remove totals, fill down labels, and rebuild averages from sums and counts. Confirm that the grand total matches the original before you use the new summary.
