Chapter 17 · 17.6
A pivot table by month, quarter or week
Watch it in a short video
You've created a pivot table from a sales list (see Pivot tables) with the date under Rows. The result is an endless list with one line per day. What you want is a total per month or per quarter, per year. You do that with Group.
As an example, we'll use the sales of Bloemisterij Het Zonnetje for 2025 and 2026: the date in A, the product in B, the category in C and the amount in D.
Step 1: the pivot table
- Select the list including the header row, only the filled rows, and on the Insert tab choose Pivot Table. In the Create pivot table window, leave New sheet selected and click OK.
- On the right, under Select fields, tick the Date field. It goes to Rows.
- Click the arrow to the right of the Amount field and choose Add to values.

If you then see Count of Amount instead of Sum of Amount, the pivot table is counting the number of sales instead of adding up the amounts. This happens when you've also selected empty rows below the list. Right-click a number in the pivot table and choose Summarize values by, Sum. If it stays on Count, create the pivot table again and select only the filled rows.

Step 2: group by month
- Right-click a date in the pivot table and choose Group.
- In the Grouping window, Starting at and Ending at show the first and last date. Leave them as they are.
- Under By there's a list of options; Months is already selected. Hold down Ctrl and also click Years, so both are selected. If you click without Ctrl, you replace Months with Years.
- Click OK.

Why Years as well? If you choose only Months, the pivot table adds January 2025 and January 2026 together in a single Jan line.

By quarter
If the pivot table is already grouped by month, you can't simply group it again: if you click OK, nothing happens and the window goes back to Months. So remove the old grouping first.
- Right-click a month in the pivot table and choose Ungroup. You'll see one line per day again.
- Right-click a date and choose Group.
- Click Quarters and, holding Ctrl, also Years, then click OK.
You get the quarters for each year, which Euro-Office calls Qtr1 to Qtr4.


Compare with last year
Right-click an amount in the pivot table and choose Show values as. Options include % of grand total, Difference from and % Difference from. The last one shows you, for example, how many per cent higher or lower one quarter is than another.

By week
In the Grouping window, choose only Days and set Number of days to 7. The weeks then start on the date under Starting at; enter a Monday there if you want weeks from Monday to Sunday. Note: these are blocks of 7 days, not ISO week numbers. If you want real week numbers, add a helper column with the week label to the list (see ISO week numbers, including around the turn of the year) and put that under Rows.

Remove the grouping
Right-click a month or quarter in the pivot table and choose Ungroup. The same menu also contains Group and Refresh.

Pitfalls
- A (blank) line and Count instead of Sum: you've also selected empty rows below the list. Select only the filled rows.
- New sales don't appear: a pivot table doesn't refresh automatically. Right-click in the pivot table and choose Refresh.
- Group does nothing: there's already a grouping. Choose Ungroup first and then group again.
