How to Control Pivot Table Sort Order from a Slicer Selection in WPS Spreadsheets
Question details
The user needs a pivot table to place the first value selected in an Excel slicer at the top and display subsequently selected values below it in the click order.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a slicer in a pivot table and wants the visible table order to follow the sequence in which slicer buttons are clicked, rather than the default alphabetical or data order.
- Observed behavior
- A slicer only filters records and does not remember the order in which slicer items were selected, so the pivot table cannot automatically reorder rows according to the click sequence.
Make sure your pivot table and slicer already exist in WPS Spreadsheets, and add a unique identifier to the source data if it does not already have one. If you plan to use a macro, open the Developer tab and save a copy of the workbook as a macro-enabled file.
Record Slicer Selection Order with a VBA Macro and Sort the Pivot Table
Use this method when you need the table order to update automatically according to the exact sequence in which slicer items are clicked.
A slicer cannot natively store click order, so a macro must listen for slicer changes and write a rank to a helper column in the source data.
The pivot table can then sort by that rank to place the first selected value at the top.
In WPS Spreadsheets, click the Developer tab, then choose Visual Basic or Macros to open the macro editor.
Insert a new module and paste a macro that captures the PivotTable slicer selection event and writes the rank of each selected slicer item to a SortOrder helper column in the source data.
Run the macro once or connect it to the slicer change event so it updates the helper column automatically when a slicer button is clicked.
Return to the pivot table, click Refresh, and use Row Label Sort Options to sort by the SortOrder helper field in ascending order.
Create a Manual Priority Table and Sort with a Helper Column
Use this method when the desired sort order is known in advance and does not need to change each time you click a slicer.
Build a Dynamic SORTBY Summary That Follows a Sort-Order List
Use this method to keep the original pivot table untouched and return a separate sorted list that updates from a priority list.
Control Slicer Selection Sort Order in WPS Spreadsheets
WPS Spreadsheets can accomplish this by adding a rank helper column to the source data and then sorting the pivot table by that rank, so the first selected slicer value appears at the top.
- 1. Prepare the source data: Open the workbook in WPS Spreadsheets, confirm the source table has a unique Item column, and add a SortOrder helper column.
- 2. Assign ranks from slicer clicks: Use the VBA macro or a priority-table VLOOKUP to write 1, 2, 3 in SortOrder as slicer items are selected or ordered.
- 3. Add the helper to the pivot table: Click the pivot table, open Field List, and drag SortOrder into the Values area so it can be used for sorting.
- 4. Sort row labels by rank: Right-click a row label in the pivot table, open Sort > More Sort Options, and choose Ascending by the Sum of SortOrder field.
- 5. Refresh and verify: Click PivotTable Analyze > Refresh, then click different slicer buttons and confirm the first selected item appears at the top.
Frequently Asked Questions
Why does my Excel slicer not remember the order I click items?
A slicer is a filter control, not a sort control. It filters the pivot table but does not store click sequence, so you need a helper rank column or a macro.
Can WPS Spreadsheets open an Excel workbook that already has slicers?
Yes. Open the workbook in WPS Spreadsheets, and the slicer remains available; you can refresh and modify the pivot table while keeping the .xlsx file compatible.
How do I sort a pivot table by a helper column in WPS Spreadsheets?
Add the helper column to the Values area, right-click a row label, choose Sort, and sort by the helper field in ascending order.
Does the SORTBY approach work in all WPS Spreadsheets versions?
SORTBY requires a version that supports dynamic array formulas; if your version does not support it, use the priority-table VLOOKUP or macro method.
Can I use a macro to capture slicer selection order?
Yes. Open the Developer tab > Visual Basic, add a module that handles the slicer change event, and write the selected rank to a SortOrder column in the source data.
