Filters and grouping by month
People usually look at revenue by month, not by day: how much was earned in January, how much in February. But if you build a PivotTable from a column of dates, Excel shows each day as a separate row. Here's what that looks like for orders from early 2025 and early 2026:
Days add up to months
In Excel, adding days up into months is called grouping dates. Newer versions of Excel often do it by themselves: put a date field into Rows, and the report is already split by year and month. If the report still shows individual days, as above, right-click any date in the report and choose Group….
The Grouping dialog opens. In its By list, you select what to add the dates up by, and you can select several items at once:
- Months;
- Quarters;
- Years.
The diagram shows twelve orders from the same table, and you can see which of them land in each report row with different steps:
The Months step looks only at the month and ignores the year. That's why January 2025 and January 2026 end up in the same row. If the table holds data for several years, select Months and Years together.
Here are the same forty orders as at the start of the lesson. Their report is grouped with the Months step alone, and the "Jan" row adds up both Januaries. Add years to the months:
Only real dates can be grouped. They're aligned to the right edge of the cell. A date at the left edge is stored as text, and the PivotTable won't group it.
Report filter
Sometimes you need a report not on all orders, but on just one city. There's no need to copy the table and delete the other cities from it: the report has a Filters area.
A field in Rows splits the report into rows. A field in Filters doesn't add any rows: it decides which orders get into the report at all. That's why a filter changes all the numbers at once, Grand Total included.
The report by category is already built. Keep only the orders from the cities you need in it, and you can pick more than one:
Revenue by month and one city's revenue by category both come from a single table, with no copies and no adding up by hand.