How to Filter Data for the Next 90 Days in Excel
Question details
The user wants to extract specific columns (MSC, Unit, and Country) from a master tracker to generate a report showing only units scheduled to move within the next 90 days.
- Product
- Excel 365
- Device & OS
- not provided
- Scenario
- Creating a dynamic 90-day moving tracker report based on multiple conditions and selecting non-contiguous data columns.
- Observed behavior
- Needs a formula solution to automatically filter rows where the scheduled days are between 1 and 90, grouped and selectively extracted from the main dataset.
Ensure your master tracker data is formatted as an official Excel Table (e.g., Table1) so that your formula references remain dynamic and update automatically when new rows are added.
Use the FILTER and CHOOSE Functions for Dynamic Extraction
This method dynamically extracts specific non-contiguous columns for records moving within the next 90 days, excluding today.
The FILTER function is perfect for extracting rows based on criteria, while the CHOOSE function allows you to pick and rearrange specific columns from your source table into a new, single array.
Verify that your master tracker is formatted as a table (e.g., 'Table1') and identify the exact column names, such as 'MSC', 'Unit', 'Country', and 'Days from HSAD'.
Click on the cell in a new report sheet where you want the top-left corner of the filtered data to appear.
Type the formula: =FILTER(CHOOSE({1,2,3},Table1[MSC],Table1[Unit],Table1[Country]),(Table1[Days from HSAD]>0)*(Table1[Days from HSAD]<=90),"") and press Enter.
The formula will automatically spill the results into the neighboring rows and columns, displaying only the MSC, Unit, and Country for units moving from tomorrow through day 90.
Use Standard Number Filters for Quick Views
If you only need to view the data temporarily and do not need to extract it to a separate dynamic report, use Excel's built-in data filters.
Use WPS Spreadsheet to Filter and Track Data Seamlessly
WPS Spreadsheet offers powerful dynamic array functions like FILTER and CHOOSE, along with extensive table management tools. It is fully equipped to handle advanced data extraction and tracking scenarios.
- 1. Open your tracker in WPS Spreadsheet: Launch WPS Office and open your existing master tracker workbook to access your data.
- 2. Apply dynamic array formulas: Use the exact same =FILTER() and =CHOOSE() formulas as you would in Excel. WPS Spreadsheet natively supports these modern array calculations.
- 3. Export or share your report: Easily share your dynamic 90-day report with your team by clicking 'Share' or export it directly to PDF using the built-in toolbar options.
Frequently Asked Questions
Can I use column names instead of CHOOSE to reorder columns in the FILTER function?
The FILTER function returns contiguous columns by default. Using CHOOSE or HSTACK with specific column references is the standard and most reliable way to extract, isolate, and reorder non-contiguous columns dynamically.
Why does my FILTER formula return a #CALC! or #VALUE! error?
The #CALC! error typically occurs if no records match your conditions (e.g., there are no units moving within the 1-90 day window). You can handle this gracefully by adding a text string like "No upcoming moves" as the third argument in your FILTER formula.
Does the days column need to be a raw number or a date?
For the specific formula provided (Days > 0 and Days <= 90), the column requires raw numerical values representing the days remaining. If your column contains target dates instead, you should calculate the difference using Target Date - TODAY() directly inside your formula conditions.
Can I export this dynamically filtered list directly to PowerPoint?
The array formula extracts the data natively within the spreadsheet. To push this specific report directly into PowerPoint, you would need to use a VBA macro, Power Query, or copy and paste the spilled array range as a linked object in your presentation slide.
