How to Create a PivotTable from Multiple Sheets

Key Takeaways

• You can create a PivotTable from multiple sheets in the same workbook by adding your data to the Excel Data Model and defining relationships between tables.
• Each sheet's data must be formatted as a named table with at least one shared column (like an ID or invoice number) to link the tables together.
• The order in which you define relationships affects how your PivotTable calculates values, so start with the table containing the field you want in the Values area.
• Alternative methods like the Consolidate Multiple Ranges wizard and Power Query can also consolidate multiple worksheets into one PivotTable.

Once you become a PivotTable fan, you will quickly discover that your data doesn't always live on a single sheet. One of the most common challenges intermediate Excel users face is figuring out how to create a pivot table from multiple sheets when information is spread across different tabs in a workbook. This guide walks you through the Data Model approach step by step, along with tips for setting up relationships, troubleshooting common issues and exploring alternative methods for combining your data.

When to Use a PivotTable from Multiple Sheets

There are several scenarios where you may need to consolidate multiple worksheets into one PivotTable. You might have monthly sales data on separate tabs, departmental budgets split across sheets or related but distinct data sets like orders, payments and customer details. Whenever your data is logically connected but physically separated, a multi-sheet PivotTable lets you analyze everything in one place.

The Excel Data Model pivot table approach works best when your sheets share a common linking column, such as an invoice number, employee ID or product code. This shared column acts like a primary key in a database, allowing Excel to connect the tables and calculate accurately.

The table below compares three methods for combining data from multiple sheets so you can choose the right approach for your situation.

Before You Begin

Before you start building your PivotTable from multiple sheets, make sure you have the following in place:

• All data on each sheet is formatted as a named table (select your data and press CTRL + T)
• Each table includes at least one shared column that can serve as a linking field between tables
• There are no blank rows or columns within your table ranges
• Your workbook is saved in .xlsx format (the Data Model is not supported in the older .xls format)
• You are using a modern version of Excel (2013 or later, including Microsoft 365)

Create a Pivot Table from Multiple Sheets

Let's begin with the trickiest: How do I create a PivotTable when the data is spread over multiple sheets across the workbook? The steps below will demonstrate how to create PivotTables using multiple sheets as a source of data and apply to modern versions of Excel (2013 and later, including Microsoft 365).

Set Up Your Data

Make sure all your data is in Named Tables. If possible, when working with multiple sheets, it is very helpful to have one column of information in each spreadsheet that is the same. A column of information that is consistent across all of your sheets is similar to a "primary key" in a database and will reduce errors (such as if the sheets become sorted differently).

In our example, this repeating data is the "Invoice #" column and appears in all three sheets we will be working with.

Hint! When specifying your named table ranges, make sure you do not capture any empty rows or columns!

Add Tables to your Data Model

Begin creating your PivotTable by clicking anywhere in the named table on the first worksheet. In the example, we clicked on the "Orders" table. Then click Insert > PivotTable to open the Create PivotTable dialog box:

·        The Table/Range field should correctly include the Table's Name "Orders".

·        The New Worksheet is selected so the new PivotTable will be placed in new worksheet.

·        Make sure Add this data to the Data Model is checked!

·        Click OK.

A new sheet opens with an empty PivotTable and the PivotTable Fields pane open. Click the All tab in the pane to see all the tables you have named in your workbook.

Open the toggle beside each table and place the fields you want in the correct field areas. In this example, we want to see amounts made with specific payment forms by state.

Create Relationships Between Tables

We now want to tell Excel which information is the same from table to table so our PivotTable can correctly associate the data between them.

Excel may offer you a dialog in the PivotTable Fields pane with options to Auto-Detect relationships between the tables and to Create your own, which is advised.

You can also click the Relationships button in the Calculations group on the Analyze contextual tab to open the Manage Relationships dialog box.

In the Manage Relationships dialog, click the New button to specify a new relationship between your tables.

In the left pulldown menus, you will select two tables that have the same information. In the right pulldown menu, you will specify which columns are the same in those tables. In our example, "Invoice" is the same in both the "Payments" table and the "Orders" table. Start by connecting the Orders Table to the Payments Table.

Since we have three tables we want to connect, you will repeat this step to connect "Payments to Customers" and your tables will be associated. Click OK.

Hint! This step is trickier than it seems! The order of relationships is very important. If the order is incorrect, then your resulting table will not calculate correctly.

A "starting place" for how to order your relationships is to identify first what field you want in the Values Area of the Pivot Table. Then select the table that contains that field first in the Create Relationship dialog box. If your tables offer more than one field (in different tables) that could appear in the Values Area, you may have to create a duplicate workbook and define a different order of relationships.

In other words, the table containing your primary value field (the field you want to sum or count) should appear first in the relationship definition.

Because we want to Total the "Invoice Total" column in our PivotTable, we will put the "Orders" Table first. The "Manage Relationships" dialog box will look something like the below. Select a connection and click Edit to make changes or Delete to remove it.

Click Close.

Finish Your Table

Our resulting PivotTable uses fields from all three tables and gives us a clear picture of how much is sold to each state during the months that were recorded. You can edit your pivot fields to show exactly the information you need. To visualize time-based trends in your results, you can also build a date or time chart directly from your PivotTable data.

This feature adds a lot of convenience to what has been a laborious and cumbersome process in previous generations.

How to Show Different Percentage Calculations in a Pivot Table

One way to display a pivot table as a percentage of total is to CTRL + click (or right-click) on a value in the pivot table to open a context menu. After finding "Show Value As", you can select "Percentage of Grand Total" and even row and column total.

For best results, spend time playing with and understanding how Relationship Order affects the outcome. Once you are comfortable with the basics, advanced PivotTable techniques like macros and PivotCharts will help you get even more from multi-sheet data.

Troubleshooting Common Issues

Even with careful setup, you may run into a few common problems when creating a PivotTable from multiple sheets. Here are the most frequent issues and how to resolve them:

• Incorrect totals: This is most commonly caused by defining pivot table relationships between tables in the wrong order. Revisit the Manage Relationships dialog and make sure the table containing your primary value field (the one being summed or counted) is listed first.
• "Relationships between tables may be needed" message: This warning appears when Excel detects that your PivotTable uses fields from multiple tables without defined connections. Click the Create button in the warning banner or manually add relationships through the Analyze tab.
• Data Model option is grayed out: Check that your file is saved in .xlsx format and that you are using a supported version of Excel. The Data Model feature is not available in the older .xls file format or in Excel Online.
• Duplicate values appearing: Look for blank rows, extra spaces or inconsistent entries in your shared key columns. Applying conditional formatting across your key columns can help you quickly spot these discrepancies. Even a small issue (like a trailing space in an invoice number) can cause Excel to treat records as unrelated, resulting in duplicated or inflated values.

Alternative Methods for Combining Data from Multiple Sheets

The Data Model approach covered in this guide is ideal when your sheets contain related but differently structured data. However, there are two other methods worth knowing about:

1. Consolidate Multiple Ranges wizard: This legacy tool lets you pivot consolidate ranges from identically structured sheets (same row and column labels) into a single PivotTable. Access it by pressing ALT+D+P to open the classic PivotTable Wizard, then select "Multiple consolidation ranges." This method works in all versions of Excel and is a good option when your sheets share the exact same layout.
2. Power Query (Get & Transform): Power Query is the most flexible option for pulling data from multiple worksheets or even combining multiple Excel files into one PivotTable. It allows you to append, merge and clean data before building your analysis. Power Query is especially useful when you need to transform data or when you are working with external files rather than sheets within a single workbook. For more complex reporting needs, the same query concepts extend into Power BI for interactive dashboards and visualizations.

Both alternatives let you pull data from multiple worksheets without relying on the Data Model, so choose the method that best fits your data structure and Excel version.

Build Your Excel Skills with Pryor Learning

Creating a pivot table from multiple sheets is a powerful skill that opens up new possibilities for data analysis. Now that you understand how to use the Data Model, define relationships and troubleshoot common issues, practice with your own workbooks to build confidence. The more you work with relationship ordering and multi-table PivotTables, the more intuitive the process becomes.

Ready to take your Excel skills further? Pryor Learning offers hands-on Excel training courses designed for every skill level. Explore PryorPlus for unlimited access to ongoing professional development, including in-depth Excel workshops and on-demand learning.