How to combine two pivot tables in Excel
By the Analistable team · Updated · 5 min read
Excel has no command to merge two pivot tables. Instead, combine the source data (append the two tables in Power Query) and build one pivot, or add both sources to the Data Model and relate them through a shared table such as Products or Dates. If you only need the numbers next to each other, reference both pivots with GETPIVOTDATA.
Part of our guide: How to combine Excel sheets into one
Pick the approach
| Your two pivots come from… | Approach |
|---|---|
| Two tables with the same columns (2025 and 2026 sales) | Append the sources, one pivot |
| Different tables sharing a dimension (Sales and Budget by product) | Data Model with a shared dimension table |
| Anything — you just need a combined report | GETPIVOTDATA formulas |
Same columns: append the sources
Load both source tables into Power Query, add a column that says which source each row came from (for example Year), and Append them. Load the result as one pivot table and put Year in Columns. The two old pivots become two columns of one pivot. Steps: Power Query Append Queries.
Different tables: relate them through a shared dimension
Sales (Product, Amount) and Budget (Product, Target) can't be appended, but both describe products. In the Data Model:
- Create a Products table with one row per product (
=UNIQUE(VSTACK(Sales[Product], Budget[Product])), pasted as values and formatted as a table). - Add Sales, Budget and Products to the Data Model (Insert → PivotTable → Add this data to the Data Model).
- In Data → Relationships, relate Sales[Product] → Products[Product] and Budget[Product] → Products[Product].
- Build one pivot: Products[Product] in Rows, Sum of Sales[Amount] and Sum of Budget[Target] in Values.
| Product | Sum of Amount | Sum of Target |
|---|---|---|
| Chair | 1350 | 1200 |
| Desk | 3200 | 3500 |
| Lamp | 560 | 500 |
Use the Products column from the shared table, not from either fact table — otherwise one measure repeats its grand total on every row.
Just the numbers: GETPIVOTDATA
=GETPIVOTDATA("Amount", PivotSales!$A$3, "Product", A2)Type = and click a value cell in a pivot to have Excel write this formula. Use one formula per pivot next to a list of products to build a combined report that updates when the pivots refresh.
Which approach should you pick?
If you'll keep reporting on both datasets together, invest in the Data Model approach: one shared Products (or Dates) table makes every future pivot consistent. If you just need this month's figures side by side, GETPIVOTDATA is quicker. Appending is right when the two sources are really one table split by year or region.
Worked example: sales and targets
Pivot 1 summarises a Sales table by Rep; Pivot 2 summarises a Targets table by Rep. To see both side by side, add a Reps table with one row per rep to the Data Model, relate Sales[Rep] and Targets[Rep] to Reps[Rep], and build one pivot with Reps[Rep] in Rows.
| Rep | Sum of Sales | Sum of Target | Attainment |
|---|---|---|---|
| Amira | 52000 | 50000 | 104% |
| Ben | 38000 | 45000 | 84% |
| Chloe | 47000 | 40000 | 118% |
| Total | 137000 | 135000 | 101% |
Attainment is a measure, =DIVIDE(SUM(Sales[Amount]), SUM(Targets[Target])), formatted as a percentage. 137,000 ÷ 135,000 is about 101%. The rep field must come from the Reps table: if you drag Sales[Rep] into Rows instead, Sum of Target repeats 135,000 on every row, because Targets is not filtered by a column in Sales.
Without the Data Model
On a Mac or in a workbook where you can't use the Data Model, keep the two pivots and build a small summary table beside them. List the reps in a column and pull each figure with GETPIVOTDATA, or with SUMIFS on the source tables, which is easier to read and does not break when a pivot is rearranged:
=SUMIFS(Sales[Amount], Sales[Rep], A2)
=SUMIFS(Targets[Target], Targets[Rep], A2)This gives the same numbers as the Data Model pivot, but you maintain the rep list yourself. Use =SORT(UNIQUE(VSTACK(Sales[Rep], Targets[Rep]))) in Microsoft 365 to build a list that includes reps who appear in only one table.
Connect two pivots to one slicer
Sometimes you don't need one pivot, just two that filter together. Insert a slicer on one pivot, then right-click it and choose Report Connections and tick the other pivot. This only works when both pivots share the same pivot cache or the same Data Model — two pivots built from unrelated ranges can't share a slicer. Building the second pivot from the same source, or from the Data Model, fixes that.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Same total on every row | Row field comes from a table with no relationship to the value | Use the field from the shared dimension table |
| Relationship can't be created | Both columns contain duplicates | Create a dimension table with unique keys |
| Report Connections lists no pivots | Pivots use different caches | Rebuild both from the same table or the Data Model |
| GETPIVOTDATA returns #REF! | The item isn't visible in the pivot | Clear the filter, or use SUMIFS on the source |
Build the shared rep table quickly
The dimension table needs every rep exactly once. In Microsoft 365, put =SORT(UNIQUE(VSTACK(Sales[Rep], Targets[Rep]))) on a helper sheet, copy it, paste as values and press Ctrl+T. In older versions, paste both Rep columns under each other and use Data → Remove Duplicates. Add the table to the Data Model with Power Pivot → Add to Data Model (or tick Add this data to the Data Model when you create the pivot), then draw the relationships in Diagram View or with Data → Relationships. When a new rep joins, add them to this table, otherwise their sales appear under a blank row label in the pivot.
Common mistakes
- Copying one pivot's values under the other — the result no longer refreshes.
- Relating Sales directly to Targets on Rep, which fails because both columns contain repeats.
- Mixing granularity: targets by month and sales by day need a shared month column or a date table before they can be compared.
Frequently asked questions
- Can you merge two pivot tables into one?
- Not directly. Combine the source data and build one pivot, or use the Data Model to relate the sources through a shared table.
- How do I combine data from two pivot tables in one chart?
- Build one pivot from combined or related sources, then insert a PivotChart from it.
- Why does my combined pivot repeat the same total on every row?
- The row field comes from a table that isn't related to the measure's table. Use the field from the shared dimension table.