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:
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:
Click OK and the outline of our PivotTable will appear along with the Field List:
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:
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:
Another dialog box will appear from which I can select any single or multiple list of group choices. I will select Months and Years:
This will now group the data on my PivotTable into those buckets:
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:
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:
And that’s how you can use PivotTables to group and analyze dates and data in Excel!