How to Combine Monthly Excel Sheets and Filter Data with Power Query
Question details
The user wants to consolidate data from 12 monthly Excel sheets or external workbooks, filter participants by specific outcome values, and extract specific fields like names and advisors.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Consolidating scattered monthly reports into a single, dynamically refreshing master table while filtering out irrelevant data.
- Observed behavior
- The user needs a reliable method to combine the data into one sheet and maintain a live link so the combined table automatically updates when source sheets or workbooks are modified.
Ensure all your monthly Excel sheets or external workbooks share the exact same column headers and data structures. If you are combining multiple files, store all source workbooks in a single dedicated folder.
Combine Multiple External Workbooks from a Folder
Ideal if each month's data is saved as a separate Excel file in a specific directory. Power Query will merge them and auto-include new files added to the folder.
By pointing Power Query to a specific folder, you eliminate the need to manually append new monthly reports. Any new file dropped into the folder will be included upon the next data refresh.
Open a new Excel workbook. Go to the 'Data' tab, select 'Get Data', choose 'From File', and then click 'From Folder'.
Browse to the folder containing your 12 monthly workbooks and click 'Open'. In the preview window that appears, click the 'Combine' dropdown at the bottom and select 'Combine & Transform Data'.
Once the Power Query Editor opens, locate the 'Outcome' column. Click the filter dropdown arrow on the column header and select only the required outcome values you wish to keep.
Hold down the Ctrl key and select the columns you need (e.g., first name, surname, advisor). Right-click one of the selected headers and choose 'Remove Other Columns'.
Click 'Close & Load' in the Home tab. To ensure live updates, right-click the newly loaded table, select 'Table Design' > 'Properties' (or click 'Connection Properties'), and check 'Refresh data when opening the file'.
Combine Multiple Monthly Sheets from the Same Workbook
Best for scenarios where all 12 monthly reports are stored as separate worksheets within the exact same Excel file.
Manage and Consolidate Your Spreadsheets Easily with WPS Office
While Power Query is a specific Microsoft Excel add-in, WPS Office provides a lightweight, highly compatible, and free alternative for all your spreadsheet tasks. With familiar interfaces and robust data processing tools like Data Consolidation, WPS Office helps you analyze and merge monthly reports seamlessly without expensive subscriptions.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new blank spreadsheet where you want your consolidated monthly data to appear.
- 2. Access Data Consolidate: Navigate to the 'Data' tab on the top ribbon and click on the 'Consolidate' button.
- 3. Select Source Ranges: Choose your desired calculation function, click the reference box to highlight the data ranges across your monthly sheets, and click 'Add' to merge them.
Frequently Asked Questions
How do I automatically refresh combined data when a new monthly sheet is added?
If you combined files from a folder using Power Query, simply save the new monthly workbook into that exact folder. Then, open your master workbook, navigate to the Data tab, and click 'Refresh All'. Power Query will automatically detect and append the new file's data.
Can Power Query handle monthly sheets with different column headers?
Yes, but it may cause misalignment. By default, Power Query appends data based on matching column headers. If headers differ slightly (e.g., 'First Name' vs 'FirstName'), Power Query will create separate columns, filling the empty gaps with 'null' values. It is highly recommended to standardize your headers before combining.
How do I filter out blanks or errors in the combined Power Query table?
Inside the Power Query Editor, click the dropdown arrow on the header of the column containing blanks or errors. Uncheck '(null)' or '(blank)' to remove empty rows. To strip out errors, right-click the column header and select 'Remove Errors'.
Does combining large monthly files slow down the Excel workbook?
Power Query is highly optimized and handles large datasets much better than standard Excel formulas like VLOOKUP. However, to keep performance snappy, only load the columns you actually need by deleting unnecessary columns in the Power Query Editor before clicking 'Close & Load'.
