How to Create a Pivot Table from Another Pivot Table in Excel (Step-by-Step Guide)

—

by

Himanshu
in

Creating a Pivot Table from another Pivot Table in Excel can be very helpful when you want to summarize, filter, or analyze data further without returning to the raw source data. Here’s how you can do it the right way, along with best practices and real-world examples.

🧠 Why Make a Pivot Table from Another Pivot Table?

Sometimes, your original Pivot Table has too much detail, and you want to:

  • Summarize it again (e.g., monthly to yearly totals)
  • Filter it differently without changing the original
  • Build dashboards with multiple views of the same summarized data

✅ Methods to Create a Pivot Table from Another Pivot Table

🔹 Method 1: Use the Existing Pivot Table as a Data Source

⚠️ Note: This works only if the original Pivot Table was created from a data range or table, not from OLAP models or external sources.

Steps:

  1. Click anywhere inside the original Pivot Table.
  2. Press Ctrl + A to select the whole Pivot Table.
  3. Copy it using Ctrl + C.
  4. Paste it into a new location using Paste Special → Values.
  5. Select the pasted data.
  6. Go to Insert → PivotTable.
  7. Choose the pasted data as your new source.
  8. Click OK.

You now have a new Pivot Table that is based on the output of the first one, and you can summarize it however you want.

🔹 Method 2: Convert First Pivot Table to Static Data

If you want a permanent copy of the summarized data from Pivot #1:

  1. Select the Pivot Table → Right-click → Copy.
  2. Paste it as Values Only using Paste Special (Ctrl + Alt + V).
  3. Use this new static table as the source for your second Pivot Table.

🔹 Method 3: Use GetPivotData or Power Query (Advanced)

For more dynamic scenarios:

  • Use GETPIVOTDATA to extract specific values and feed them into formulas or dashboards.
  • Use Power Query to pull data from the Pivot Table range, clean it, and create a new Pivot Table.

📊 Example Scenario

Original Pivot Table

You have a monthly sales Pivot Table:

MonthSales RepSales Amount
JanRavi₹25,000
JanNeha₹30,000
FebRavi₹22,000
FebNeha₹33,000

You now want to:
👉 Create a yearly total per Sales Rep
Use the steps above to:

  • Copy & paste the first Pivot Table as values
  • Insert a new Pivot Table summarizing by Sales Rep only

🚀 Bonus Tip: Use Named Ranges for Flexibility

If you plan to reuse this method:

  • Convert the pasted values into a named range or Excel Table
  • This helps you reference it dynamically across the workbook

⚠️ Important Notes

  • The second Pivot Table won’t update automatically if you change the first one unless it’s linked via formulas or Power Query
  • Always double-check for grand totals or subtotals, which might skew your new Pivot Table

📘 Want to Learn Pivot Tables Like a Pro?

✅ Master dynamic reporting, nested PivotTables, GETPIVOTDATA, slicers, charts, and more in my course:

👉 Mastering MS Excel – A Comprehensive Training Course

Best selling products