How to Use a PivotTable in WPS Spreadsheets to Find Sales Concentrated in One Customer
Question details
The user needs to identify items where a single customer accounts for a high share of total sales, such as 90% or more, using a PivotTable sales summary instead of only counting distinct customers.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has item-level sales records with customer and sales amount or quantity fields and wants to flag sales concentration by item.
- Observed behavior
- A standard PivotTable can summarize sales by item and customer, but it does not directly show the largest customer’s percentage of each item total without an additional concentration calculation.
Make sure the source data is in a tabular layout with unique headers for Item, Customer, and Sales Amount or Quantity, and that the sales values are numeric so the PivotTable can sum them.
Summarize Sales by Item and Customer and Calculate Concentration with Helper Formulas
Use this method for a straightforward WPS Spreadsheets workflow: build the PivotTable summary, then use formulas to compute each item’s total, largest customer subtotal, and concentration.
This approach keeps the PivotTable as the summary engine and uses Excel-compatible formulas for the concentration logic.
Click any cell inside the sales data, go to Insert > PivotTable, choose New Worksheet, and click OK.
In the PivotTable Fields pane, drag Item and Customer to the Rows area, then drag Sales Amount or Quantity to the Values area so it shows as Sum.
Select the PivotTable rows containing Item, Customer, and Sum values, copy them, and paste as values into a new worksheet so formulas do not overwrite the PivotTable.
Use =SUMIFS(SalesRange,ItemRange,ItemCell) for the item total and =MAXIFS(SalesRange,ItemRange,ItemCell) for the largest customer subtotal, with ranges pointed to the copied summary.
Add a concentration column with =LargestCell/TotalCell, format it as a percentage, and filter or conditional format the rows where the value is 0.9 or greater.
Find 90% Customer Concentration with WPS Spreadsheets
Summarize item and customer sales in a WPS Spreadsheets PivotTable, then add SUMIFS and MAXIFS formulas to flag items where one customer drives 90% or more of sales. WPS Spreadsheets opens and saves .xlsx files, so your existing sales workbooks remain compatible.
- 1. Download and open WPS Office: Install WPS Office, open WPS Spreadsheets, and load the sales workbook containing Item, Customer, and Sales Amount or Quantity.
- 2. Insert the PivotTable: Click inside the data range, go to Insert > PivotTable, choose New Worksheet, and click OK.
- 3. Configure rows and values: In the PivotTable Fields pane, drag Item and Customer to Rows and drag Sales Amount or Quantity to Values as Sum.
- 4. Add concentration formulas: Copy the PivotTable summary to a new sheet as values, then use SUMIFS to get each item total and MAXIFS to get the largest customer subtotal.
- 5. Filter the 90% concentration rows: Add a formula column for largest subtotal divided by item total, format as a percentage, and filter for values greater than or equal to 90%.
Frequently Asked Questions
Can a standard PivotTable directly show the largest customer’s share for each item?
Not by default. A standard PivotTable shows item and customer totals, but you need a helper formula or a data-model measure to calculate largest customer sales divided by item total.
Does this work if I use quantity instead of sales amount?
Yes. Place Quantity in the Values area as Sum, and use the same SUMIFS and MAXIFS approach on the copied summary to find quantity concentration.
Why is counting distinct customers not enough for this analysis?
Distinct customer counts show whether an item is sold to one customer, but they do not reveal when one customer has only a few transactions that represent a large share of total sales volume.
How do I filter rows with 90% concentration in WPS Spreadsheets?
After adding the concentration formula column, select the data, enable Filter from the Data tab, and set a number filter for greater than or equal to 0.9.
