WPS Spreadsheets PivotTable: Create a Unique Client Invoice Summary
Question details
Create a summary of invoice transactions grouped by unique client and client ID, with total invoice amounts.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The source contains approximately 10,000 invoice transactions that need to be summarized by client and organized by transaction value.
- Observed behavior
- The desired result is a PivotTable showing each client and client ID once with the summed invoice amount, while the source transactions are sorted by client name and descending amount.
Check that the invoice data has one header row, including Client Name, Client ID, Invoice Number, and Amount. Confirm that the Amount column contains numeric values and select the complete data range before creating the PivotTable.
Create a unique client invoice summary with a PivotTable
Use this method to group thousands of invoice records by client and calculate each client’s total invoice amount.
A PivotTable groups identical Client Name and Client ID values into one row. Adding Amount to the Values area and using Sum produces the total invoice value for each client.
Keep the source range complete so every transaction is included in the summary.
In WPS Spreadsheets, click any cell in the invoice table, or drag across the full range including the header row and all 10,000 transactions.
Open the Insert tab and click PivotTable. Confirm the complete source range, choose a new worksheet or an existing location, and select OK.
In the PivotTable Fields pane, drag Client Name and then Client ID into the Rows area. WPS will group duplicate client records into unique row combinations.
Drag Amount into the Values area. Open its value settings, choose Sum if necessary, and confirm to display the total invoice amount for each client.
Sort source transactions by client and descending amount
Use this method when the underlying invoice records must be organized for review in addition to the PivotTable summary.
Build Client Invoice PivotTables in WPS Spreadsheets
WPS Spreadsheets can create a PivotTable from the complete invoice range, group records by Client Name and Client ID, sum the Amount field, and apply multi-level sorting to the source transactions.
- 1. Open the workbook: Launch WPS Office, open the invoice workbook, and select the worksheet containing the complete transaction table.
- 2. Insert a PivotTable: Select the source range, open Insert, choose PivotTable, confirm the range, and click OK.
- 3. Configure the summary: Place Client Name and Client ID in Rows, place Amount in Values, and set the value calculation to Sum.
- 4. Sort the transactions: Select the source range, open Data > Sort, sort by Client Name, add a second level for Amount, and choose Largest to Smallest.
Frequently Asked Questions
How do I show each client only once in the invoice PivotTable?
Create a PivotTable and drag Client Name and Client ID into the Rows area. Repeated source records are grouped into one row for each unique client and ID combination.
Why does the PivotTable count amounts instead of adding them?
In the PivotTable Fields pane, open the Amount field in the Values area, choose the value settings option, and change the calculation from Count to Sum. The Amount column must contain numeric values.
Can I summarize invoices by Client Name and Client ID together?
Yes. Add Client Name above Client ID in the Rows area. This creates a client-level hierarchy and preserves the client ID beneath the corresponding name.
How can I sort invoice records by client and highest amount?
Select the complete source range, open Data > Sort, set the first level to Client Name, click Add Level, set the second level to Amount, and choose Largest to Smallest.
Does the PivotTable include all 10,000 invoice transactions?
It will if the PivotTable source range includes every transaction row and all required columns. Confirm the range in the PivotTable creation dialog before clicking OK.
