Pivot Table Example: Sales by Salesperson

This article walks through a complete pivot table example that summarizes sales by salesperson, showing total revenue, order count, and average sale per rep. You’ll see the source data, the step-by-step pivot setup, and how to read the results for real business decisions.

What a Pivot Table Does for Sales Data

A pivot table is a data summarization tool in spreadsheet software like Microsoft Excel, Google Sheets, and LibreOffice Calc. It takes a flat list of raw transactions and lets you group, count, sum, or average values by categories you choose. For sales data, the most common pivot is “sales by salesperson” — it answers the question: who sold how much, and how many deals did each rep close?

The raw data typically looks like this (one row per sale):

• Date
• Salesperson name
• Region
• Product category
• Units sold
• Unit price
• Total sale amount

With a pivot, you drag “Salesperson” into the Rows area and “Total Sale” into the Values area. The pivot automatically sums every sale for each rep and displays a clean table with one row per person.

Step-by-Step: Building the Sales-by-Person Pivot

Here is the exact process in Excel (the steps are nearly identical in Google Sheets):

1. Select any cell inside your sales data table.
2. Go to Insert > PivotTable (or Data > Pivot table in Sheets).
3. Choose where to place the pivot — a new worksheet is usually best.
4. In the PivotTable Fields pane, drag Salesperson to the Rows area.
5. Drag Total Sale to the Values area. It defaults to “Sum of Total Sale.”
6. Drag Order ID (or any unique transaction ID) to the Values area a second time. Change its value field setting to Count — this gives you deals closed per rep.
7. Drag Total Sale to the Values area a third time, then change its setting to Average for average deal size.

The resulting pivot shows each salesperson’s name, their total revenue, the number of orders, and the average order value — all in one compact table.

Concrete Example with Numbers

Suppose your raw data has 100 transactions across five salespeople. After building the pivot, you might see:

• Alice: $52,400 total, 18 orders, $2,911 average
• Bob: $48,900 total, 22 orders, $2,223 average
• Carol: $61,200 total, 15 orders, $4,080 average
• David: $39,800 total, 20 orders, $1,990 average
• Eve: $55,600 total, 25 orders, $2,224 average

Total revenue is $257,900. Carol has the highest average deal but the fewest orders; Eve has the most orders but a lower average. This immediately shows that Carol may be focusing on larger accounts while Eve is moving higher volume — a useful insight for sales management.

How to Read the Pivot Output

The pivot table gives you more than a ranking. The Sum column tells you who brings in the most revenue. The Count column tells you who is most active. The Average column reveals deal quality. Comparing these three numbers can highlight:

• High revenue with low count = few big deals (risk if one falls through)
• Low revenue with high count = many small deals (possible discounting)
• High average with low count = strong qualification or premium pricing

You can also add a Region field to the Columns area to see each rep’s performance by territory, or add Date to the Rows area (below Salesperson) to see monthly trends per rep.

Common Mistakes and How to Avoid Them

Several frequent errors trip up new pivot users:

• Counting text instead of numbers: If you drag a text column like “Product” into Values, it counts cells rather than summing revenue. Always drag the numeric amount column for sums.
• Blank or duplicate salesperson names: If the raw data has typos (e.g., “Bob” vs “Robert”), the pivot treats them as separate people. Clean the source data first.
• Not refreshing after adding data: Pivots don’t automatically include new rows. Right-click the pivot and choose Refresh (or change the source range to a table).
• Misreading the Grand Total: The bottom row shows the sum of all reps — it’s a check, not a rep’s number.

Alternatives to a Pivot Table

If you don’t want to use a pivot, you can get the same result with other tools:

• SUMIF/COUNTIF formulas: In Excel or Sheets, =SUMIF(A:A,"Alice",F:F) sums all sales where column A equals “Alice.” This works but requires one formula per salesperson.
• Group by in SQL: If your data is in a database, SELECT salesperson, SUM(amount), COUNT(*) FROM sales GROUP BY salesperson gives the same table.
• Business intelligence tools: Power BI, Tableau, or Looker Studio can build the same aggregation with drag-and-drop and add charts.

Pivots remain the fastest for ad-hoc analysis because they require no formulas and update instantly when you drag fields around.

Frequently Asked Questions

Why does my pivot show “Count of Total Sale” instead of “Sum”?

This happens when the Total Sale column contains blank cells or text values. Excel defaults to Count when it detects non-numeric data. Fix the source data by ensuring every sale amount is a number, then refresh the pivot. You can also manually change the value field setting to Sum.

Can I show sales by salesperson and by month in one pivot?

Yes. Drag Date into the Rows area below Salesperson. Excel will automatically group dates by month if you right-click the date field and choose Group. This gives you a two-level hierarchy: each rep’s name expands to show monthly totals.

How do I add a percentage column for each rep’s share of total sales?

Drag Total Sale to the Values area again, then right-click it and choose Show Values As > % of Grand Total. This adds a column showing each rep’s contribution as a percentage, which is helpful for spotting concentration risk.

What if my data has multiple regions per salesperson?

Add Region to the Columns area (or to Rows below Salesperson) to break down each rep’s sales by region. This shows whether a rep’s performance is consistent or driven by one territory.

Can I filter to only show reps above a certain revenue?

Yes. Use a Value Filter: click the dropdown arrow on the Row Labels, choose Value Filters > Greater Than, and enter a threshold like 50000. The pivot will hide any rep whose total falls below that amount.

Why is my pivot table not updating when I add new sales?

Pivots are snapshots of the data at the time they were created. If you add rows to your source data, you must refresh: right-click the pivot and choose Refresh, or press Ctrl+Alt+F5 (Excel) to refresh all. For automatic updates, convert your source range to a Table (Ctrl+T) so the pivot recognizes new rows.

Related

• Deer Water Heater Calculator: Estimate Tank Size
• Electric Water Heater Calculator: What It Can Estimate
• Capacitor Voltage From Current: Formula and Example