Power Query Problems

How to Combine 12 Monthly Excel Sheets into One Summary Report

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to consolidate data from twelve distinct monthly worksheets (such as payroll records) into a single master summary report in Excel.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Compiling a year-end summary or master report by merging data spread across 12 identically structured monthly tabs.
Observed behavior: The goal is to append the data dynamically and efficiently without manually copying and pasting rows from each individual monthly sheet.

Before you start

Ensure that all 12 monthly worksheets have identical column headers and structures, and that they are formatted as Excel Tables if you plan to use Power Query for the cleanest results.

Solution 1Recommended

Use Power Query to Append Worksheets

Power Query is the most robust method for consolidating multiple sheets, allowing for automated appending and easy data cleaning.

Power Query allows you to fetch data from all sheets within your current workbook or from an external file, and append them into one continuous table. This method is highly scalable and can handle massive datasets effortlessly.

Open Power Query

Navigate to the Data tab on the Excel ribbon, click on 'Get Data', select 'From File', and then choose 'From Workbook' to import your current file.

Select Multiple Items

In the Navigator window, check the box for 'Select multiple items' and check all 12 of your monthly worksheets, then click 'Transform Data'.

Append Queries

In the Power Query Editor, go to the Home tab and click 'Append Queries' (or 'Append Queries as New').

Configure Appended Tables

Select 'Three or more tables', add all your monthly tables to the 'Tables to append' box, and click OK.

Load to Summary Sheet

Click 'Close & Load' in the top left corner to output the combined dataset into a brand new summary worksheet.

Updating Data: If you update data in any of the monthly sheets, simply right-click the combined Power Query table and select 'Refresh' to update your summary report.

Solution 2

Combine Sheets Using VSTACK and FILTER Formulas

If you are using a newer version of Excel with dynamic array support, you can use VSTACK and 3-D references to stack data instantly.

Smart Data Consolidation

Merge Multiple Sheets Instantly with WPS Spreadsheet

WPS Spreadsheet features a highly intuitive, built-in 'Merge Worksheets' tool that allows you to consolidate 12 months of data into a single summary sheet in just a few clicks, bypassing the need for complex formulas or Power Query setups.

1. Open Your Workbook: Launch WPS Spreadsheet and open the workbook containing your 12 monthly sheets.
2. Access the Merge Tool: Navigate to the Data tab on the top ribbon and click the 'Merge Worksheets' button.
3. Choose Merge Type: Select the option labeled 'Merge multiple worksheets into a single worksheet' from the dropdown menu.
4. Select Source Sheets: Check the boxes next to the 12 monthly worksheets you wish to combine in the prompt window.
5. Complete the Merge: Click 'Merge'. WPS Spreadsheet will automatically extract and stack the data into a brand new summary worksheet.

Built-in one-click 'Merge Worksheets' feature for easy consolidation100% compatible with Microsoft Excel (.xlsx) file formatsSupports dynamic array formulas like VSTACK for advanced usersFree, lightweight, and fast alternative for daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Do the monthly sheets need to have the exact same columns for VSTACK to work?

Yes. When using VSTACK or 3-D references, the columns in all worksheets must be in the exact same order because the formula stacks data strictly by cell position. If your columns vary in order, Power Query is the better option as it aligns data by column headers instead.

Why are there blank rows in my VSTACK combined data?

Blank rows appear if the range you specified in the VSTACK formula (e.g., A2:G100) includes empty cells at the bottom of your monthly sheets. Wrapping the VSTACK formula inside a FILTER function allows you to exclude any rows where the primary column is empty.

Can I combine sheets from different workbooks into one summary?

Yes. While VSTACK works best within the same workbook, you can use Power Query to combine entirely different files. Go to Data > Get Data > From File > From Folder, point it to a folder containing all your monthly Excel files, and click 'Combine and Load'.

Will my summary report update automatically if I change data in a monthly sheet?

If you use the VSTACK formula, the summary report will update instantaneously when source data changes. If you use Power Query, the report does not update live; you must right-click anywhere in the summary table and select 'Refresh' to pull in the latest changes.

Excel & Data Problems