Power Query Problems

How to Combine Failed Items from Multiple Excel Sheets Using Power Query

Adam Davis Oct 13, 2026 868 views

Question details

The user needs to consolidate checklist or inspection data from multiple Excel worksheets and extract only the rows where the item status is marked as 'Failed'.

How to Combine Failed Items from Multiple Excel Sheets Using Power Query

Product: Excel
Device & OS: not provided
Scenario: Consolidating checklist forms or testing data across multiple Excel tabs to quickly identify and isolate all failed items for reporting or follow-up.
Observed behavior: The user needs a unified table that pulls data from disparate worksheets and automatically filters for a specific text value ('Failed').

Before you start

Verify that all worksheets you plan to combine share the exact same column header names (e.g., 'Status') and data types, as mismatched headers will create separate, misaligned columns in the consolidated table.

Solution 1Recommended

Append Worksheets and Filter for Failed Items via Power Query

Import your workbook into Power Query to append the individual worksheets into a single master table, then apply a filter to isolate the specific status.

Power Query excels at consolidating repetitive data structures. By importing the entire workbook, you can combine the sheets dynamically. If your worksheet layouts differ slightly, standardizing your column headers first will prevent data appending errors.

Import the Excel Workbook

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

Select Multiple Worksheets

In the Navigator window, check the box for 'Select multiple items', check all the worksheets containing your checklist data, and click 'Transform Data' to open the Power Query Editor.

Append Queries

In the Power Query Editor, go to the 'Home' tab and click 'Append Queries' (or 'Append Queries as New'). Select the tables you wish to combine and confirm to merge them into one continuous table.

Filter the Status Column

Locate the column containing your pass/fail statuses. Click the filter dropdown arrow on the column header, uncheck 'Select All', check only the box for 'Failed', and click 'OK'.

Load the Consolidated Data

Click 'Close & Load' in the upper-left corner of the Home tab. Power Query will output a brand-new worksheet containing only the combined failed items from your selected sheets.

Append Worksheets and Filter for Failed Items via Power Query

Handling Different Worksheet Layouts: If the worksheets have significantly different layouts, you may need to import a sample workbook first to establish a transformation template before applying the append operation.

Simplify Data Consolidation

Quickly Merge and Filter Sheets Using WPS Spreadsheet

If you don't want to navigate complex Power Query environments, WPS Spreadsheet offers intuitive built-in tools to merge multiple worksheets and filter your specific status data in seconds.

1. Open Your File in WPS: Launch WPS Spreadsheet and open the workbook containing your multiple checklist sheets.
2. Merge the Worksheets: Navigate to the 'Data' tab, click 'Merge Sheet', and select 'Merge multiple worksheets into a single worksheet'. Select the sheets you want to combine.
3. Apply a Filter: On the newly created merged sheet, highlight your header row, go to the 'Data' tab, and click 'AutoFilter'.
4. Isolate Failed Items: Click the filter dropdown on your Status column, uncheck all options except 'Failed', and click 'OK' to view your results.

Dedicated 'Merge Workbooks' tool for instant data consolidationFully compatible with Microsoft Excel (.xlsx, .xls) files and formattingLightweight performance with a familiar, easy-to-use interfacePowerful AutoFilter features to isolate 'Failed' statuses effortlessly

microsoft office alternative - wps office

Frequently Asked Questions

Can I automatically update the combined list if an item status changes to 'Failed' later?

Yes. One of the primary benefits of Power Query is that it maintains a connection to the source data. Simply right-click anywhere in your resulting consolidated table and select 'Refresh' to update the list with newly failed items.

Why is Power Query creating separate columns for my appended data?

Power Query appends data based on exact column headers. Ensure that the spelling, capitalization, and trailing spaces of your headers (e.g., 'Status' vs 'status ') are completely identical across all source worksheets.

What if my checklist data is stored in multiple different workbooks instead of just tabs?

Instead of importing 'From Workbook', you can use the 'Get Data > From File > From Folder' option. This allows you to combine all Excel files within a specific folder and apply the 'Failed' filter to the entire dataset simultaneously.

Excel & Data Problems