Excel Pivot Tables – Grouping Data by Date and Time

May 25, 2020 Excel tips,

Excel Pivot Tables – Grouping Data by Date and Time

Where reporting is about numbers and analysis, the big part of it is also about time. And to best understand the situation you sometime have to want information specific to certain time period or grouped for certain period of time. Excel is good and the best thing happened to Excel is Pivot Tables and one of the best things of pivot tables is grouping. And this is exactly what we are learning today!

Download Practice Excel workbook

To best understand the techniques in this tutorial is to actually do the steps. To help you out, download this workbook and you can carry out the steps on the data to learn at best

Making Pivot Table travel time

So what it does is apparent from the name, it aggregates the information on the basis of days, months, weeks, years, quarters etc. With pivot tables it is really easy and let you quickly summarize the report on the basis of time and you can make reports with many different dimensions of time. OK enough of the verbose!

Step 1: Open the workbook you downloaded and you have two worksheets, source and pivot table. Pivot table is already made for you, but if you like to learn how to do it then read creating pivot tables tutorial. And once you have it continue to Step2.

Step 2: I kept the pivot table report to a minimal state for better understanding. So I have date in the row labels box and units sold field in the value box.

You must have a really really long report by now. But this doesn’t help much as report is giving sales by each day. It will be great if we can show a report by month. To do this continue reading.

Step 3: Right click on any date and from the menu select Group. A new dialogue box will appear. Make sure month option is highlighted and click OK.

Pivot table report will immediately update to report sale of units on monthly basis. However, there is one problem with it. For example sales of January are actually sales of all the years in the month of January. It will be better if we report months under respective years.

Step 4: Right click on any month’s name, same dialogue box will appear, this time click year as well. Noticed something??? Yes this is basically a selection box, and you can make multiple selections by simply clicking the option. To deselect any already selected, simply click it again and it will be deselected telling that data won’t be grouped on these basis. Click OK

And now the months are grouped under years in no time. You can play around with this little technique to group the data in many different ways. Like we can make a report on quarterly basis as well. Following examples show you the many other ways in which you can group the data on different units of time.

Group by weeks

This is certainly good way to convert daily data into a weekly data to reduce the clutter of days. It makes much more sense if it is grouped by week. You can do this very easily using pivot tables.

Step 1: Right click on any time variable, year or month > Group

Step 2: From the dialogue box deselect every other option excel days .

Step 3: In the number of days box put 7 using spinner arrows or by typing 7 from the keyboard. Click OK and the data will be grouped on the basis of 7 days which is a week.

Group by hours of the day

You can even group the data by hours. But that would be necessary only if you want data is of sensitive nature and relevant information is available.

Step 1: Right click any time variable and from the menu select group

Step 2: From the dialogue box select hours. Click OK and the data will be grouped on the basis of hour of the day.

But if you do so, you will get a single time i.e. 12:00 AM because dates were generated randomly in the data provided and in the case every date had the same time.

Important to note

You might have noticed that if multiple options are selected in group dialogue box, a new field is added in the pivot table fields pane to the right. For example, if you select year and months, a separate field with the name Year is inserted. As this is a pivot table field, you can move it to other boxes to further fine tune the report. For example, if you move year field to column label box then you get a better presentation.

Time Warps!

For those who are not a fiction movie maniacs unlike me, warp is a distortion or getting hung in the time and not being in the real space. In human language, even with all the gymnastic moves pivot table can do there are some that it cannot do or at least I don’t know about them yet.

For example if I want to show data by weeks under months or quarters, there is no easy way to do it.
With the data already grouped as years or months or quarters, if I manually want to group the data, again it is not possible.

But the above issues did came into my notice and while I was writing this article I did came up with a solution which I will be sharing with everyone in my next article. There must be something left for the next time right?