Data Import & Export

Filter Data by Date Across Multiple Excel Worksheets

John Wilson Sep 25, 2026 871 views

Question details

The user wants to combine matching records from multiple identical department worksheets into a summary sheet based on a specific date.

How to Filter Data by Date Across Multiple Excel Worksheets

Product: Excel
Device & OS: not provided
Scenario: Consolidating and filtering departmental data by a specific date on a master summary sheet.
Observed behavior: The user needs a method to dynamically stack data from multiple identical worksheets and filter it by a single date input.

Before you start

Ensure all your department worksheets have exactly the same column layout, and that your date column is located in the same position across all sheets.

Solution 1Recommended

Combine and Filter Using VSTACK, FILTER, and LET Functions

Use modern dynamic array formulas to stack data from different sheets and filter by your target date in one step.

This method uses the VSTACK function to combine your ranges into a single array, and the FILTER function to extract only the rows matching your specified date. The CHOOSECOLS function helps identify which column contains the date.

Define your data ranges

Create named ranges for your department data (e.g., Dept1, Dept2, Dept3) or note their exact cell references.

Enter the target date

On your Summary sheet, enter the target date you wish to filter by in a reference cell, such as A1.

Input the dynamic array formula

Select the destination cell (e.g., A3) and enter the formula: =LET(stacked, VSTACK(Dept1, Dept2, Dept3), FILTER(stacked, CHOOSECOLS(stacked, 4)=A1, "No records with that date")). Change the number '4' to the actual column number that contains your dates.

Execute the formula

Press Enter. The filtered data from all department sheets will dynamically populate.

Combine and Filter Using VSTACK, FILTER, and LET Functions

Version Compatibility: This formula requires a version of Excel or WPS Office that supports dynamic array functions like VSTACK and CHOOSECOLS.

Solution 2

Consolidate and Filter Data Using Power Query

Append data from multiple sheets into one master table using Power Query, then filter it by date.

Advanced Data Management

Filter and Consolidate Data Seamlessly with WPS Office

WPS Spreadsheet offers full support for advanced dynamic array functions like VSTACK, FILTER, and LET, making it incredibly easy to combine and filter data across multiple worksheets without complex programming.

1. Open your Workbook: Launch WPS Spreadsheet and open the file containing your department worksheets.
2. Enter the Target Date: On your Summary sheet, type the date you want to filter by into a designated cell (e.g., A1).
3. Apply the Formula: Select the first cell where you want the results to appear, input your =LET(stacked, VSTACK(...)) formula, and press Enter to pull in all matching records instantly.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Supports modern dynamic array functions out of the box.Lightweight application with fast processing for large data sets.Free to use with a familiar, easy-to-navigate interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I filter across worksheets without using the VSTACK function?

Yes. If your software version does not support VSTACK, you can use Power Query to append the tables, or use VBA (macros) to loop through the worksheets and copy matching data to the summary sheet.

Why is my VSTACK and FILTER formula returning a #NAME? error?

The #NAME? error typically occurs if your version of the spreadsheet software does not support newer dynamic array functions like VSTACK or LET, or if a named range like 'Dept1' contains a typo and hasn't been defined correctly.

How do I define named ranges for my department data?

Highlight the entire data range in your department worksheet, click inside the Name Box located to the left of the formula bar, type a unique name (e.g., Dept1) without spaces, and press Enter.

Excel & Data Problems