Function Problems

Filter Excel Data by Date Range and a Select All Option

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

Create a dynamic formula that filters records by a date range and a drop-down selection of EE, SE, or all values.

Filter Excel Data by Date Range and a Select All Option
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.
Before you start

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.

Solution 1Recommended

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.

1
Check the drop-down

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.

2
Enter the date range

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.

3
Enter the formula

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")

4
Test the selections

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.

Use FILTER with a conditional Select All criterion
Dates with times: The formula includes the end date when Settlement Date values are dates without times. If they include times, replace the end-date condition with FYFunded_Deals[Settlement Date]<$R$3+1 to include the entire end date.
Work with spreadsheet filters

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. 1. Open the workbook: In WPS Spreadsheet, select Open and choose the Excel workbook containing the FYFunded_Deals table.
  2. 2. Set the criteria: Choose EE, SE, or Select All in O3, then enter the start and end dates in Q3 and R3.
  3. 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. 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.
Filter deals by date and CP ID with a dynamic formulaOpen and save Microsoft Excel XLSX filesEdit spreadsheet data in a familiar interface
microsoft office alternative - wps office

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.