Pivot Table Issues

How to Combine Two Pivot Tables into One Annual Pivot Table

Steve K Oct 9, 2026 871 views

Question details

The user needs a method to merge two separate period-based PivotTables (such as January-June and July-December data) into a single, consolidated annual PivotTable.

How to Combine Two PivotTables into One Annual PivotTable

Product: Excel
Device & OS: not provided
Scenario: Consolidating multiple half-year data reports into an annual summary report when dealing with massive datasets.
Observed behavior: Manually copying and pasting two massive datasets into a single list is highly inefficient and frequently crashes or exceeds the 1,048,576 row limit of standard worksheets.

Before you start

Ensure that both of your source datasets have exactly the same column headers, spelling, and data types before attempting to append them.

Solution 1Recommended

Combine PivotTables using Power Query and Data Model

This scalable method bypasses the worksheet row limit by processing data in the background and loading the combined result directly into a Data Model.

Power Query and the Excel Data Model allow you to append multiple large datasets without pasting them into a standard worksheet. This is the most efficient way to combine data that exceeds the traditional one-million-row limit.

Load data to Power Query

Open your workbook, navigate to the Data tab, and select Get Data to load your first half-year dataset and second half-year dataset into Power Query as connections.

Append the queries

In the Power Query Editor, click Append Queries as New on the Home tab. Select your January-June query as the primary table and your July-December query as the secondary table.

Group the combined data

Select your target identifier column (e.g., Customer ID), click Group By on the Transform tab, and set the operation to Sum for your specific spending or value columns.

Load to the Data Model

Click Close & Load To..., choose Only Create Connection, and make sure to check the box for Add this data to the Data Model.

Create the annual PivotTable

Go to Insert > PivotTable, select From Data Model, and build your newly consolidated annual PivotTable without worrying about row limits.

Combine PivotTables using Power Query and Data Model

Data Consistency: Matching column names and data types precisely in both initial queries guarantees a seamless append operation without generating null errors.

Free Microsoft Office alternative

Try WPS Office for Efficient Data Management

If you frequently struggle with heavy data processing, expensive subscriptions, or complex toolsets in Microsoft Office, WPS Office Spreadsheets offers a highly compatible, lightweight, and free alternative. It supports robust PivotTable creation, handles vast amounts of data smoothly, and keeps your system running quickly.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Lightweight design ensures fast loading even with large datasets.Built-in advanced PivotTable functionalities for complex data analysis.Familiar, easy-to-use tabbed interface with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I combine more than two datasets using Power Query?

Yes. In the Power Query Editor, when selecting 'Append Queries', you can choose the 'Three or more tables' option to append multiple periods (like Q1, Q2, Q3, and Q4) into a single master table simultaneously.

Why is my combined PivotTable showing duplicate Customer IDs?

This usually happens if you appended the queries but skipped the 'Group By' step in Power Query, or if there are slight spelling or spacing differences in the Customer IDs between the two datasets. Use the 'Trim' function in Power Query to remove extra spaces before grouping.

What is the maximum number of rows the Excel Data Model can handle?

Unlike standard worksheets which are limited to 1,048,576 rows, the Excel Data Model can theoretically handle hundreds of millions of rows, limited primarily by your computer's available memory (RAM).

Do I need to refresh the PivotTable if my source data changes?

Yes. If you update the original source files, you must go to the Data tab and click 'Refresh All'. This triggers Power Query to pull the new data, append it, and update the Data Model and your PivotTable.

Excel & Data Problems