Power Query Problems

How to Combine Rows Marked with X into an Excel Summary List Using Power Query

Bushra Parveen Oct 13, 2026 869 views

Question details

The user needs to extract and consolidate specific row descriptions from multiple similar worksheets into a single summary list whenever a row is marked with an 'X'.

Product: Excel
Device & OS: not provided
Scenario: Modifying an existing company file where manual formatting changes, Excel Tables, or traditional filter functions are restricted or not preferred.
Observed behavior: The goal is to dynamically import data from multiple tabs, append the queries, filter for the marked rows, and load the clean result into a consolidated summary sheet.

Before you start

Ensure that all the source worksheets you want to combine have a consistent structure and identical column headers before running the query.

Solution 1Recommended

Use Power Query to Append and Filter Worksheets

Power Query can import data from multiple worksheets, combine them, and filter for rows marked with an 'X' without making permanent changes to your original company datasets.

This method is highly recommended when working with strict corporate files. Because Power Query runs in the background, you do not have to format existing data into Excel Tables or apply intrusive filter functions directly on the source tabs.

Get Data from Workbook

Open your Excel summary workbook. Go to the Data tab, click on 'Get Data', choose 'From File', and select 'From Workbook'. Locate and import your company file.

Select and Transform Data

In the Navigator dialog box, click on the folder icon representing the entire workbook to select multiple sheets, then click 'Transform Data' to launch the Power Query Editor.

Expand the Worksheet Data

In the Power Query Editor, filter the 'Kind' column to only show 'Sheet'. Then, click the expand icon (two diverging arrows) on the 'Data' column to reveal the rows from all combined worksheets.

Promote Headers and Filter for X

Go to the Home tab and click 'Use First Row as Headers' if your column names are in the first row. Locate your marker column, click the filter drop-down arrow, uncheck all options, and select only 'X'.

Close and Load to Summary Sheet

Once your data is filtered, click 'Close & Load To...' on the Home tab. Select 'Table' and choose the existing summary worksheet where you want the consolidated list to appear.

Use Power Query to Append and Filter Worksheets

Refreshing Your Summary List: When new rows are marked with 'X' in the source tabs, simply right-click your summary table and click 'Refresh' to instantly update the list.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Data Management?

While Power Query is a robust tool in Microsoft Excel, WPS Office provides a lightweight, free alternative with seamless format compatibility. Easily manage, filter, and consolidate your worksheet data using our intuitive built-in tools without the heavy system resource requirements.

1. Download and Install WPS Office: Visit the official WPS website to download the free version and follow the simple installation prompts.
2. Open Your Spreadsheets: Launch WPS Spreadsheets and seamlessly open your existing .xlsx company files without losing any formatting.
3. Utilize Built-in Data Tools: Navigate to the Data tab to access intuitive filtering, sorting, and data consolidation tools as a streamlined alternative to complex queries.

Fully compatible with Microsoft Excel (.xlsx) formatsLightweight design with fast installation and smooth performanceFamiliar user interface for a seamless migrationBuilt-in smart tools for quick sheet merging and data filtering

microsoft office alternative - wps office

Frequently Asked Questions

Can I combine rows marked with X without using Power Query?

Yes. If you are using Microsoft 365, you can use the FILTER function combined with VSTACK to dynamically pull marked rows from multiple sheets. However, Power Query remains the most robust option for workbooks with many sheets or stricter formatting rules.

Why is Power Query better than standard AutoFilters for company files?

Power Query extracts and transforms the data in the background. It doesn't require modifying the original company spreadsheets with new tables or formulas, thereby keeping the original source file completely untouched and pristine.

Will the summary list update automatically when I add a new X?

The summary list does not update in real-time. You must right-click the Power Query result table in your summary sheet and select 'Refresh', or set the workbook query properties to refresh automatically upon opening the file.

What happens if my source worksheets have different column headers?

If the column headers don't match exactly across your tabs, Power Query will create separate columns for the mismatched headers and fill the empty gaps with 'null' values. It is highly recommended to standardize your headers before appending.

Excel & Data Problems