How to Group PivotTable Items Below Five Percent as Other in WPS Spreadsheets
Question details
The user needs to display exchanges that contribute more than 5 percent of the indicator, while grouping all smaller exchanges into an "Other" category separately for each scenario in a PivotTable.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Analyzing exchange contributions by scenario in a PivotTable
- Observed behavior
- Smaller exchanges appear as individual PivotTable items instead of being combined into a single Other category per scenario.
Make sure your source data has separate columns for Scenario, Exchange, and Quantity, and that the helper column area is empty so you can add the grouping formula.
Group PivotTable items below 5% as Other with a helper column
Use this when you want a regular WPS PivotTable to show an Other category without using the Excel Data Model or DAX.
This method adds a calculated helper column to the source data. The formula calculates each exchange's percentage of its scenario total and returns the exchange name only when that percentage is greater than 5%.
Open the WPS Spreadsheets workbook that contains the Scenario, Exchange, and Quantity columns.
Add a new column next to your data and name the header Grouped Exchange.
In the first data row of the helper column, enter: =IF(SUMIFS($C:$C,$A:$A,A2,$B:$B,B2)/SUMIFS($C:$C,$A:$A,A2)>0.05,B2,"Other")
Drag the fill handle down through every source row so each exchange receives a grouped label.
Select the data range, go to Insert > PivotTable, choose a location, and click OK.
In the PivotTable Fields pane, drag Scenario to Rows, Grouped Exchange to Rows below Scenario, and Quantity to Values.
Use a threshold cell to make the percentage cut-off adjustable
Use this when you want to test different percentage cut-off values without rewriting the formula each time.
Group small PivotTable items as Other directly in WPS Office Spreadsheets
WPS Spreadsheets lets you complete this grouping with a helper formula and a regular PivotTable, so you do not need the Excel Data Model or DAX.
- 1. Download WPS Office: Download WPS Office from the official website and open the WPS Spreadsheets application.
- 2. Add the grouping helper column: In WPS Spreadsheets, add a Grouped Exchange column and enter =IF(SUMIFS($C:$C,$A:$A,A2,$B:$B,B2)/SUMIFS($C:$C,$A:$A,A2)>0.05,B2,"Other") in the first row.
- 3. Create the PivotTable: Select the data range and click Insert > PivotTable.
- 4. Build the rows and values: Drag Scenario and Grouped Exchange to Rows, then drag Quantity to Values.
- 5. Review grouped results: Check that each scenario shows large exchanges individually and all smaller exchanges as Other.
Frequently Asked Questions
Can I create this grouping without the Excel Data Model in WPS Spreadsheets?
Yes. Add a helper column to your source data that calculates each exchange's percentage of its scenario total and returns "Other" when the value is 5% or below, then build a regular PivotTable from that column.
What formula groups exchanges below 5% as Other?
Use a helper column with: =IF(SUMIFS($C:$C,$A:$A,A2,$B:$B,B2)/SUMIFS($C:$C,$A:$A,A2)>0.05,B2,"Other"), assuming Scenario is column A, Exchange is column B, and Quantity is column C.
Will the Other category update when the source data changes?
Yes. After adding or changing source rows, fill the helper formula down and refresh the PivotTable by right-clicking it and choosing Refresh or by going to Data > Refresh All.
Does this work when there are multiple scenarios in the same worksheet?
Yes. The SUMIFS/SUMIF criteria use the current Scenario value, so each exchange is compared only against the total for its own scenario.
Can I adjust the 5% threshold later?
Yes. Put the threshold in a cell such as F1, reference $F$1 in the formula instead of 0.05, and refresh the PivotTable after changing the cell.
