Pivot Table Issues

How to Capture Slicer Selections from a Regular Excel Table in WPS Spreadsheets

Steven ParkerSteven Parker Nov 13, 2026 869 views

Question details

The user needs to display the item or items selected by a slicer that is connected to a regular Excel table, without using GETPIVOTDATA because no PivotTable is present.

How to Capture Slicer Selections from a Regular Excel Table in WPS Spreadsheets
Product
WPS Spreadsheets
Device & OS
not provided
Scenario
A user has a regular Excel table with a slicer and wants to show the currently selected slicer item or items in a cell for reports or dashboards.
Observed behavior
GETPIVOTDATA is not available because the slicer is not connected to a PivotTable, so a helper-column formula is required to identify visible rows and combine their values.
Before you start

Make sure your data is formatted as an Excel table and the slicer is connected to that table. Check that your WPS Spreadsheets version supports the FILTER and TEXTJOIN functions before creating the helper column.

Solution 1Recommended

Capture slicer selections with a SUBTOTAL helper column and TEXTJOIN/FILTER

Use this method in WPS Spreadsheets versions that support FILTER and TEXTJOIN; it updates automatically when the slicer changes and preserves duplicates row by row.

Because the slicer is connected to a regular table rather than a PivotTable, GETPIVOTDATA cannot retrieve the selected items. A helper column uses SUBTOTAL(103,...) to mark only the rows currently visible after the slicer filters the table. Then FILTER returns the visible item values and TEXTJOIN combines them into one list.

1
Add a Visible helper column to the table

Select the first empty cell in a new column inside the table, type =SUBTOTAL(103,[@Item]) in the formula bar, and press Enter. Replace [@Item] with the column that contains your slicer values if it is named differently.

2
Connect and test the slicer on the table

Click the existing slicer, or go to Table Tools > Insert Slicer, choose the Item column, and then click different slicer buttons. Visible rows display 1 in the helper column while hidden rows display 0.

3
Enter the TEXTJOIN/FILTER result formula

Click an empty cell, enter =TEXTJOIN(", ",TRUE,FILTER(Table1[Item],Table1[Visible]=1)) in the formula bar, and press Enter. Adjust Table1, Item, and Visible to match your table names.

4
Verify the captured selection

Select different slicer buttons and confirm that the result cell updates immediately to show the currently selected item or items as a comma-separated list.

Capture slicer selections with a SUBTOTAL helper column and TEXTJOIN/FILTER
Updates with the slicer: The SUBTOTAL formula recalculates when the table is filtered by the slicer, so this method follows the current screen visibility rather than hard-coded criteria.

Capture table slicer selections in WPS Spreadsheets

WPS Spreadsheets can capture slicer selections from a regular Excel table without GETPIVOTDATA. Add a SUBTOTAL helper column to mark visible rows, then use FILTER and TEXTJOIN to return a clean, comma-separated list that updates with the slicer.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx or .xls file that contains the table and slicer, and click the worksheet tab with the table.
  2. 2. Add the Visible helper column: Click the first empty cell in a new table column, type =SUBTOTAL(103,[@Item]) in the formula bar, and press Enter.
  3. 3. Enter the TEXTJOIN/FILTER result formula: Select an empty cell, enter =TEXTJOIN(", ",TRUE,FILTER(Table1[Item],Table1[Visible]=1)), and press Enter.
  4. 4. Test the slicer selection: Click different buttons in the slicer and confirm that the result cell updates to show the currently selected item or items.
Displays selected slicer items without a connected PivotTableFully compatible with Excel .xlsx and .xls table workbooksIncludes dynamic functions such as SUBTOTAL, FILTER, and TEXTJOINFamiliar table and slicer controls make migration easyFree, lightweight, and easy to use for everyday spreadsheet tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn’t GETPIVOTDATA work for a regular table slicer?

GETPIVOTDATA only returns values from a PivotTable. When a slicer is connected directly to an Excel table, there is no PivotTable context, so use a helper column with SUBTOTAL(103,[@Item]) and combine visible values with FILTER and TEXTJOIN.

Which WPS Spreadsheets functions do I need to capture slicer selections?

Use SUBTOTAL(103,[@Item]) to mark visible rows, FILTER to return only those rows, and TEXTJOIN to combine the values. FILTER is available in newer WPS Spreadsheets versions.

How do I handle duplicate items in the slicer source table?

The SUBTOTAL helper method returns duplicate items once per visible row. If you need a unique list, wrap the FILTER result with UNIQUE when supported, or use a separate dedupe step.

Will the captured selection update automatically when I click a different slicer button?

Yes. SUBTOTAL recalculates after the table is filtered by the slicer, and FILTER/TEXTJOIN update the result cell immediately.