Create a Multi-Level Vendor Summary in WPS Spreadsheets with a PivotTable
Question details
Create a consolidated vendor summary that counts employees and sums current-year amounts by company, state, and class.
- Product
- WPS Spreadsheets PivotTable
- Device & OS
- not provided
- Scenario
- The source data includes approximately 100 companies with employee ID, company, state, class, and current-year total fields. The user needs grouped totals that can be filtered and refreshed as records change.
- Observed behavior
- The required multi-level vendor summary has not yet been created from the source records.
Confirm that the source range has one header row with unique labels, including Employee ID, Company, State, Class, and Total Amount. Check that Employee ID is populated and Total Amount contains numeric values before inserting the PivotTable.
Build the vendor summary with a multi-level WPS PivotTable
Use this method to group records by company, state, and class while calculating employee counts and total amounts.
A PivotTable uses nested row fields to create the vendor hierarchy. Employee ID should be summarized by Count, while Total Amount should be summarized by Sum.
Select any cell in the source data, open the Insert tab, choose Table, confirm that My table has headers is enabled, and click OK. This makes it easier to include new records when the summary is refreshed.
Click inside the table, open the Insert tab, select PivotTable, choose the table or source range, select a new worksheet as the destination, and click OK.
In the PivotTable Fields pane, drag Company to Rows, then State to Rows, and finally Class to Rows. The fields will appear as nested levels in that order.
Drag Employee ID to Values, open its value-field settings, and select Count. Drag Total Amount to Values, open its value-field settings, and select Sum.
Drag Company, State, or Class to Filters, or select the PivotTable and use the PivotTable Tools option to insert slicers for interactive filtering.
Adjust PivotTable filters and value formatting
Use this option when the PivotTable already exists but needs clearer filtering, totals, or number formatting.
Create Refreshable Vendor Reports with WPS Spreadsheets PivotTables
WPS Spreadsheets can group vendor data by company, state, and class, calculate employee counts and total amounts, and provide filters or slicers for reviewing the report. Its familiar spreadsheet interface makes it easy to refresh the summary as the source table grows.
- 1. Open the source workbook: Launch WPS Office, open the workbook in WPS Spreadsheets, and verify the source headers and values.
- 2. Insert a PivotTable: Select the source table, open Insert, choose PivotTable, select a worksheet destination, and click OK.
- 3. Arrange the fields: Drag Company, State, and Class to Rows; drag Employee ID to Values and set it to Count; drag Total Amount to Values and set it to Sum.
- 4. Add filtering controls: Place the required fields in Filters or insert slicers from the PivotTable tools to review selected vendors, states, or classes.
- 5. Refresh the report: After adding records to the source table, select the PivotTable and choose Refresh from the PivotTable tools.
Frequently Asked Questions
How should the PivotTable show company, state, and class levels?
In the PivotTable Fields pane, place Company, State, and Class in the Rows area in that order. WPS Spreadsheets will display state groups under each company and class groups under each state.
How do I count employees instead of adding employee IDs?
Drag Employee ID to the Values area, open its value-field settings, and choose Count. If the field is automatically set to Sum, change the calculation to Count.
How can I total the amount for each class and state?
Drag Total Amount to the Values area and select Sum in Value Field Settings. Make sure the source amount column contains numeric values rather than text.
Can I filter the vendor summary by company or state?
Yes. Drag Company, State, or Class to the Filters area, or select the PivotTable and insert slicers through the PivotTable tools for clickable filtering.
How do I include new vendor records in the summary?
Keep the source data as a table before creating the PivotTable. Add new records directly below the table, then select the PivotTable and choose Refresh from the PivotTable or Analyze tab.
