Now that you’ve got the fundamentals of pivot tables down – structuring data, setting up a pivot table, and adding your data – time to unlock some of the fun and power pivot tables can offer.

Often within a dataset you’ll have a column of dates. Depending on your dataset, the dates could represent transactions, traffic, or another time-specific event. The benefit of having dates associated with data is you can trend those data over time to see movement in things like sales or traffic. This helps answer those pesky questions like – is my marketing campaign working.

First, let’s get comfortable with all the information encoded in a date. It may seem obvious, but this information is what allows pivot tables to group ranges together. A common date format is month/day/year – meaning you have three pieces of information within a single cell of data: the month, the day, and the year. Pivot tables are able to recognize these three pieces of information and group them together in a number of ways: by day, by month, or by year.

By way of example, we’ll use the same dataset from Airbnb of properties in Berlin. This dataset has a date column that represents the time when a property was last reviewed. Start by setting up a new pivot table with this data – refresher on that here. Next, drag the “last_review” column into your rows field and the “id” into your values field. The resulting pivot table fields should look something like this…

And the corresponding pivot table like this…

Now before doing anything with the date rate, you’ll notice Excel decided to SUM the id value since its numeric. We want a count. To clean that piece up, right click on any value in the “sum of id” column in the pivot table. Navigate to “summarize values by” and click “count.” This column now represents the count of last reviews that were made during a particular time period.

Now let’s tackle grouping date ranges. You’ve probably already noticed that the pivot table automagically did this! Remember the date values were days, months, and years – but the pivot table output summarized the information by year. Grouping date ranges is pretty easy – simply right click on one of the date values. From there you’ll see an option to group – click on that.

Now you’ll see a dialogue box that indicates how you can group your date range. You’ll notice it can get as granular as seconds, but since that information isn’t in out pivot table, we’ll focus on years, months, and days. Within the context of this dataset, I think grouping by years and months would be the most useful, so I am going to select those by clicking months, then control+clicking year to select both. Should look something like this…

Click ok and you’ll notice two things. First, your pivot table now contains the months grouped below the corresponding year. You’ll only see months for which there is a data value from the dataset. For example, in 2011, the last reviews for the properties in the list were apparently only in January and November – likely because the service and properties were more scarce the further back you go in time.

Next thing you’ll notice is that in your pivot fields, you have a new value in your in your rows quadrant: “years (last_review).” This is automatically added since you grouped your data. Years represents – you guessed it – years, while the other value, “last_review” represents the month.

Now let’s say you want to cross reference the count of last_reviews against the month and year to see if there is a more popular month. Click and drag the “Years (last_review)” from your row into your column. Should look like this…

Corresponding pivot tables now looks like this…

This is now a super fast and easy way to see how many last_reviews were submitted across any month in a particular year. From the data, two conclusions can be drawn. First – Airbnb properties in Berlin have gotten a lot more popular between 2011 and 2018. Second, reviews seem to correspond, not surprisingly, to the busier travel times: summer months into early fall, from July through September.  

That’s pretty much the gist of it! Grouping data is a great way to draw quick, actionable insights within pivot tables. Questions – let’s hear ‘em…