Filter Excel Data by Date Range and a Select All Option
Question details
Create a dynamic formula that filters records by a date range and a drop-down selection of EE, SE, or all values.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has a table of funded deals, a CP ID drop-down in O3, and start and end dates in Q3 and R3.
- Observed behavior
- The desired result is a spill range containing records within the date range that match the selected CP ID, or every CP ID when Select All is chosen.
Check that your Excel version supports the FILTER function and that the table is named FYFunded_Deals with columns named CP ID and Settlement Date. Enter valid dates in Q3 and R3, and include EE, SE, and Select All in the O3 drop-down.
Use FILTER with a conditional Select All criterion
Return rows within the selected dates while applying the CP ID criterion only when a specific value is selected.
Select cell O3 and confirm that its list includes EE, SE, and the exact text Select All. To edit the list, use Data > Data Validation.
Enter the start date in Q3 and the end date in R3. Make sure the Settlement Date column contains actual Excel dates rather than text.
Select an empty cell outside the source table and enter: =FILTER(FYFunded_Deals,(IF($O$3="Select All",1,FYFunded_Deals[CP ID]=$O$3))*(FYFunded_Deals[Settlement Date]>=$Q$3)*(FYFunded_Deals[Settlement Date]<=$R$3),"No matches")
Choose EE, SE, or Select All in O3 and check the spilled results. Leave the cells below and to the right of the formula empty so the results can expand.

Filter the Same Deal Data in WPS Spreadsheet
In a WPS Spreadsheet version that supports dynamic FILTER formulas and the workbook's table references, use the same date and drop-down criteria to return matching deals.
- 1. Open the workbook: In WPS Spreadsheet, select Open and choose the Excel workbook containing the FYFunded_Deals table.
- 2. Set the criteria: Choose EE, SE, or Select All in O3, then enter the start and end dates in Q3 and R3.
- 3. Enter the FILTER formula: In an empty cell outside the table, enter =FILTER(FYFunded_Deals,(IF($O$3="Select All",1,FYFunded_Deals[CP ID]=$O$3))*(FYFunded_Deals[Settlement Date]>=$Q$3)*(FYFunded_Deals[Settlement Date]<=$R$3),"No matches").
- 4. Check the results: Change the O3 selection and verify that the results update. If WPS does not recognize FILTER or the table references, check whether your installed version supports them.

Frequently Asked Questions
How does Select All bypass the CP ID filter?
The IF test checks whether O3 equals Select All. If it does, the formula uses 1 for the CP ID criterion, allowing every CP ID through while still applying both date conditions.
Why does the formula show No matches?
No rows meet all the criteria. Check the selection in O3, the dates in Q3 and R3, and whether the Settlement Date values are real dates.
Why do I see a #SPILL! error?
The FILTER results need an unobstructed range outside the source table. Clear any cells blocking the intended spill area and check the formula again.
How can I include records with times on the end date?
Replace FYFunded_Deals[Settlement Date]<=$R$3 with FYFunded_Deals[Settlement Date]<$R$3+1. This includes times throughout the end date.
Can I use this formula in an older Excel version?
FILTER requires a version that supports dynamic arrays, such as Excel for Microsoft 365 or Excel 2021 and later. In an older version, use the table's filter arrows to apply the CP ID and date filters manually.




