Grouping is another powerful way to analyze data in Pivot Tables and it’s the topic of Excel Video 294. We’ll start our discussion of grouping data by using a date. Notice how when I click on a date in a Pivot Table Excel recognizes that it’s a date and gives me several ways to group the date. Also notice that I can choose more than one way to group. In the video I’ll group my dates by month and by year.
When I group the dates two ways, Excel automatically assigns a new name for me to track and pivot the data. In the video, my Date field has the dates grouped by month and Excel created a field called Years to hold the dates grouped by year. Now I can quickly move months and years around in my Pivot Table to analyze trends and spot opportunities.
One final note. If you’re trying this trick and Excel doesn’t give you date fields when you group your dates, make sure that the data is stored as a date in Excel. If Excel doesn’t recognize your data as dates, you may have a problem with your data. I’ve seen problems in the past where the data has dates like February 31. Make sure your dates are valid dates and this trick will be very helpful for you.
