Excel Pivot Table Using Multiple Sheets: How It Works

Charts & pivot tables · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, you can build a PivotTable from multiple sheets by combining their rows with Power Query, or by adding separate tables to the Data Model and relating them. Use Power Query when sheets contain the same kind of records; use the Data Model when the tables describe different but connected things, such as sales and products.

Why a PivotTable does not automatically combine sheets

A standard PivotTable summarizes one source range or table. If a workbook has a January sheet and a February sheet, Excel does not automatically treat them as one continuous list just because the columns look alike. You need to combine the records first or tell Excel how separate tables are related.

The right method depends on what the sheets contain. If each sheet is another batch of the same records—for example, monthly transactions with the same columns—append the rows into one query and create a PivotTable from the result. If one sheet lists sales and another lists products, customers, or regions, preserve those as separate tables and connect them through shared fields in the Data Model.

  • Same columns, more records: append the sheets into one table with Power Query.
  • Different tables, related by an ID: use the Data Model and create a relationship.
  • A one-off, small dataset: copying the rows into one clean table can be simpler than maintaining a multi-sheet setup.

Method 1: Append similar sheets with Power Query

Power Query is usually the most practical choice for repeated monthly or departmental sheets that have the same fields. Appending means stacking rows from multiple tables; it does not join columns side by side. For example, append tables named JanuarySales and FebruarySales when both have columns such as Date, Store, Product, and Amount. The resulting query contains both months’ transactions, so one PivotTable can summarize the combined data.

  1. On each source sheet, make the records a proper Excel table. Include one header row, avoid blank rows inside the data, and remove subtotal rows. Select the data and choose Insert > Table; confirm that the table has headers. Give each table a clear name on the Table Design tab, such as Sales_Jan and Sales_Feb.
  2. Click inside the first table and choose Data > From Table/Range. In Power Query Editor, check that the column names and data types are sensible. Use the same process for each source table, then choose Home > Close & Load To… and select Only Create Connection. Repeat for the other tables.
  3. In Power Query Editor, choose Home > Append Queries > Append Queries as New. Select the option for two or three tables, as appropriate, and add the tables to the list. For more than three, use the option for three or more tables and select each required query.
  4. Give the appended query a meaningful name, such as AllSales. Choose Home > Close & Load To… and load it as a table, or choose to create a connection and add the data to the Data Model if that suits the workbook design.
  5. Select the loaded table and choose Insert > PivotTable. Confirm the table or query output as the source, choose where to put the PivotTable, and build the report from its fields.

Power Query matches columns by their header names rather than requiring the columns to appear in precisely the same order. If one sheet has an extra column, the appended result can include it; rows from a table without that field will have a blank value for it. That flexibility is useful, but inconsistent headers such as Amount and Sales Amount will be treated as different fields until you standardize them.

Keeping the combined PivotTable current

When you add rows to an Excel table that is already a Power Query source, the table generally expands to include them. To update the query output and PivotTable after changing source data, choose Data > Refresh All. You can also right-click the PivotTable and choose Refresh, but Refresh All is clearer when the workbook has several queries or connections.

For a new month, create a table with the same expected columns and make sure the append query includes it. Adding a new worksheet alone does not guarantee that an existing query will discover the sheet. If your process regularly creates new monthly tabs, consider a folder-based Power Query import or revise the query to select tables based on a naming convention. Folder imports are especially useful when each month arrives as a separate workbook file, but they require a consistent file and column structure.

Refresh updates the query results; it does not fix badly formatted source records. Check that dates are dates, amounts are numeric, and category labels are consistent. For example, values recorded as East, east, and E may appear as separate PivotTable items. Clean those differences in the source or in Power Query before reporting.

Method 2: Relate separate tables with the Data Model

Do not append tables that represent different types of information. Suppose a workbook has a Sales table with OrderDate, ProductID, and Units, and a Products table with ProductID, ProductName, and Category. Appending these tables would stack unrelated records and produce a confusing set of mostly blank fields. Instead, add both tables to the Data Model and relate them through ProductID. The PivotTable can then use sales measures alongside product names and categories.

  1. Format each source range as an Excel table, with one row per record and a unique, clear column header.
  2. Create a PivotTable and select the option to add the data to the Data Model, or add the tables to the model through the available Data Model tools in Excel for Windows.
  3. Choose Data > Relationships > New to connect the matching fields. For the example, relate ProductID in Sales to ProductID in Products.
  4. Build the PivotTable from the fields belonging to both tables. Put descriptive fields such as Category in Rows and numerical fields such as Units in Values.

A useful relationship normally has a unique key on one side. In this example, each ProductID should occur only once in Products, while it may occur many times in Sales. Duplicate keys in the lookup table can prevent a valid relationship or lead to unsuitable results. Check that the key columns use compatible data types too; a number stored as text in one table may not match a numeric ID in another.

The Data Model is appropriate for several related tables and can avoid copying the same product or customer details into every transaction row. It is not simply a way to make arbitrary sheets act like one list: the relationships and the meaning of each table matter. Data Model authoring and Power Pivot capabilities are primarily Windows features. Excel for Mac has different support, so if the workbook must be created and maintained on a Mac, check the available features in that Excel version; combining the records into one flat query may be the more portable option.

Choosing between Power Query and the Data Model

Think about whether your sheets contain more rows of one record type or separate kinds of entities. Power Query append is the natural fit for repeated transaction logs: North and South store orders, weekly survey responses, or monthly expenses with the same fields. The Data Model fits a fact table plus descriptive tables: orders connected to customers, products, and dates.

  • Choose append when: every source row means the same thing, the columns mostly align, and the output should be one long list.
  • Choose relationships when: tables have different purposes and share a reliable key, and you need fields from several tables in one report.
  • Choose a single consolidated table when: the data is small, rarely changes, and the additional query or model setup would add more maintenance than value.

There is also a legacy PivotTable option called Multiple Consolidation Ranges, available through Excel’s PivotTable and PivotChart Wizard in some Windows versions. It can summarize multiple ranges with a similar layout, but it does not behave like a normal PivotTable built from a clean table with meaningful source fields. It offers limited flexibility for analyzing individual categories and is often less useful than Power Query for ongoing work. It is not the same thing as adding several relational tables to the Data Model.

Prepare the source sheets before combining them

Good source structure prevents many PivotTable problems. Give each table one header row and keep each column to one type of information. A Date column should hold dates, not a mixture of dates and notes; a Quantity column should contain numbers rather than entries such as 12 units. Avoid merged cells, repeated title rows, blank separator rows, and manually inserted totals within the records.

Keep field names stable across sheets. If one tab uses Customer ID and another uses CustomerID, standardize them before appending. If a source has a column that does not exist in the other tables, decide whether the difference is meaningful or accidental. A deliberately added SourceMonth column can be helpful for filtering, and it is often clearer than relying on worksheet names to identify where a row came from.

Before building the report, confirm that each row represents one observation, order line, or other consistent unit. If a sales sheet contains both individual order lines and a monthly total row, the total row will be counted along with the detail and inflate the PivotTable results. Remove such summaries from the source data.

Common problems and practical fixes

  • A sheet is missing from the results: confirm that its table or query was included in the append, then use Data > Refresh All. A newly created worksheet may not be part of the query yet.
  • Values appear under separate field names: compare the headers. Power Query treats differently named columns as separate fields; rename them consistently and refresh.
  • Numbers appear as counts rather than sums: check whether the source values are stored as text or contain non-numeric entries. Correct the type in the source or Power Query, then refresh the PivotTable.
  • Dates group strangely or will not group: verify that every value in the date field is a real date and that there are no text notes or blanks causing trouble.
  • A relationship cannot be created: verify that the lookup-side key is unique, both key columns have compatible data types, and the fields represent the same identifier.
  • Totals are unexpectedly high: look for duplicated source rows, included subtotal lines, or a relationship that does not match the intended one-to-many structure.

When troubleshooting, inspect the combined query or source tables before changing the PivotTable layout. A PivotTable summarizes what it receives; rearranging fields cannot correct duplicate records or inconsistent source values.

Frequently Asked Questions

Can I make one PivotTable from two sheets with different columns?
Yes, if the sheets hold the same general type of records, Power Query can append them and keep the combined set of columns. Where a source lacks a column, its rows will have blanks for that field. If the sheets describe different entities, use related Data Model tables instead of appending them.

Will a PivotTable update automatically when I add a new sheet?
Usually not just because the sheet exists. The source query or Data Model must include the new data. After updating the query’s sources, choose Data > Refresh All to update the query output and PivotTable.

Can I combine monthly sheets without copying and pasting?
Yes. Format each month’s records as a table, create Power Query connections, and append the queries. Once configured, the process is easier to repeat than manually copying rows, though a newly added monthly table may need to be added to the query.

Can Excel for the web or Mac create the same setup?
Feature availability and interface options differ by platform and Excel version. This walkthrough targets Microsoft 365 on Windows. Mac users may be able to work with Power Query but should verify their version’s support, particularly for Data Model relationships; Excel for the web also does not provide every Windows authoring feature.

Should I use Multiple Consolidation Ranges?
Only for a narrow, simple summary where its limited field structure is acceptable. For a refreshable report with recognizable fields, Power Query or a properly designed Data Model is generally easier to maintain and analyze.

Build for the next refresh, not just today

The most reliable setup uses structured Excel tables, consistent headers, and a repeatable refresh process. For sheets that hold the same kind of records, append them with Power Query and make one PivotTable from the combined output. For distinct tables that share keys, use the Data Model and create valid relationships. Choosing based on the shape of the data keeps the summary accurate and makes the workbook easier to update when new records arrive.

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.