Can You Power Pivot Two Excel Sheets? How to Do It

Charts & pivot tables · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, turn each sheet’s data range into an Excel table, add both tables to the Data Model, create a relationship between their matching key columns, and build a PivotTable from that model. You can then summarize fields from both sheets in one report; if the sheets contain the same kind of records instead, appending them is usually the better approach.

What Power Pivot does with two sheets

Power Pivot is Excel’s data-modeling feature. It lets a workbook hold tables as connected data sources, rather than requiring all fields used in a report to sit in one flat worksheet. A PivotTable can then combine fields from related tables, such as a product name from one table and sales totals from another.

The key distinction is that Power Pivot does not create a relationship between worksheet tabs themselves. It works with tables in the workbook’s Data Model. A worksheet may contain one or more tables, and a table can be added to the model without changing the visible arrangement of the sheet. The model stores data and relationships separately from the worksheet grid.

For example, imagine an Orders sheet with OrderID, ProductID, Date, and Quantity, and a Products sheet with ProductID, ProductName, and Category. A relationship from Products[ProductID] to Orders[ProductID] lets a PivotTable display product categories and total quantities from the order records. The shared ProductID, not the fact that the data sits in two sheets, is what makes the combination possible.

Decide whether to relate or append the data

Before building the model, determine what the two sheets represent. Relationships are appropriate when each table describes a different but connected thing: orders and products, transactions and customers, or hours and employees. Appending is appropriate when both sheets contain the same type of record, such as January sales and February sales with the same columns.

  • Relate tables: Keep separate entities separate and connect them with a common identifier. A product lookup table can provide names and categories for many order rows.
  • Append rows: Stack similar records into one table. Two monthly transaction lists with matching fields are usually one transaction table with a Month or Date column.
  • Merge columns: If you need a single flat output, join one table’s fields onto another by a matching key. Power Query is often suitable for this, but a Data Model relationship is more flexible for PivotTables that use multiple related tables.

Putting two sheets side by side is not a reliable substitute for a relationship. Nor does having similarly named columns automatically mean the tables are correctly connected. Decide the intended structure first; otherwise, a PivotTable can show duplicated totals, blank categories, or results that look plausible but are wrong.

Prepare each sheet as a clean Excel table

Power Pivot works best when each source is a rectangular table with one header row, one record per row, and no blank rows or merged cells inside the data. Headers should be unique and descriptive. Avoid subtotals within the source range because PivotTables calculate summaries themselves.

On each sheet, click inside the data and choose Insert > Table, confirm that the range is correct, and select My table has headers if the first row contains field names. You can also use Ctrl+T on Windows. Give each table a clear name from the Table Design tab’s Table Name box, such as Orders and Products. Table names cannot contain spaces.

Check the key column before loading the tables. In the example, every ProductID in Products should identify one product, while the same ID may appear many times in Orders. Values on both sides should have compatible data types and consistent formatting. A numeric ID stored as text in one table and as a number in the other can prevent a usable relationship. Remove accidental spaces and resolve missing or inconsistent identifiers rather than relying on Excel to guess what they mean.

Add both tables to the Data Model

In Excel for Microsoft 365 on Windows, select a cell in the first table and choose Power Pivot > Add to Data Model. Repeat for the second table. Depending on the workbook and Excel build, you may also see an option to add a table to the model while creating a PivotTable. The important result is that both tables appear in the Data Model.

If the Power Pivot tab is missing, check whether the add-in is available and enabled: choose File > Options > Add-ins, select COM Add-ins in the Manage box, choose Go, and look for Microsoft Power Pivot for Excel. Availability can depend on the Excel license or edition, so the add-in may not appear in every installation. Excel for the web and Excel for Mac do not provide the same Power Pivot model-authoring tools as Excel for Windows; use the supported Windows desktop version to create or manage this model.

Adding a table to the Data Model does not normally remove it from its worksheet. You can continue to edit the source table there, and refresh the model or PivotTable after changes. If the source data is an ordinary range rather than a named Excel table, converting it first makes the source easier to maintain as rows are added.

Create the relationship between the tables

Excel may detect some relationships, but check them instead of assuming they are correct. In the Power Pivot window, use Diagram View to inspect the tables and connect the matching fields, or use Power Pivot > Manage Relationships > New. Select the lookup table and its unique key on one side, then the detail table and matching foreign key on the other. In the example, Products[ProductID] is the unique side and Orders[ProductID] is the repeating side.

This is a one-to-many relationship: one product can appear in many order rows. The column on the “one” side must contain unique values, and the linked columns need compatible data types. A relationship does not mean Excel will repair mismatched IDs; unmatched order IDs simply have no corresponding product record to describe them.

Many-to-many situations need extra care. If both tables contain repeated values for the proposed key, do not assume a direct relationship will produce the intended calculation. Often the model needs a separate lookup or bridge table containing one row per key, with relationships from that table to each of the other tables. That structure clarifies how filters should flow and helps avoid inflated totals.

Build a PivotTable from the model

Once both tables and their relationship are in place, create a PivotTable using Insert > PivotTable and choose the workbook Data Model as the source, or use the PivotTable command in the Power Pivot window. The precise wording can vary slightly by Excel build. In the field list, fields are grouped by table, so you can drag a field from each table into the same report.

For the Orders and Products example, put Category from Products in Rows and Quantity from Orders in Values. Excel will summarize Quantity, commonly as a sum when the field contains numbers. You could add Date from Orders to Columns or Filters to examine quantities by period. This produces one PivotTable that uses descriptive fields from Products and transaction data from Orders.

Before trusting a result, compare a small sample with the source rows. Confirm that the Values area uses the intended aggregation, that the relationship points to the right key, and that categories or records are not unexpectedly missing. A PivotTable can be structurally valid while reflecting an incorrect key or an unintended sum, so a simple reconciliation is useful.

Common problems and how to diagnose them

One frequent issue is a relationship that cannot be created. Check for duplicate values in the lookup key, incompatible data types, and blank or malformed keys. If a product ID should be unique but appears twice in Products, decide which record is authoritative and correct the source before establishing the relationship.

Another common symptom is a blank item in the PivotTable. This can happen when detail rows contain keys that are absent from the lookup table, or when the key values differ because of spelling, spaces, or formatting. Filter the source tables for missing values and compare the actual key values; changing a cell’s display format alone may not change its underlying value.

Totals that are much larger than expected can point to duplicate lookup rows, an incorrect relationship, or a field summarized with the wrong operation. For instance, summing a price column is not the same as calculating a weighted sales amount. Verify the business meaning of every field and check a few results against manually inspected records.

  • If new source rows are not included, confirm they were added inside the Excel table or that the table expanded, then refresh the PivotTable.
  • If a table is absent from the field list, verify it was added to the Data Model and refresh or reopen the field list.
  • If the PivotTable repeats labels or shows unexpected blanks, inspect relationship keys and lookup-table uniqueness.
  • If the model seems too large or slow, remove unnecessary columns and rows from the model’s sources and avoid loading duplicate copies of data without a reason.

Refreshes, updates, and ongoing maintenance

When source values change, refresh the PivotTable so it reflects the updated model. Use PivotTable Analyze > Refresh, or choose Data > Refresh All to refresh workbook connections and model-based reports. If rows are added to a properly defined Excel table, the table can expand to include them; refresh after the update. If a source is a fixed range or comes from an external connection, check that the source definition includes the new records.

For a report that will be reused, document the key columns and the expected relationship, and keep stable table names. If someone replaces a source sheet or changes a header, confirm that the model still points to the intended data. Treat the Data Model as part of the workbook’s structure, not as a one-time operation: source cleanup and refresh habits determine whether the report stays dependable.

How this differs from a regular PivotTable

A regular PivotTable can summarize a single flat source range or table. If all fields are already in one clean table, that is usually the simplest option; Power Pivot is not required just because the workbook has multiple tabs. A PivotTable built from the Data Model is useful when the report needs fields from multiple related tables or when the model needs to handle a larger, more structured set of data.

Power Query and Power Pivot also solve different parts of the workflow. Power Query imports, cleans, reshapes, appends, and merges data. The Data Model stores tables and relationships for analysis, and the PivotTable presents summaries. They can be used together: use Power Query to clean or combine source data, load the result into the Data Model, and then report with a PivotTable.

Frequently Asked Questions

Can I create one PivotTable using data from two sheets?
Yes. Add both Excel tables to the Data Model, create a valid relationship between them, and build the PivotTable from that model. The tables need a meaningful common key; merely placing data on separate sheets is not enough.

Do the two sheets need the same columns?
No, not when you are relating different kinds of data. Orders and Products can have different fields, joined by ProductID. If the sheets contain the same kind of records and matching columns, append them into one table instead.

Can Power Pivot combine sheets without a matching column?
Not as a meaningful relationship between the tables. You need a shared identifier or another deliberate data-model design, such as a bridge table. Without a valid connection, fields from both tables cannot reliably describe the same records.

Can Excel for Mac or Excel for the web create Power Pivot relationships?
The Power Pivot model-authoring experience described here is for Excel on Windows. Mac and web versions do not offer the same tools for creating and managing the Data Model, so use a supported Windows desktop installation for this workflow.

Should I use Power Query instead of Power Pivot?
Use Power Query when the main task is cleaning, reshaping, appending, or merging data. Use Power Pivot relationships when you want to keep distinct tables connected for analysis in one PivotTable. Many workbooks benefit from using Power Query for preparation and the Data Model for reporting.

Related

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.