digest
Short Summary
Main recommendation: In Excel 2016 and later, PivotTables automatically group date fields by Years, Quarters, and Date (months) when a date field is dragged into Rows, so you can roll up revenue into monthly reports without manually grouping. The video demonstrates building this with a dataset of about 2,000 records spanning two years, then customizing the layout.
Key Points
- Excel 2016 automatically creates Years, Quarters, and Date groupings when a date field is added to Rows, a change from earlier versions where you had to right-click and choose Group to get that grouping.
- The default behavior shows only the unique individual dates, so daily detail requires right-clicking the field and choosing Ungroup.
- The host calls this new automatic grouping surprising and says it happened to them the first time they used a PivotTable date grouping.
- Field order in the Rows area can be adjusted, with the grouped date levels shown together.
Details Worth Noting
- Concrete instructions:
- Click a single cell in the data range and insert a PivotTable (Alt+V shortcut is used in the video), placing it on an existing worksheet at a chosen location such as F4.
- Drag the Date field into Rows. Excel automatically shows Years, Quarters, and Date.
- To get daily detail instead, right-click the date field and choose Ungroup (press Ctrl+Z to undo if you want the grouped view back).
- To get a year-then-month report, drag Year on top of Date in the Rows area, so Year appears first and months beneath it.
- Drag Revenue to Values, then right-click it and use Number Formatting (not Format Cells) to set Currency with two decimal places, which applies formatting to the field itself.
- To change the layout, go to Design, Report Layout, and choose Tabular or Compact. Tabular puts the date label in its own column, and the host suggests renaming the header from "Date" to "Month" or a similar label.
- To rename the Row Labels header in Compact layout, type a new name such as "Date" over it.
- The video references a workbook download linked below the video for following along.
- The dataset in the demo is roughly 2,000 records covering two years (2016 and 2017).
Bottom Line
Excel 2016's automatic date grouping lets you build year and month summaries in a PivotTable immediately, with simple steps to switch to daily detail or adjust the layout.
