Creating a Pivot Table with Grouped Dates
Imagine that you have the output from an accounts system: a table of payments made by your company. For each row of the table in Excel you have:
the supplier code the amount of the purchase the date the purchase was made
You want to see the trend over time for each supplier, so you create a pivot table showing this data. To do this (at least for Microsoft Excel 2010 and Excel 2016):
Select a single cell in your table. Click on the INSERT tab in the ribbon, and choose to insert a pivot table. Choose OK to choose all of the default options (this will create a new worksheet). Drag the supplier code onto the ROW LABELS section of the pivot table, the purchase date onto the COLUMN LABELS section and the amount of the purchase onto the VALUES section.
You should now have a pivot table with the supplier codes going down the left-hand side, the unique dates going across the top and the total spending for each combination of date and supplier as values in the middle. We now want to group the dates together into months, quarters or years.
To do this, click on the first date in your column headings. Now click on the Data tab on the ribbon, and choose GROUP. A dialog box should appear, and you should be able to choose to combine dates together into months and/or quarters and/or years. Hey, presto! an instant monthly or quarterly or annual reporting facility.
What Can Go Wrong
If you get the message CANNOT GROUP THAT SELECTION when you do the above, the reason is almost certain that not all of your dates are dates. For example, if you have 9,999 dates and just one cell which isn’t a date, Excel will still refuse to group by your date field.
To solve the CANNOT GROUP THAT SELECTION problem, you need to persuade Excel that every single one of the dates in the date field column really is a date. To do this, first check the format – a left-aligned value can be a giveaway since Excel will right-align dates by default. Another common problem is that dates imported from a mainframe system can appear with full stops (aka periods to American readers) instead of slashes: 25.12.2099 rather than 25/12/2099.
One way to spot problems like this is to use the ISNUMBER function, which returns either TRUE or FALSE depending on whether Excel recognizes a date or not. Since Excel treats dates as numbers behind the scenes, you should look for the FALSE values returned by the ISNUMBER function and correct the corresponding dates.
One final point – Excel can be slow to recognize changes – you might find that it helps to save, close and reopen a spreadsheet before retesting grouping in your pivot table.
John Abrams a Microsoft Office expert has been working in the technology industry from the last 7 year. As a technical expert, he has written technical blogs, white papers, and reviews for many websites such as office.com/setup