Beginner level · Module 4: PivotTables and PivotCharts · Lesson 2 of 3
Grouping and Calculations in PivotTables
Group dates and numbers, add calculated fields and show values as percentages or running totals.
In this lesson you will
- Group dates by month and quarter
- Use calculated fields and show-values-as options
Key concepts
groupingcalculated field% of totalrunning total
The lesson
Group dates into months, quarters or years, and numbers into bands such as order sizes. This turns daily records into readable trends.
A calculated field creates a value from other fields, such as profit = sales − cost. Show Values As displays values as a percentage of the total, of a row or as a running total.
Sort and filter within PivotTables to find top products or customers, for example the top 10 by sales.
Step by step
- Group sales dates by month.
- Add a calculated profit field.
- Show each branch's share of total sales.
- List the top five customers.
Monthly sales by branch from a PivotTable
A PivotTable groups four months of transactions by month and branch, and a PivotChart shows the trend.
| Month | Juba | Wau |
|---|---|---|
| Jan | 210,000 | 95,000 |
| Feb | 230,000 | 101,000 |
| Mar | 198,000 | 120,000 |
| Apr | 245,000 | 131,000 |
What the data shows
Juba sells about twice as much as Wau (883,000 vs 447,000 SSP over the four months), but Wau is growing faster: up 37.9% from January to April, against 16.7% for Juba.
Juba's dip in March, while Wau kept growing, is worth investigating — a stock shortage or a closed day would show up in the underlying transactions.
Recommendation
Add a slicer for product category to see which products drive Wau's growth and Juba's March dip, then double-click the March Juba cell to drill into the transactions behind it.
Practice activity
Answer the remaining practical questions with grouping and calculations.
Expected result: Correct trends, shares and top lists, with one figure checked by SUMIFS.
Check your understanding
-
How do you see sales as a share of the total?
Show answer
Use Show Values As > % of Grand Total.
-
What is a calculated field?
Show answer
A PivotTable value computed from other fields.
Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.