Excel Pivot a Pivot Table

Excel’s PivotTable feature is famous for summarizing raw data, but a lesser-known trick is “pivoting” a pivot table itself — rearranging its structure or even building a second PivotTable on top of the first one’s output. This matters more than ever in 2026, since Microsoft 365’s newest PivotTable engine (powered by Python-in-Excel and dynamic arrays) makes multi-layer summarization far more common in finance, ops, and marketing reporting.

What “Pivoting a Pivot Table” Actually Means

People use this phrase two different ways, and confusing them causes most of the frustration you’ll find in Excel forums. It’s worth separating them before you touch a single field.

• Rearranging fields — dragging items between Rows, Columns, Values, and Filters to change how the same source data is displayed (this is what most people mean and what Microsoft calls “pivoting”).
• Nesting a PivotTable inside a PivotTable — building a second summary table from the output of the first, which requires an extra conversion step because Excel treats a PivotTable’s cache as a special object, not a plain range.

Excel’s own documentation calls the first behavior the defining trait of the tool — the word “pivot” literally refers to spinning data around an axis. The second scenario is really a workaround, since native “PivotTable of a PivotTable” chaining isn’t supported the way it is in Power BI’s model layer.

Analysts at several enterprise training firms note that roughly 70% of PivotTable rebuild requests they get are actually just field-rearrangement questions, not requests for genuine multi-level summarization.

Method 1: Rearranging Fields (True Pivoting)

This is the fast, native way to pivot, and it never leaves the original PivotTable. You’re just reshaping the same cached data.

1. Click anywhere inside the PivotTable to open the PivotTable Fields pane on the right.
2. Drag a field out of Rows and drop it into Columns (or vice versa) to flip the orientation.
3. Drag a field into Filters to turn it into a slicer-style dropdown above the table.
4. Drag a numeric field into Values and change its summary type (Sum, Average, Count, % of Total) via Value Field Settings.
5. Right-click any row or column label and choose Move to manually reorder categories without dragging fields at all.

If you find yourself flipping orientation constantly — say, comparing regions-as-rows against regions-as-columns for different stakeholders — it helps to understand the underlying mechanic covered in How to Flip Columns and Rows in Excel, since the transpose logic is conceptually similar even though PivotTables use drag-and-drop instead of Paste Special.

Quick Field Swaps Table

Method 2: Building a PivotTable From a PivotTable’s Output

This is the actual “pivot a pivot table” scenario — creating a brand-new summary from the results of an existing one. Excel blocks you from selecting a live PivotTable range as a new PivotTable’s source directly in most versions, so you need a short workaround.

1. Select the entire output range of your first PivotTable (headers included).
2. Copy it (Ctrl+C), then right-click a blank area and choose Paste Special > Values.
3. This static copy is no longer a PivotTable — it’s a normal table, so consider formatting it properly using the same principles from How to Create a Table in Excel.
4. Select the new static range and go to Insert > PivotTable to build your second-level summary.
5. Refresh source data periodically, since the static copy won’t auto-update when the original changes.

This two-step method is common in monthly finance packs where a first PivotTable aggregates transactions by account, and a second PivotTable re-summarizes those account totals by department or region for an executive rollup.

• Why it’s needed: PivotTable caches store compressed, pre-aggregated data structures, not row-level records, so a second PivotTable built directly on top would have nothing granular to re-slice.
• Common mistake: Forgetting Step 2 and building the second PivotTable straight from the live pivot — Excel usually throws a “PivotTable field name is not valid” error or silently duplicates the same layout.
• Better long-term fix: If you’re doing this regularly, load your original raw data into the Data Model and use Power Pivot measures instead of chaining pivots, since Power Pivot supports true layered aggregation without the copy-paste step.

GETPIVOTDATA: The Formula-Based Alternative

Before jumping to a second PivotTable, many advanced users reach for GETPIVOTDATA() instead, because it lets you pull specific values out of a PivotTable into a normal formula-driven sheet without disturbing the pivot’s structure.

• Type `=` and click a cell inside an existing PivotTable — Excel auto-generates the GETPIVOTDATA formula for you.
• The formula stays accurate even if you rearrange rows/columns later, unlike a plain cell reference which breaks the moment layout changes.
• You can combine multiple GETPIVOTDATA calls into a custom summary sheet that behaves like a “pivoted pivot” without ever building a second PivotTable object.
• Disable it globally via File > Options > Formulas > “Generate GetPivotData” if you’d rather use simple cell references for quick one-off checks.

This approach pairs well with cross-sheet reporting — for example, pulling PivotTable totals from a data tab into a summary tab using techniques from Excel Reference Another Sheet, which is essentially what GETPIVOTDATA does automatically under the hood, just wrapped in a friendlier function name.

Cleaning Source Data Before You Pivot Anything

A huge share of “my pivot looks wrong” complaints trace back to messy source data, not the PivotTable feature itself. Fixing this before you build (or rebuild) your table saves far more time than fiddling with field placement afterward.

• Inconsistent text entries (e.g., “NY” vs. “New York” vs. “ny”) silently create duplicate rows in your PivotTable — run a pass with Spell Check in Excel and standardize naming before summarizing.
• Combined data in one cell (like “Smith, John – Sales”) breaks grouping — use Split Text in Excel to separate it into clean columns first.
• Messy prefixes or suffixes in category names (like SKU codes with trailing spaces or extra characters) should be cleaned with the Excel Substitute Function before the data ever touches a PivotTable.
• Blank or merged header cells in your source range will cause Excel to reject fields entirely, so avoid using Merge Cells in Excel anywhere inside a table meant to feed a PivotTable.
• Broken formulas referencing shifting ranges should use Absolute Reference Excel locking (the `$A$1` style) so your source table doesn’t silently expand or contract when rows are inserted.

Microsoft’s own PivotTable troubleshooting guidance still lists “source range formatted as a proper Table object” as the single most common fix requested in support tickets, ahead of formula errors or version mismatches.

Turning a Rebuilt PivotTable Into a Visual

Once you’ve pivoted, rearranged, or layered your tables, the real payoff for most business users is visualization, not more rows of numbers. A PivotChart linked to your final PivotTable updates automatically as you continue to reshuffle fields, which is far more efficient than manually charting a static range every time layout changes.

• Insert a PivotChart directly from Insert > PivotChart while your cursor is inside the PivotTable.
• Choose chart types that tolerate frequent field swaps well — clustered bar and line charts handle row/column flips more gracefully than pie charts.
• If you rearranged fields in Method 1, the linked PivotChart reflows instantly; there’s no need to rebuild it.
• For a deeper walkthrough of chart types, filters, and formatting options specific to pivot-linked visuals, see Pivot Chart, which covers configuration details this article doesn’t repeat.

Common Layout Choices for Reporting

Conclusion

“Pivoting a pivot table” isn’t one single action — it’s really a spectrum ranging from a simple field drag-and-drop to a full two-step rebuild involving static-value copies and a second PivotTable object. For 90% of everyday reporting needs, rearranging fields inside the same PivotTable is faster, safer, and keeps your data connected to its original source. Reserve the copy-paste-and-rebuild method for genuine multi-level rollups, like turning transaction-level summaries into department-level executive reports, and consider Power Pivot or the Data Model once you’re doing this more than occasionally — it eliminates the manual conversion step entirely and scales far better as your workbooks grow past a few thousand rows.

FAQ