Use a Month Selector to Display Matching Data in WPS Spreadsheet
Question details
Create a report that automatically shows data for the month and year selected from a drop-down list or combo box.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants to choose a reporting month and year, then retrieve or summarize records that belong to that period.
- Observed behavior
- The desired result is for the worksheet report to update with matching records or calculations after the selected month changes.
Make sure the source table contains real Excel-style dates rather than text dates. Use a helper list of valid month-start dates, such as 1/1/2025 and 2/1/2025, formatted as mmmm yyyy for the selector.
Create a month drop-down and return matching records with FILTER
Use this method when you want the report to display every source row that falls within the selected month and year.
Store the selected month as a real date representing the first day of the month. This lets the formula match all dates from that month even when source dates include different days.
On a helper sheet or unused area, enter month-start dates in a column, such as 1/1/2025, 2/1/2025, and 3/1/2025. Select the cells and use Home > Number Format to display them as mmmm yyyy.
Select the report selector cell, such as B2, then choose Data > Data Validation. Set Allow to List, set Source to the helper range containing the month-start dates, and select OK.
Click the first output cell, such as A6, and enter a formula like =FILTER(A2:D100,(B2:B100>=EOMONTH($B$2,-1)+1)*(B2:B100<=EOMONTH($B$2,0)),"No matching records"). Replace A2:D100 with the source data range, B2:B100 with the source date column, and $B$2 with the month selector cell.
Open the drop-down arrow in B2 and choose a month. The FILTER result updates to show only records dated within the selected month.
Calculate month-based totals with SUMIFS, COUNTIFS, or AVERAGEIFS
Use this option when the report needs a total, record count, or average instead of a full list of matching rows.
Build an Interactive Month-Based Report in WPS Spreadsheet
WPS Spreadsheet lets you create a month-and-year selector with Data Validation and connect it to dynamic formulas such as FILTER, SUMIFS, COUNTIFS, and AVERAGEIFS. Select a reporting period and let the worksheet return the matching records or summary values automatically.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook containing the dated source data.
- 2. Create a list of reporting months: Enter month-start dates in a helper column and format the cells as mmmm yyyy from the Home tab.
- 3. Add the month selector: Select the report input cell and choose Data > Data Validation > List, then select the helper month range as the source.
- 4. Add a matching-data formula: Select the report output area and use FILTER for detail rows or SUMIFS, COUNTIFS, or AVERAGEIFS for a monthly summary.
Frequently Asked Questions
Why does my month selector not match dates in the source table?
The selector or source dates may be stored as text. Convert both to real dates, use a month-start date such as 1/1/2025 in the selector, and apply the mmmm yyyy format only for display.
Can I show all transactions for a selected month in WPS Spreadsheet?
Yes. Use Data > Data Validation to create the month selector, then use FILTER with criteria from the first day through the last day of the selected month.
How can I total sales for the selected month and year?
Use SUMIFS with two date criteria: one greater than or equal to EOMONTH(selector,-1)+1 and one less than or equal to EOMONTH(selector,0).
Can I use a combo box instead of a data-validation drop-down?
Yes, a combo box can be inserted when a form-control interface or macro-based workflow is required. For most formula-driven monthly reports, Data > Data Validation > List is simpler and does not require VBA.
