Excel Pivot Tables With Multiple Data Sources: How-To

Yes. In Excel for Microsoft 365 on Windows, you can build one PivotTable from multiple tables by adding them to the Data Model and relating them, or by using Power Query to combine them into one table first. Choose the Data Model when the sources describe related entities; append or merge with Power Query when the data should become one unified dataset.

Why a regular PivotTable may not be enough

A standard PivotTable is usually based on one table or range. If you select one sales table, Excel can summarize its fields, but it cannot automatically treat a separate product list or regional lookup table as part of that source. Creating a second PivotTable from the other range does not join the two: the PivotTables remain separate and their fields cannot normally be mixed in one report.

This is why the phrase multiple data sources can mean two different things. You might have separate tables that relate to each other, such as orders and products. Or you might have several files or worksheets containing the same kind of records, such as monthly sales exports. Those situations call for different methods:

Use the Data Model and relationships to analyze related tables without flattening them into one giant table.
Use Power Query to append tables with matching columns, or merge tables when you need to bring in matching fields.
Use a traditional PivotTable on a combined table when the dataset is small or straightforward.

Picking the method first helps avoid duplicate totals, awkward field lists, and manual copy-and-paste work.

Choose the right method for your sources

Think about what each source represents and how its rows fit together. If two sources have the same columns and each row is another transaction, they are usually candidates for an append. For example, January and February sales files might both contain OrderDate, ProductID, Quantity, and SalesAmount. Appending adds the February records beneath January’s records.

If one source describes another, a relationship or merge is more appropriate. A sales table may contain ProductID and SalesAmount, while a product table contains ProductID, ProductName, and Category. A relationship lets the PivotTable use fields from both tables. A Power Query merge instead joins selected columns into a resulting query.

Use the Data Model when you want to retain several related tables and analyze them together, particularly when the model includes a fact table, such as transactions, and lookup tables, such as products, dates, or customers. Use Power Query to standardize, filter, combine, and clean source data before analysis. These approaches can also work together: Power Query can prepare the tables, which you then load into the Data Model.

Create a PivotTable from the Data Model

For a relationship-based report, first make each source a well-structured Excel table or a clean imported table. Give each table a distinct name, use a single header row, and avoid blank header cells and merged cells. On Windows, you can select a data range and choose Insert > Table, confirming that the table has headers. Excel tables are useful because they can expand when new rows are added.

Load each table into the Data Model. One route is to select a table and choose Power Pivot > Add to Data Model, if the Power Pivot tab is available. Another is to use Power Query: choose Data > Get Data to import or connect to a source, then use Close & Load To and select Only Create Connection and Add this data to the Data Model. The exact import choices depend on the source.
Create a relationship between the tables. In Excel, choose Data > Relationships > New, then choose the two tables and the matching columns. You can also manage relationships in the Power Pivot window, if available.
Create a PivotTable from the model. Choose Insert > PivotTable and select From Data Model when that option is offered. In supported Windows installations, the Power Pivot window also provides a PivotTable creation command.
In the PivotTable Fields pane, expand the tables and drag fields into Rows, Columns, Values, or Filters. For example, place Category in Rows and SalesAmount in Values to summarize sales by product category.

When tables are loaded through Power Query, the Close & Load To dialog may not appear for every query after the first load. You can change a query’s load settings through its query options or the Queries & Connections pane. Check that the relevant tables are actually added to the model before trying to create relationships.

Understand relationships before building the report

A relationship connects matching values in two tables; it does not mean that every column in one table should be joined to every column in another. In the sales example, ProductID in the product table should identify one product per value, while ProductID can repeat across many rows in the sales table. This is the common one-to-many pattern: a lookup table on the one side, and transaction records on the many side.

The column on the one side must contain unique values. Relationship columns also need compatible data types. If one ProductID column is stored as text and the other as a number, or if values contain hidden spaces or inconsistent codes, the relationship may fail or leave records unmatched. Clean the keys in Power Query when necessary.

For a useful report, build a simple model around a shared key. For example, a Sales table could hold OrderDate, ProductID, and SalesAmount; a Products table could hold ProductID, ProductName, and Category; and a Calendar table could hold one row per date. Relate Sales to Products by ProductID and to Calendar by OrderDate. The PivotTable can then show sales by category and year without repeating product descriptions on every sales row.

Do not assume a relationship will fix inconsistent or duplicated keys. If both tables contain repeated values for the supposed lookup key, Excel may not be able to create the intended relationship. You may need to make a distinct lookup table or create a bridge table for a many-to-many business scenario. Carefully check the totals against the source records, especially after adding fields from multiple tables.

Use Power Query to append or merge data

Power Query is useful when the source files need preparation or when several sources should feed one consolidated table. To append monthly files, start with Data > Get Data to import the sources. In the Power Query Editor, use Home > Append Queries or Home > Append Queries as New. Choose the tables or queries to append. The columns are matched by their names, not by their left-to-right positions, so consistent and meaningful column names matter.

To bring descriptive fields into another query, use Home > Merge Queries or Home > Merge Queries as New. Select the matching key column in each query and choose an appropriate join kind. A left outer join, for instance, keeps every row from the first query and adds matching values from the second. Expand the merged column to select the fields you want to include. If you select the wrong key, or if the lookup side has duplicate keys, the result may contain repeated rows and inflated totals.

After shaping the query, choose Home > Close & Load or use Close & Load To to load the result to a worksheet table or the Data Model. A worksheet table is convenient for a straightforward PivotTable. Loading to the Data Model is useful when you want to keep separate related tables or work with a larger model. Power Query records transformation steps, which makes a repeatable import more dependable than manually copying new exports into a master sheet.

Refresh data and keep the report reliable

A PivotTable does not necessarily update its displayed results the moment source data changes. Refresh it with PivotTable Analyze > Refresh, or refresh workbook queries and connections with Data > Refresh All. On Windows, Alt+F5 refreshes the active PivotTable and Ctrl+Alt+F5 refreshes all data connections and PivotTables. On Mac, keyboard shortcuts can differ by keyboard and Excel version; use the ribbon commands if a shortcut is unavailable.

If a source is an Excel table, newly added rows are generally included when the PivotTable is refreshed. A fixed cell range may not expand to include new records, so converting source ranges to tables is often a better choice. For folder imports, confirm that new files follow the same structure and are placed in the folder used by the query. Refresh can also fail when a source file has moved, permissions have changed, or a connection requires updated credentials.

Before sharing a report, refresh it and check a few totals against a source extract. Confirm that the PivotTable is using the intended model or combined table, and that filters, date ranges, and unmatched keys are understood. If other people need the report, consider whether they can access the source files and refresh the connections on their own computers.

Common limitations and alternatives

The older Multiple Consolidation Ranges feature is not the same as a modern multi-table Data Model. It can summarize several ranges in a limited way, but produces generic row, column, and page fields rather than a normal field list based on a well-designed relational model. It is generally a poor fit when you need to analyze fields such as product category alongside transaction values.

Excel for Mac and Excel for the web do not provide the same Data Model and Power Pivot authoring experience as Excel for Windows. Power Query capabilities also vary by platform and connector. If you need to create a model, define relationships, or use Power Pivot features, use a supported Windows desktop edition. For a simpler cross-platform workbook, combine the sources into one clean table with Power Query where available, then create a conventional PivotTable from that table.

Another alternative is to prepare a consolidated dataset in a database or reporting platform and connect Excel to it. That can be appropriate when the data is large, refreshed centrally, or shared by a team. For ordinary workbook-scale analysis, however, Power Query plus a Data Model or a single clean table is often sufficient.

Frequently Asked Questions

Can one Excel PivotTable use two separate tables?
Yes. Add both tables to the Data Model, create a valid relationship between them, and insert a PivotTable from the model. If the tables are actually pieces of one dataset with the same columns, append them first instead.

Can I create a PivotTable from two worksheets?
Yes, but separate worksheets do not automatically become one source. Load the tables into the Data Model and relate them, or use Power Query to append or merge them. For similar monthly sheets, append is usually the relevant operation.

What is the difference between append and merge?
Append stacks rows from tables with corresponding columns, such as sales records from separate months. Merge matches rows using one or more selected keys and adds fields from another query, such as adding product names to sales records.

Why is Excel repeating or inflating my totals?
A common cause is a merge that matches one source row to multiple rows in the other source, often because the lookup key is not unique. In a Data Model, check the relationship direction and key uniqueness, then compare the PivotTable total with the original transaction total.

Do I need Power Pivot to use multiple sources?
Not always. Power Pivot is useful for managing and analyzing a Data Model, but Power Query can combine sources into one table that a regular PivotTable can summarize. The best choice depends on whether your sources should remain related tables or become one dataset.

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.