Power Query Problems

How to Combine Failed Test Results from Multiple Excel Worksheets

Adam Davis Oct 9, 2026 868 views

Question details

The user needs to extract and combine only the failed rows from multiple worksheets into a single summary sheet without importing entire worksheets.

How to Combine Failed Test Results from Multiple Excel Worksheets

Product: Excel
Device & OS: not provided
Scenario: Managing a workbook with multiple sheets containing test results where a consolidated view of only the failed rows is required.
Observed behavior: The user is looking for an automated or efficient method to collect specific rows across sheets into a summary table without manual copying.

Before you start

Ensure all your source worksheets share the exact same column structure and headers. It is also recommended to save your workbook before connecting to it with Power Query.

Solution 1Recommended

Use Power Query to Append and Filter Worksheets

Power Query is the most efficient way to combine data from multiple sheets dynamically and apply a filter to extract only the failed test results.

This method assumes that all your target worksheets have a consistent structure. By appending queries and filtering directly in the Power Query Editor, you avoid loading unnecessary data into your workbook.

Get Data from Workbook

Open a new blank Excel workbook or go to the Data tab in your existing workbook. Click 'Get Data' > 'From File' > 'From Workbook' and select your file.

Select Multiple Items

In the Navigator window, check the box for 'Select multiple items', choose all the relevant test result worksheets (excluding any existing summary sheets), and click 'Transform Data'.

Append Queries

In the Power Query Editor, go to the Home tab and click 'Append Queries'. Select the tables you want to combine into a single master table.

Filter for Failed Results

Locate the column containing the test outcomes (e.g., 'Status' or 'Result'). Click the filter dropdown arrow on the column header, uncheck all options except 'Fail', and click OK.

Load to Summary Sheet

Click 'Close & Load To...' on the Home tab. Choose 'Table' and select 'Existing Worksheet' or 'New Worksheet' to load the consolidated failed results.

Use Power Query to Append and Filter Worksheets

Exclude Summary Sheets: If you are querying the same workbook where the summary sheet resides, make sure to filter out the summary sheet's name in the initial Power Query source step to prevent an infinite data loop.

Manage Data Seamlessly

Consolidate Multiple Worksheets Using WPS Spreadsheet

WPS Office offers powerful data handling capabilities, allowing you to manage, filter, and combine datasets across multiple worksheets easily. It provides a familiar interface that is fully compatible with Microsoft Excel files.

1. Open Your Workbook: Launch WPS Spreadsheet and open the workbook containing your test results across multiple sheets.
2. Use Data Consolidation: Navigate to the Data tab and select 'Consolidate' to merge data from different sheets based on your specific cell ranges.
3. Apply Filters: Select your merged dataset, click 'AutoFilter' under the Data tab, and filter the status column to show only 'Fail'.
4. Extract to Summary: Copy the filtered results and paste them into your designated summary worksheet for reporting.

Fully compatible with Microsoft Excel (.xlsx, .xls) formats.Lightweight software with fast processing for large data consolidations.Built-in advanced filtering and data consolidation tools for test results.Free and highly intuitive interface with no steep learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I automatically update the summary sheet when new failed tests are added?

Yes. If you used Power Query, simply go to your summary sheet, right-click the loaded data table, and select 'Refresh'. Power Query will pull the latest data from all connected sheets and reapply the 'Fail' filter automatically.

What happens if my worksheets have different column structures?

If the column headers do not match exactly, Power Query will create separate columns for the mismatched headers and fill the gaps with null values. It is highly recommended to standardize your headers across all sheets before appending them.

Why am I getting duplicated data in my summary sheet over time?

This usually happens if your Power Query includes the summary sheet itself in the append step. Ensure you filter out the summary sheet's name from the source file list before expanding and combining the data.

Excel & Data Problems