Filter PivotTable IDs by Selected Month While Showing Other Months in WPS Spreadsheets
Question details
The user needs to filter a PivotTable or Power Pivot-style report so only IDs that are present in a chosen month are displayed, while performance values for all other months remain visible.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with monthly performance data and wants a month selector that filters IDs rather than hiding the month columns.
- Observed behavior
- A standard month slicer normally filters the month field itself, which removes the other month values from the report.
Open your monthly data in WPS Spreadsheets and make sure it has separate columns for ID, date/month, and performance value. Confirm the date column uses real date values or a consistent yyyy-mm text format so the month comparison works.
Filter IDs by a Selected Month with a Helper Column in WPS Spreadsheets
Use this method when a standard month slicer removes the other month columns and you need to keep all monthly performance columns visible.
Instead of slicing the month column directly, this method adds a TRUE/FALSE flag to the source data. The flag checks whether the row's ID appears in the selected month, so the PivotTable filters IDs without hiding other months.
The helper column can stay in the source table and be updated whenever you change the selected month cell.
In your WPS Spreadsheets workbook, select an empty cell such as J1, type the month you want to filter as text (for example 2025-01), and apply the Text format if needed.
Add a new column titled 'ID in selected month' next to your source data. In the first data row, enter the formula =SUMPRODUCT((TEXT($B$2:$B$1000,"yyyy-mm")=$J$1)*($A$2:$A$1000=A2))>0, adjusting A and B to your ID and date columns.
Click any cell in the source data, go to Insert > PivotTable, confirm the full range including the helper column, and choose to place the PivotTable on a new worksheet.
In the PivotTable Fields pane, drag the 'ID in selected month' field to the Filters area. Set the filter to TRUE so only IDs present in the selected month remain.
Drag the ID field to Rows, the Month field to Columns, and the performance value field to Values. The other month columns stay visible for the filtered IDs.
Filter PivotTable IDs by Selected Month in WPS Spreadsheets
Use a helper column and a WPS Spreadsheets PivotTable filter to show only IDs that exist in the selected month while preserving the other monthly performance columns. This avoids complex Power Pivot models and works directly with your Excel-format data.
- 1. Open your workbook in WPS Spreadsheets: Download WPS Office, open WPS Spreadsheets, and load the Excel workbook that contains the monthly performance data.
- 2. Add a selected-month helper flag: In a new column, enter a formula that checks whether each ID appears in the selected month, such as =SUMPRODUCT((TEXT($B$2:$B$1000,"yyyy-mm")=$J$1)*($A$2:$A$1000=A2))>0.
- 3. Insert a PivotTable: Select the source data including the helper column, click Insert > PivotTable, and place the report on a new worksheet.
- 4. Apply the filter and arrange fields: Drag the helper field to Filters and set it to TRUE. Add ID to Rows, Month to Columns, and performance to Values.
- 5. Refresh and reuse: When the selected month changes, update the input cell, right-click the PivotTable, and choose Refresh.
Frequently Asked Questions
Why does a normal month slicer remove the other month columns?
A slicer filters the field it is connected to. When it is connected to the Month column, it hides every row or column that is not in the selected month. Filter the ID-presence helper field instead of slicing the month field.
Can I make the selected month dynamic in WPS Spreadsheets?
Yes. Use a dedicated input cell for the selected month and reference that cell in a COUNTIFS or SUMPRODUCT helper column, then refresh the PivotTable after changing the input.
Does WPS Spreadsheets support Power Pivot or DAX measures?
WPS Spreadsheets includes PivotTable tools and supports many Excel formulas, but for Power Pivot/DAX models the helper-column method is the recommended workaround. You can still open and work with .xlsx data in WPS Office.
How do I refresh the PivotTable after changing the selected month?
Right-click the PivotTable and choose Refresh, or select the PivotTable and use the PivotTable Analyze tab to click Refresh.
Will the other month columns stay in the report?
Yes, if the Month field is placed in the Columns area and the helper field is used to filter IDs, the report keeps all month columns for the IDs that pass the filter.
