Power Query Problems

Filter Data from Multiple Excel Worksheets on a Summary Sheet

Rana GarciaRana Garcia Nov 20, 2026 869 views

Question details

Combine employee records from multiple worksheets and filter them by name on a summary sheet.

Filter Data from Multiple Excel Worksheets on a Summary Sheet
Product
Microsoft Excel
Device & OS
not provided
Scenario
A billing workbook contains employee records on separate worksheets that need to be reviewed together.
Observed behavior
The desired summary sheet displays matching records from the combined worksheets when an employee name is chosen.
Before you start

Make sure each source sheet has a header row and that matching fields, such as employee name, use the same column names. The Power Query steps below apply to Excel 2016 or later.

Solution 1Recommended

Append the worksheets with Power Query and filter the summary

Use this no-code method to combine similarly structured tables and filter their records by employee name.

1
Create source tables

On each source worksheet, select the records and choose Insert > Table. Confirm that the table has headers.

2
Create a query for each table

Click inside a source table and choose Data > From Table/Range. In Power Query Editor, choose Home > Close & Load > Close & Load To, then select Only Create Connection. Repeat for each table.

3
Append the queries

Open a query from Data > Queries & Connections. In Power Query Editor, choose Home > Append Queries > Append Queries as New, select two or more source queries, and confirm.

4
Load the combined records

Choose Home > Close & Load > Close & Load To. Select Table and Existing worksheet, then choose a starting cell on the summary sheet.

5
Filter and refresh

On the summary table, open the filter arrow in the employee name column and select a name. After source data changes, choose Data > Refresh All to update the combined records.

Append the worksheets with Power Query and filter the summary
Filtering by a selected cell: The table filter requires you to choose the name from the filter menu. Automatically changing results when a separate summary cell changes requires additional setup, such as a Worksheet_Change VBA macro.
Free Microsoft Office alternative

Review combined employee records in WPS Spreadsheets

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel files and filter combined records in WPS Spreadsheets for an easy transition, though it does not fix Excel Power Query issues.

Filter a combined employee list by name in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface for a smoother transitionWork with a free, lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Do the source worksheets need identical column names?

Use the same header names for fields you want combined, such as Employee Name. Power Query appends columns by name, so mismatched headers can create separate columns.

How do I update the summary after changing source records?

Choose Data > Refresh All in Excel. Add new records within the source tables so they are included when the queries refresh.

Can I use a PivotTable instead of a filter?

Yes. After loading the combined query, select the resulting table and choose Insert > PivotTable. Add the employee name field as a filter or slicer.

Can choosing a name in a separate cell update the results automatically?

The standard table filter does not respond automatically to a separate cell. A Worksheet_Change VBA macro is one possible approach; save a macro-enabled workbook as an XLSM file.