How do I check the pivot is correct after merging?

Compare its grand total with the total of the original detail table and confirm the merged table has the same number of rows as the detail table. Any difference points to duplicated keys or unmatched rows.

How do I show unmatched rows in the pivot?

Return a label such as Unknown from the lookup instead of an error. It becomes its own row, so you can see how much value has no match.

Can a pivot table use two tables without merging them?

Yes, if you add both to the Excel Data Model and create a relationship, then tick “Add this data to the Data Model” when inserting the pivot.

Why is my pivot total higher than the source total?

The merge duplicated rows, usually because the lookup table has repeated keys, or a header-level value was copied onto every line.

Should I merge in Power Query or with XLOOKUP?

XLOOKUP is fine for a one-off. Power Query is better when the files refresh every month, because the merge and pivot update together.

Which table should I add columns to?

The most detailed one, such as order lines. Adding detail to a summary table is what multiplies rows.

How to merge two tables and summarise them with a pivot

By the Analistable team · Updated 2026-10-10 · 5 min read

Bring the descriptive columns (region, category, rep) onto the detail table with a lookup or join, then build the pivot on that merged table. Always add attributes to the most detailed table, never the other way round, and check that the lookup table has one row per key. If it doesn't, totals get double counted. Compare the pivot's grand total with the source total before using it.

Part of our guide: How to answer questions across multiple spreadsheets

The end-to-end flow

A typical question — “sales by region and category” — needs columns from two places: the amount lives on order lines, the region lives on the customer table. A pivot table can only group by columns it can see, so you merge first and pivot second. The risk is in the merge: join the wrong way and some amounts appear twice.

Order lines (detail table)
Order	Customer	Category	Amount
1001	C-01	Lighting	120
1001	C-01	Furniture	480
1002	C-02	Lighting	75
1003	C-03	Furniture	900
1004	C-01	Lighting	60
Total			1635

Customers (lookup table, one row per customer)
Customer	Region
C-01	North
C-02	South
C-03	North

Step 1: merge onto the detail table

Add Region to each order line. In Excel or Google Sheets:

=XLOOKUP(B2, Customers!A:A, Customers!B:B, "Unknown")

Use “Unknown” rather than an error for missing customers, so they show up as their own row in the pivot instead of breaking it. Before you merge, confirm the lookup table is unique on the key: =COUNTIF(Customers!A:A, A2) should never exceed 1. XLOOKUP would silently take the first duplicate; a SQL join or Power Query merge would duplicate the order line.

The basics of the lookup step are in joining two spreadsheets on a common column.

Step 2: pivot the merged table

Turn the merged range into a table (Ctrl+T) so new rows are included on refresh.
Insert → PivotTable. Put Region in Rows, Category in Columns, Amount in Values (Sum).
Compare the grand total with the source total: 1,635 here.

Pivot: sales by region and category
Region	Furniture	Lighting	Total
North	1380	180	1560
South	0	75	75
Grand total	1380	255	1635

In Microsoft 365, =PIVOTBY(Region, Category, Amount, SUM) produces the same grid as a formula, where your version supports it. Google Sheets pivot tables work the same way from Insert → Pivot table.

Where double counting comes from

The classic mistake is adding a header-level number to line-level rows. Suppose the orders header table has a Shipping charge per order and you look it up onto each line. Order 1001 has two lines, so its £15 shipping appears twice and the pivot overstates shipping.

Header value copied to lines (wrong)
Order	Line amount	Shipping (looked up)
1001	120	15
1001	480	15
1002	75	5
Sum	675	35

True shipping for the two orders is £20, not £35. The fixes:

Summarise header-level values from the header table, then combine the two summaries.
Or allocate the header value across lines (for example by line share of the order) so the parts add back to the whole.
Or use the Excel Data Model, which relates tables instead of copying values; see Excel Data Model relationships.

The same thing in SQL

SELECT COALESCE(c.region, 'Unknown') AS region,
       l.category,
       SUM(l.amount) AS sales
FROM order_lines l
LEFT JOIN customers c ON c.customer = l.customer
GROUP BY ALL
ORDER BY region, category;

GROUP BY ALL is DuckDB shorthand for grouping by every non-aggregated column. To pivot categories into columns, DuckDB has a PIVOT statement; most people keep the long format and let the chart or table do the layout. Analistable runs this kind of join-then-aggregate query when you ask “sales by region and category” across an orders sheet and a customers sheet, and shows the SQL so you can see how the tables were joined.

When the source data sits on many sheets rather than two related tables, see creating a pivot table from multiple sheets. For the broader approach, the answer questions across spreadsheets guide covers more patterns.

Checklist before you share the pivot

Lookup table unique on the key.
Merged table has the same row count as the detail table.
Pivot grand total equals the detail total.
An “Unknown” row exists only if you expect unmatched keys, and you know how many.
No header-level amounts summed at line level.

Refreshing next month

The merge-then-pivot setup should survive new data without rebuilding. Keep the order lines and customers as Excel tables so formulas and the pivot extend automatically, and paste each month's export into the bottom of the order lines table. Right-click the pivot and choose Refresh. New customers who aren't yet in the customers table show as Unknown, which is your prompt to add them.

If the files are replaced rather than appended each month, load both through Power Query, do the merge there, and load the result to the Data Model or a table. Refresh All then reruns the merge and the pivot together, and the checks in the list above take a minute.

Frequently asked questions

How do I check the pivot is correct after merging?: Compare its grand total with the total of the original detail table and confirm the merged table has the same number of rows as the detail table. Any difference points to duplicated keys or unmatched rows.
How do I show unmatched rows in the pivot?: Return a label such as Unknown from the lookup instead of an error. It becomes its own row, so you can see how much value has no match.
Can a pivot table use two tables without merging them?: Yes, if you add both to the Excel Data Model and create a relationship, then tick “Add this data to the Data Model” when inserting the pivot.
Why is my pivot total higher than the source total?: The merge duplicated rows, usually because the lookup table has repeated keys, or a header-level value was copied onto every line.
Should I merge in Power Query or with XLOOKUP?: XLOOKUP is fine for a one-off. Power Query is better when the files refresh every month, because the merge and pivot update together.
Which table should I add columns to?: The most detailed one, such as order lines. Adding detail to a summary table is what multiplies rows.