Default date aggregation for Excel

Question

What is the default behavior of adding a date, time, or datetime into an Excel pivot row/column? I have seen it sometimes add it as the "raw value", sometimes it will add it as a Year > Query > Value, and other times (?) perhaps in between. For example:

When does Excel add it without aggregating it, and when does Excel aggregate it? Does it have to do with value cardinality, date range, or something else?

Answer 1

First, every entry in the column has to be a date/time or you won't be able to group them. In that case, obviously, the default would be not grouped.

Assuming everything is groupable, the default is no grouping. Each date will show individually.

The exception is if a pivot cache already exists. In that case it will group based on what the pivot cache says - the last way that field was grouped. This happens when you have more than one pivot table on the same data. The first pivot table creates the cache and all subsequent pivot tables use that existing cache.

In a new workbook (2010), I add a date field to the Row Labels and they are initially ungrouped by default.

I group them by month

Now I go back to the original data and make a new pivot table. I add the date field to the Column Labels.

Because it uses the same cache, it automatically has them grouped the same way. Finally, I go back to the source data and replace one of the dates with a string. If I create another pivot table, it will look like the others. But when I refresh it ungroups them because I have a non-date in there.

And if I try to Group now, it says "Cannot group that selection"

That's why it works the way it does - shared pivot cache. There are ways you can give each pivot table it's own cache but that uses more memory. However, if you want to group the same data differently, that's what you have to do.