Here is a quick tutorial on using PivotTables to count the number of times a date appears in a long list of data, plus how to fix a small quirk 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 I have a list of 2,000 dates scattered randomly from January 1, 2013 to July 27, 2015:
So, I’m going to set up a PivotTable on the same worksheet and drag the Date category to both the Rows and Values quadrants:
This gives me a list of every unique date and the number of times each appears in my list of 2,000. However, this may be a bit more granular that we would like, so I’m going to do some grouping. To do this, I just right click on any date in the list and choose Group:
This brings up the Group dialog box, and I will select Months and Years:
And now I have the dates broken down by year and sub-divided by month:
Notice how there is no subtotal next to each year. This is a minor quirk in Excel. To correct this, right-click on the year and choose Field Settings. The Field Settings dialog box appears, and you can select Automatic:
Now there is a subtotal for each year:
You can also switch the Date and Years items in the Rows quadrant to see each month broken down by year:
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!