I was hoping to post a final post on Pivot Tables and then to move on to creating a dashboard in excel. But there is just so much to learn about pivot tables. I couldn’t publish a post sooner as I had so many other things going on at the same time and there was just not enough time to listen to the next lecture from the course that I am taking through EdX. In this post, I am going to talk about how to create groups in Pivot Tables and in the next post, I am hoping to cover charting in pivot tables. Let’s get started…
When analyzing data in an Excel Pivot Table, it is often useful to group the Pivot Table data into categories. In a pivot table, we can group dates, number and text fields. We can manually select text items in a pivot table field, and group the selected items. This lets us quickly see subtotals for a specific set of items in the pivot table. I am using the
For the sake of this post, I am using the data file given in the lecture.
Group a Pivot Table by Date:
I have created a pivot table from the data provided on a separate sheet. The pivot table contains only two columns: ‘Date’ and ‘Revenue’.
In order to group date column by month, we will follow these steps:
- Right-click in one of the cells in dates column and select ‘Group‘.
- You will be presented with the ‘Grouping’ dialog box for dates.
- Select the value Months and click OK.
Group a Pivot Table by Range:
I created another pivot table from the same data and added “Customer Age” and “Revenue” to the table this time.
- Right click on one of the cells containing the ages and select the option, Group.
- Excel will automatically enter the minimum and maximum values from the data.
- I want to group the ages into 5-year periods, so we change the value in the By box to a 5.
- Click OK.
Ungrouping a Pivot Table:
To ungroup the values in a pivot table, just right click in one of the cells in the grouped column and select the option Ungroup.
The concept of grouping in the pivot table is easier but it was difficult when I did not know how to do it. I will try to come back soon with charting in pivots. Thanks for reading my post.