Course / PivotTables

PivotTables: dates by month and quarter

Turn 24 daily dates into months and quarters, then add regions across the top for a quarterly report. Intermediate · 5 min · PivotTable, Group dates, Months, Quarters

Daily dates make a long, spiky PivotTable. Grouping rolls them up into months, quarters or years, which is how reports are usually read.

The PivotTable at H1 shows only the Sum of Sales so far. You'll add the dates, group them, and build a quarterly report by region.

In Excel, right-click a date in the PivotTable and choose Group, then pick Months, Quarters or Years. Recent versions often group dates by themselves when you add them.

Steps

  1. Click a cell in the PivotTable and tick Date. You get one row per order date: 24 rows.

  2. Click the ▾ on Row Labels in H1 and set Group dates by to Months.

  3. Roll it up further: change Group dates by to Quarters.

  4. Finish the quarterly report: drag Region onto Columns.

Remember

Previous: PivotTables: sort, top 3 and filters · Next: GROUPBY and PIVOTBY: PivotTables as formulas