How to Filter a PivotTable by Partial Text in WPS Office Spreadsheets
Question details
The user wants to filter a PivotTable so only customer name labels containing a supplied partial text are displayed. The original workflow uses an Excel Office Script with Power Automate, but WPS Office does not run Office Scripts, so the article explains the Microsoft Excel script approach and the equivalent WPS Spreadsheets label filter.
- Product
- WPS Office Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating a PivotTable filter based on a customer name value passed from Power Automate, while matching partial text instead of one exact item.
- Observed behavior
- Selecting one exact PivotTable item fails when the supplied customer name is partial; the desired outcome is to show all items whose labels contain that text.
Identify the exact worksheet name, PivotTable name, hierarchy name, and field name used for customer names in the workbook. If you are using WPS Spreadsheets, make sure the workbook is opened in a current version of WPS Office and the PivotTable is visible.
Use an Office Script label filter in Microsoft Excel for Power Automate
Use this when the workbook must stay in Microsoft 365 and the filter must be automated by Power Automate. It uses the PivotTable label filter condition contains instead of selecting one exact item.
WPS Office does not run Office Scripts, so this method applies only to Microsoft 365 workbooks opened in Excel for the web or Excel with Office Scripts enabled.
Pass the customer name from Power Automate as a script parameter, and adjust the hierarchy and field names to match your workbook.
In Excel for the web, open Automate > New Script, or in Excel desktop use the Automate tab if your plan includes Office Scripts.
Reference the worksheet, PivotTable, hierarchy, and PivotField by name, for example: const field = workbook.getWorksheet("Transportkosten_klant").getPivotTable("PivotTable1").getHierarchy("customernames").getPivotField("customernames");
Call field.applyFilter({labelFilter:{condition:ExcelScript.LabelFilterCondition.contains,substring:custName,exclusive:false}}); and pass custName from Power Automate as a script parameter.
Save the script, create or update the Power Automate flow to pass the customer name, and test that the PivotTable shows all labels containing the supplied text.
Filter a PivotTable by partial text in WPS Spreadsheets
Use this for a manual, script-free alternative in WPS Office. It reproduces the same Contains label filter directly from the PivotTable field menu.
Use WPS Office Spreadsheets as a Free, Script-Free Alternative for PivotTable Filters
WPS Office Spreadsheets is a free, lightweight Microsoft Excel alternative that opens .xlsx and .xlsm workbooks with familiar PivotTable tools. You can apply a Contains label filter to display partial customer name matches without needing Office Scripts, Power Automate, or a Microsoft 365 automation license.
- 1. Download and install WPS Office: Download WPS Office from the official WPS website and install WPS Spreadsheets on your Windows or macOS computer.
- 2. Open the Excel workbook: Launch WPS Spreadsheets and open the .xlsx or .xlsm workbook that contains the PivotTable.
- 3. Apply the Contains label filter: Click the drop-down arrow next to the customer name field in the PivotTable, choose Label Filters > Contains, enter the partial customer text, and click OK.
Frequently Asked Questions
Does WPS Office support Excel Office Scripts?
No, WPS Office does not run Microsoft Excel Office Scripts or Power Automate connectors. You can still reproduce the same PivotTable partial-text filter in WPS Spreadsheets by using the PivotTable field drop-down menu and Label Filters > Contains.
How do I filter a PivotTable by text that appears anywhere in the label?
In WPS Spreadsheets, click inside the PivotTable, open the drop-down menu for the field in the Rows or Columns area, choose Label Filters > Contains, and enter the partial text. In Microsoft Excel Office Scripts, use LabelFilterCondition.contains with the substring parameter.
Can I pass a Power Automate value to a WPS Spreadsheets filter?
WPS Spreadsheets does not have a direct Power Automate connector for Office Script-style parameters. You can manually enter the value in the Label Filter dialog, or keep the automated workflow in Microsoft Excel if Power Automate integration is required.
Why does my PivotTable filter only show one exact item instead of all partial matches?
The filter is likely set to an exact item selection. Switch to a label filter that contains the customer name text, either in WPS Spreadsheets via Label Filters > Contains or in Excel Office Scripts with LabelFilterCondition.contains.
Will my Excel PivotTable open correctly in WPS Spreadsheets?
Yes, WPS Spreadsheets opens .xlsx and .xlsm workbooks and retains PivotTable structure. You can then use the PivotTable field menus to apply or change label filters, including Contains filters for partial text.
