Filter Data from Multiple Excel Worksheets on a Summary Sheet
Question details
Combine employee records from multiple worksheets and filter them by name 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.
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.
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.
On each source worksheet, select the records and choose Insert > Table. Confirm that the table has headers.
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.
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.
Choose Home > Close & Load > Close & Load To. Select Table and Existing worksheet, then choose a starting cell on the summary sheet.
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.

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.

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.




