How to Filter Excel Table Rows by Year Using a Drop-Down
Question details
The user needs to filter and display rows from an Excel data table dynamically based on a specific year selected in a reference cell containing a drop-down list.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Setting up a dynamic dashboard or report where records are extracted based on a selected year, ensuring that any cells with blank dates in the source table are excluded from the final output.
- Observed behavior
- The user requires a formula to extract the records without returning errors or empty rows when the date column contains blank entries.
Ensure your date column is formatted as valid dates rather than text, and verify that your spreadsheet software supports dynamic array functions like FILTER.
Use the FILTER Function with Multiple Criteria
This method uses the dynamic array FILTER function combined with the YEAR function to extract matching records while strictly ignoring empty date cells.
The FILTER function can handle multiple conditions by multiplying logical expressions together. By checking both the year and verifying the cell is not blank, you ensure clean, accurate results.
Locate the name of your source data table (e.g., 'Venues14'), the specific Date column (e.g., 'Venues14[Date]'), and the cell containing your drop-down list year (e.g., 'K5').
Click on the empty cell where you want the filtered data to start, and type the following formula: =FILTER(Venues14, (Venues14[Date]<>")*(YEAR(Venues14[Date])=K5), "Not found")
Press Enter. The formula will instantly evaluate the conditions and spill all the rows that belong to the year selected in K5 into the adjacent cells, automatically omitting blank dates.
Easily Filter and Manage Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas like FILTER, allowing you to easily build interactive dashboards and reports. You can handle drop-down lists and extract complex data sets effortlessly.
- 1. Open your workbook in WPS: Launch WPS Spreadsheet and open the file containing your source data table.
- 2. Create a drop-down list: Go to the Data tab, click Data Validation, and select 'List' to input the years you want to filter by.
- 3. Apply the FILTER formula: Use the exact same =FILTER() formula as you would in Excel to instantly extract your yearly data without format loss.
Frequently Asked Questions
Why am I getting a #CALC! error when using the FILTER function?
A #CALC! error typically occurs when the FILTER function finds no matching records and you haven't provided an 'if_empty' argument. Including a fallback text like "Not found" at the end of your formula prevents this error.
Can I filter table rows by month instead of year?
Yes, you can simply replace the YEAR() function in the formula with the MONTH() function. For example, using (MONTH(Venues14[Date])=K5) will filter the table based on a month number selected in cell K5.
What if I need to filter by year and another text condition?
You can multiply additional logical conditions within the include argument. For example, =FILTER(Venues14, (YEAR(Venues14[Date])=K5)*(Venues14[Status]="Active"), "Not found") will filter rows that match both the selected year and an 'Active' status.
How do I fix a #SPILL! error after typing the formula?
A #SPILL! error means there is existing text, numbers, or spaces in the cells where the formula is trying to place the filtered results. Clear the cells below and to the right of your formula to allow the data to populate freely.
