Understand PivotTable Grouping Boundaries for Percentage Ranges in WPS Office Spreadsheets
Question details
The user wants to understand how WPS Office Spreadsheets assigns values to PivotTable percentage range groups and confirm that boundary values are not double-counted when intervals appear to overlap.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is creating or reviewing a PivotTable that groups percentage data into 10-point intervals and needs to interpret displayed range labels such as 0–10%, 10–20%, and 20–30%.
- Observed behavior
- The displayed groups appear to overlap at boundaries, causing uncertainty about whether a value such as 10% is counted in both groups.
Open the workbook that contains the percentage data and confirm the source column stores numeric percentage values rather than text labels, because PivotTable grouping depends on numeric data.
Use the Built-in 10-Point Grouping Boundary Rule in WPS Spreadsheets
Use this if you want to keep the default percentage grouping and simply confirm how WPS assigns values to the displayed intervals.
The labels 0–10%, 10–20%, and 20–30% describe the range boundaries but do not mean a value is counted twice. The first group includes values greater than or equal to the starting value and up to the next boundary. Later groups include values greater than the previous boundary and up to the next boundary.
This means 10% falls in the 0–10% group, 20% falls in the 10–20% group, and so on. If your report readers expect non-overlapping labels such as 0–9%, 10–19%, and 20–29%, use a helper column with custom bins.
Click any percentage row label in the PivotTable to activate the PivotTable tools in WPS Spreadsheets.
Right-click the selected label and choose Group, or go to the PivotTable Tools tab and click Group Selection to view the Starting at, Ending at, and By interval.
In the Grouping dialog, confirm the Starting at and By values, then click OK. WPS assigns the first group values greater than or equal to the starting value through the next boundary, and later groups values greater than the previous boundary through the next boundary.
Create a Helper Column with Custom Percentage Bins for Clear Labels
Use this when you want labels such as 0–9%, 10–19%, and 20–29% so displayed intervals do not share endpoint values.
Group Percentage Data in PivotTables Without Boundary Confusion in WPS Office
WPS Office Spreadsheets gives you two accurate ways to work with percentage ranges in PivotTables: keep the built-in grouping and understand its boundary rule, or create a helper column with custom bins for non-overlapping labels.
- 1. Open your percentage data in WPS Spreadsheets: Launch WPS Office, open the workbook, and confirm the source column contains numeric percentage values.
- 2. Create a helper column for custom bins: Insert a new column next to the data and enter a bin formula such as =LOOKUP(A2,{0,0.1,0.2},{"0–9%","10–19%","20–29%"}), then fill the formula down.
- 3. Insert a PivotTable with the helper column: Select the expanded data range, go to Insert > PivotTable, and choose a new worksheet.
- 4. Build the grouped report: Drag the Percentage Bin field to Rows and the percentage field to Values to show each value under exactly one non-overlapping label.
- 5. Check the built-in boundary rule: If you keep the default grouping, right-click a percentage row label, choose Group, and confirm that boundary values belong to the group that ends at that boundary.
Frequently Asked Questions
Does 10% appear in both the 0–10% and 10–20% groups?
No. WPS Spreadsheets follows the standard PivotTable rule: the first group includes values greater than or equal to the starting value through the next boundary, and later groups include values greater than the previous boundary through the next boundary. Therefore 10% belongs to the 0–10% group, not the 10–20% group.
Why do the default percentage groups show labels like 0–10% and 10–20%?
The labels are range names for the intervals. They share a displayed endpoint at the boundary, but the underlying grouping assigns the boundary value to only one group—the group that ends at that boundary.
How can I make PivotTable labels show 0–9%, 10–19%, and 20–29% instead?
Add a helper column next to your data with a formula such as =LOOKUP(A2,{0,0.1,0.2},{"0–9%","10–19%","20–29%"}), then build the PivotTable from the expanded range and place the helper column in Rows.
Where can I open the grouping settings in WPS Spreadsheets?
Select a grouped row label in the PivotTable, right-click it, and choose Group, or use the PivotTable Tools tab and click Group Selection to view Starting at, Ending at, and By.
