How to Easily Create a Pivot Table from Multiple Excel Sheets

This detailed example walks through the necessary procedures to combine and analyze information spread across multiple Excel worksheets.

Step 1: Preparing the Dispersed Source Data

For this demonstration, we will assume we are tracking sales figures across two separate reporting periods stored in two distinct sheets titled week1 and week2. It is crucial that the structure—specifically the column headers and data types—is identical across all sheets intended for consolidation.

The data in Week1 is structured as follows, containing columns for Store, Product, and Sales:

Similarly, the data in Week2 maintains the exact same structure to ensure seamless integration:

Our objective is to create a single analytical pivot table that summarizes the total sales figures from both the week1 and week2 datasets simultaneously.

Step 2: Implementing Data Consolidation into a Single Range

Before the pivot table can be generated, all data must reside within one continuous range on a single sheet. This single range will serve as the definitive source for the pivot table. We will use the week1 sheet as our primary consolidation location, appending the data from week2 directly beneath the data from week1.

We utilize the powerful FILTER formula, a dynamic array function, to efficiently pull the required data from the secondary sheet. This approach is highly recommended as it automatically updates the consolidated range if the source data in week2 changes.

The formula is constructed to select specific columns and ensure that only rows containing actual data are returned, preventing blank entries from disrupting the consolidated dataset. The formula syntax required for this specific consolidation task is detailed below:

=FILTER(week2!A2:C11, week2!A2:A11<>"")

Step 3: Applying the Dynamic Array Formula

To execute the consolidation, select the first empty cell directly beneath the final row of the week1 data. In our current example, this means typing the formula into cell A12 of the week1 sheet. The use of a dynamic array function means that entering the formula into this single cell will automatically “spill” the results across the required number of rows and columns, creating the unified dataset.

This specific implementation of the FILTER formula instructs Excel to look at the data range A2:C11 on the week2 sheet and return only the rows where the corresponding entry in column A (the criterion range A2:A11) is not blank ( “”). This effectively excludes headers and any empty rows, ensuring a clean consolidation.

Upon successful execution, all rows from both the week1 and week2 sheets will be combined into a single, continuous dataset spanning cells A1 through C21 on the week1 sheet, now ready to be used as the source for the analytical tool.

Step 4: Initiating the Pivot Table Wizard

With the data now consolidated, the pivot table creation process is identical to that of a single-sheet dataset. First, ensure any cell within the newly consolidated range (A1:C21) is selected. Navigate to the Insert tab on the Excel ribbon and click the PivotTable button.

A new dialog box, Create PivotTable, will appear. Since the range was preselected, the table range input should automatically display the correct range (e.g., Sheet1!$A$1:$C$21). Confirm that the Choose the data that you want to analyze selection correctly references the entire consolidated range. Then, select where you want the pivot table to be placed—typically New Worksheet is the safest option—and click OK.

Step 5: Configuring the PivotTable Fields for Analysis

Once the pivot table structure is initialized on the new sheet, the PivotTable Fields panel will appear on the right side of the screen. This panel allows you to define how the consolidated data should be aggregated and displayed. Effective field placement is critical for meaningful analysis.

For our sales summary, we require the following configuration to cross-tabulate sales by Store and Product:

• Drag Store into the Rows box to display each store name vertically.
• Drag Product into the Columns box to display each product category horizontally.
• Drag Sales into the Values box. By default, Excel will perform a Sum of Sales calculation, which is suitable for aggregating the weekly totals.

Step 6: Reviewing the Final Consolidated Pivot Table

As soon as the fields are dropped into their respective areas, the pivot table will automatically populate and calculate the aggregated results. This final view presents a comprehensive, two-week summary of sales performance, synthesized from the disparate source worksheets.

The resulting table provides an immediate overview of total sales per store, broken down by product, encompassing all transactions recorded in both the week1 and week2 source sheets. This dynamic consolidation method ensures that future updates to the source data will automatically flow into the consolidated range and can be refreshed in the pivot table with a single click.

Conclusion: Benefits of Dynamic Consolidation

The technique of using dynamic array formulas like FILTER for data consolidation represents a significant advancement over older, static methods. By consolidating the data dynamically, analysts ensure accuracy and save significant time when dealing with continually updating multi-sheet reports. This approach keeps the source data intact while providing a unified view necessary for powerful summary tools like the pivot table.