PivotTable Drill

Exercise 9: Build a sales table by branch and month

Task

Put Branch in Rows, dates grouped by month in Columns, and the sum of Amount in Values, then sort branches by total, largest first. Format the amounts as #,##0.

Moves 0 (target 7)

FileHome

	A	B	C	D	E
1	Date	Branch	Rep	Item	Amount
2	2026/4/3	New York	Smith	Notebook	12000
3	2026/4/8	Chicago	Johnson	Pen	8000
4	2026/4/15	New York	Williams	File	15000
5	2026/4/22	Chicago	Smith	Notebook	9000
6	2026/5/2	New York	Johnson	Notebook	11000
7	2026/5/9	Seattle	Williams	Pen	6000
8	2026/5/14	New York	Smith	File	13000
9	2026/5/20	Chicago	Williams	Notebook	7000
10	2026/5/28	Seattle	Johnson	File	10000
11	2026/6/3	New York	Williams	Pen	5000
12	2026/6/11	Chicago	Smith	File	14000
13	2026/6/18	Seattle	Smith	Notebook	9500
14	2026/6/24	New York	Johnson	Pen	4500
15	2026/6/30	Chicago	Williams	File	12500
16
17
18

Put Branch in Rows, dates grouped by month in Columns, and the sum of Amount in Values, then sort branches by total, largest first. Format the amounts as #,##0.

This drill is for computer screens. On a phone you can review the steps.

Insert > PivotTable on a new sheet
Put Branch in Rows, Date in Columns, and Amount in Values
Right-click a date cell > Group > Months
"Sum of Amount" > Value Field Settings > Number Format: #,##0
Right-click an amount cell > Sort > Sort Largest to Smallest

Key takeaway

That's a summary table ready for a monthly meeting. Insert a PivotChart if you want a chart.

See Pivot Tables for details.

← Back to all exercises