Excel Pivot Table Using Two Data Tables: How It Works

Yes. In Excel for Microsoft 365 on Windows, add both Excel tables to the Data Model, create a relationship between their matching key columns, then build a PivotTable from the model using fields from both tables. This works especially well when one table contains transactions and the other contains descriptive details such as customer names or product categories.

How one PivotTable can use two tables

A regular PivotTable usually summarizes one source range or one Excel table. The Data Model changes that: it can hold multiple tables and connect them through relationships. The PivotTable can then combine fields across those tables without first copying every detail column into the transaction table.

For example, a Sales table might have one row per order line, with OrderDate, CustomerID, ProductID, Quantity, and Amount. A Customers table might have one row per customer, with CustomerID, CustomerName, and Region. A relationship between the two CustomerID columns lets you group sales by customer name or region even though those fields are not stored in the Sales table.

The tables remain separate in the workbook. Excel uses the relationship to work out which rows in one table match rows in the other when it calculates the PivotTable. This is different from placing two ranges side by side or choosing multiple consolidation ranges, which have substantial limitations and do not create a reusable relational model.

Choose tables that have a valid relationship

Before building the PivotTable, decide what the tables represent and how their rows match. The most straightforward setup is a one-to-many relationship: a lookup or dimension table has one row per key, while a transaction or fact table can have many rows with that same key.

One side: Customers has a single row for each CustomerID.
Many side: Sales can contain many rows for each CustomerID, such as one row for every product on every order.
Matching key: Both columns need compatible data types and values. Text IDs and numeric IDs are not interchangeable just because they look alike.

The key on the one side must be unique and should not contain blank values. If Customers has two rows for CustomerID 104, Excel cannot use it as the unique lookup side of a standard relationship. Correct the duplicate or decide which record should represent that customer before creating the relationship.

Two transaction tables are a different case. For instance, Orders and Returns may each contain many rows for the same CustomerID. Neither CustomerID column is unique, so linking Orders directly to Returns on that field is not the same clean one-to-many setup. A better model often includes a separate Customers table with one row per customer, then relates both transaction tables to it. The same principle applies to shared dates or products.

Add both Excel tables to the Data Model

Start with well-formed Excel tables rather than loose ranges. Each table should have one header row, no merged cells, and one kind of information per column. Select a cell in a range and choose Insert > Table if it is not already a table. Give each table a clear name on the Table Design tab, such as Sales and Customers.

Click inside the first table and choose Insert > PivotTable. In the Create PivotTable dialog, choose where to place the PivotTable and select Add this data to the Data Model.
To include the second table, click inside it and choose Power Pivot > Add to Data Model. If you are using the Power Pivot window, its Add to Data Model command can also add a selected Excel table.
Repeat for any other tables the analysis needs. Each table should appear as a separate table in the model, not as extra columns pasted into another table.

The Power Pivot tab may not be visible by default. In Excel for Microsoft 365 on Windows, check File > Options > Add-ins, select COM Add-ins in the Manage box, choose Go, and enable Microsoft Power Pivot for Excel if it is listed. Add-in availability can depend on the Excel edition and installation. If you cannot access these commands, Power Query can also load tables into the Data Model in supported desktop versions; check the available load options in your Excel installation.

Create the relationship between the tables

With both tables in the model, choose Data > Relationships > New. In the relationship dialog, select the table containing the repeated key as the Table, select its key column, then choose the table with the unique key as the Related Table and select its matching column. For Sales and Customers, that means Sales[CustomerID] is the repeated side and Customers[CustomerID] is the unique side. Confirm the relationship.

Excel can sometimes detect relationships automatically, but it is worth checking the result rather than assuming a suggested relationship is correct. Open Data > Relationships to review the tables and columns. A relationship connects columns; it does not merge the tables or make unmatched records disappear from their original sources.

If Excel refuses to create the relationship, check for duplicate keys on the unique side, blanks, mismatched data types, or inconsistent key values such as leading or trailing spaces. Make sure the selected columns represent the same identifier, not merely two fields that happen to contain similar-looking numbers.

Build and arrange the PivotTable

Once the model and relationship are in place, use the PivotTable connected to the Data Model. If you created it while adding the first table, its field list can show fields from the model; if needed, create another PivotTable from the Data Model using the available PivotTable insertion options. In the field list, tables are grouped separately, so expand Sales and Customers to find their columns.

Drag CustomerName or Region from Customers to Rows.
Drag Amount from Sales to Values, and check that Excel summarizes it as Sum rather than Count.
Drag OrderDate from Sales to Columns or Filters, depending on the view you want.
Use fields from other related tables, such as ProductCategory, to add another level of analysis.

For instance, the result could show total sales by customer region and product category. Excel uses each Sales row for the amount and uses the relationships to assign that row to the appropriate customer and product. If the amount field is being counted instead of summed, inspect its source data for text values or blanks; numeric-looking values stored as text are a common cause.

Understand what the totals mean

A relationship lets Excel filter and group across tables; it does not automatically decide the right business calculation. Think about the grain, or level of detail, of each table. If Sales has one row per order line, summing Amount is usually meaningful. If a table has one row per order, a value repeated on every order line in another table could be counted more than once when combined carelessly.

This matters particularly when adding two fact tables. Suppose Orders contains order totals and Returns contains return amounts. Joining both directly to a customer dimension can support separate summaries by customer, but putting fields from both fact tables into one view requires care: their rows may have different levels of detail and there may be no matching row for every order and return. Review the model design and validate totals against a known report before relying on the result.

For more controlled calculations, Excel’s Data Model supports measures created with Data Analysis Expressions (DAX) in Power Pivot. Measures are calculated for the current PivotTable filters and are often more suitable than a simple sum when a calculation must respect relationships or avoid double-counting. A basic PivotTable can be useful without writing DAX, but the model does not make every measure automatically correct.

When to combine tables another way

A Data Model is a strong choice when tables have different purposes, such as transactions and lookup details, and you want to analyze them together without repeating descriptive columns. It is also useful when several PivotTables should use the same connected data.

If two tables have the same columns and each row is another record of the same kind, you may want to append them instead. Power Query can append one query to another, placing rows from both sources into a single combined table. For example, monthly sales tables with the same fields can often be appended, then summarized in a conventional PivotTable. Appending adds rows; it is not the same as relating a customer table to sales by an ID.

If the second table contributes columns that should be physically added to each matching row, a Power Query merge may be appropriate. A merge joins tables using selected matching columns and can produce a new query result. Use it when a single flattened output is desired; use model relationships when you want separate tables and a reusable relational structure.

Copying columns manually can be acceptable for a small, one-time task, but it creates maintenance work and can lead to stale or mismatched details when source data changes. The older multiple-consolidation-ranges PivotTable option is not a substitute for relationships: it is designed for simpler consolidation and does not provide the same field-level analysis across related tables.

Common problems and how to fix them

The second table is missing from the field list: Confirm that it was added to the Data Model, not merely formatted as an Excel table. Reopen or refresh the PivotTable field list if needed.
Excel reports that the relationship cannot be created: Check that the key on the lookup side is unique, that the columns have compatible data types, and that the intended columns were selected.
Some categories or customers show blank: A fact-table key may have no matching lookup-table row, or the key values may differ because of spaces, spelling, or inconsistent formatting. Find and correct unmatched records.
Totals look too high: Check whether a value is repeated at a lower level of detail, whether a transaction appears more than once, and whether fields from two fact tables are being combined in a way that duplicates rows conceptually.
The menu names or options differ: Excel for the web and Excel for Mac do not expose every Windows Data Model and Power Pivot command in the same way. For the full Windows workflow described here, use the desktop version of Excel for Microsoft 365; check your Mac or web edition for its current Data Model support before building the workbook.

After changing source rows, refresh the PivotTable so it reflects the latest data. If the model is populated through Power Query, refresh the query as well as the PivotTable when necessary. For recurring reports, consistent table names and stable key columns make refreshing and troubleshooting much easier.

Frequently Asked Questions

Can a PivotTable use two tables without a relationship?
Not as a relational Data Model PivotTable that analyzes fields across both tables. Add both tables to the Data Model and define a relationship, or combine the data first with a suitable Power Query append or merge.

Do the matching columns need the same name?
No. They need to represent the same key and have compatible data types and matching values. Giving them the same clear name can make the model easier to understand, but the name itself does not create a relationship.

Can I relate two tables when both contain duplicate IDs?
A standard one-to-many relationship needs a unique key on one side. If both tables have repeated IDs, use a separate lookup table with one row per ID as the shared table, or reconsider whether the data should be combined another way.

Can I use two tables that have the same columns?
Yes, but if they are two batches of the same type of records, appending them in Power Query is usually more natural than relating them. Relationships are most useful when the tables describe different things and share a key.

Does the Data Model automatically prevent double-counting?
No. Relationships help Excel connect and filter tables, but they do not fix repeated values or mismatched levels of detail. Check the source grain, the fields used in the PivotTable, and totals against a trusted calculation.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.