Excel PivotTable: Summarize Sales by Year and Salesperson
Question details
Create a year-by-year sales summary that also breaks down total sales by salesperson.
- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- The source data contains sale dates, salesperson names, and sales amounts spanning multiple years.
- Observed behavior
- The user needs yearly sales totals organized by salesperson without writing complex formulas.
Check that the source range has one header row with consistent columns such as Sale Date, Salesperson, and Amount. Confirm that every Sale Date is a real date value and that the Amount column contains numbers.
Create a PivotTable grouped by year and salesperson
Use this method when your sales records have consistent date, salesperson, and amount columns and you want an interactive summary.
Grouping the Sale Date field by Years lets the PivotTable combine individual transaction dates into annual totals. Place Salesperson in Columns for a side-by-side comparison or in Rows for a list-style report.
Click any cell in the sales data, or select the complete range including the headers Sale Date, Salesperson, and Amount.
Open the Insert tab, select PivotTable, confirm the source range, choose a new worksheet if desired, and click OK.
In the PivotTable Fields pane, drag Sale Date to Rows, Salesperson to Columns or Rows, and Amount to Values. Open the Amount field settings and choose Sum if another calculation is selected.
Right-click any date displayed in the PivotTable, choose Group, select Years, and click OK. The report now shows annual sales totals by salesperson.
Prepare the source as a structured table before building the report
Use this approach when the data range may expand or contains inconsistent formatting that could prevent reliable PivotTable updates.
Build a yearly salesperson sales report in WPS Spreadsheets
WPS Spreadsheets can create a PivotTable from your sales table, sum the Amount field, and group Sale Date values by year. Its familiar spreadsheet interface helps you migrate the workflow from Excel while keeping your report organized and editable.
- 1. Open the sales workbook: Launch WPS Office, open the workbook in WPS Spreadsheets, and click inside the table containing Sale Date, Salesperson, and Amount.
- 2. Insert a PivotTable: Open the Insert tab, choose PivotTable, confirm the selected source range, and click OK to place the report on a new worksheet.
- 3. Assign the report fields: In the PivotTable Fields pane, drag Sale Date to Rows, Salesperson to Columns or Rows, and Amount to Values. Set Amount to Sum through its field settings if necessary.
- 4. Group the dates by year: Right-click a date in the PivotTable, select Group, choose Years, and click OK to display yearly totals for each salesperson.
Frequently Asked Questions
Why can’t I group the Sale Date field by year?
The date column may contain text, blanks, or invalid dates. Clean the Sale Date values so every row contains a valid date, refresh the PivotTable, and then right-click a date and choose Group > Years.
Should Salesperson go in Rows or Columns?
Place Salesperson in Columns for a side-by-side comparison of people within each year. Place it in Rows for a vertically organized list that may be easier to read with many salespeople.
How do I make the PivotTable show total sales instead of a count?
Open the Amount field in the Values area, choose Value Field Settings or the equivalent field settings command, and select Sum rather than Count.
Can I use this method with several years of transactions?
Yes. Include all transaction rows in the source table, place Sale Date in Rows, and group the dates by Years. The PivotTable will create a separate annual group for each year represented in the data.
