Analyzing Dates With PivotTables In #Excel

Here is how you can use a PivotTable to calculate the frequency of occurrences of events in Excel.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here we have a list of 500 random names and dates from 1/1/2010 until 4/22/2014, that lists the winners of daily contests. The dates and names may occur more than once. We want to find out the frequency of each date, and see how many times there were winners in each month and year over this period:

ptd1

The first thing we will do is highlight the data, then go to the Insert tab and select PivotTable. I’ve chosen to put the PivotTable on the existing worksheet at cell E1:

prd2

Click OK and the outline of our PivotTable will appear along with the Field List:

ptd3

I will next drag the Date field to both the Rows and Values sections. This will generate our PivotTable with the dates in order and the frequency of each date in our data range:

ptd10

However, I don’t need to see this data on such a granular level, but would rather see the quantities by month and year. To accomplish this I will right-click on any date in the list and select Group:

ptd5

Another dialog box will appear from which I can select any single or multiple list of group choices. I will select Months and Years:

ptd6

This will now group the data on my PivotTable into those buckets:

ptd7

If I choose, I can go to the Rows field on the Field List and drag the Years field below the Date field to show the Month as the primary sort, and Year as secondary:

ptd8

The last step might be to drag the Winner field into the Filters area of the Field List so that I can see the winnings of any individual person. Here I’ve chosen Canssandra Banks and rearranged the Rows field back to show Years over Months:

ptd9

And that’s how you can use PivotTables to group and analyze dates and data in Excel!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

No comments yet.

Leave a Reply