How to create custom PivotTable Groups

The PivotTable grouping option in Microsoft® Excel® allows you to see summaries of data by grouping it together so that less detail is shown. Grouping can be done automatically on date fields and the data summarised by days, months, quarters or years. However, you can also create your own custom groups. For instance, you can group your expenses by reporting categories, which is what we’ll demonstrate in this tip.

Note: Download the workbook to practice this exercise

Applies To: Microsoft Excel 2007, 2010 and 2013

The example PivotTable has been populated with expenses and amounts.

tip-1

 

 

 

 

 

 

 

 

 

To create the grouping for Admin expenses:

  1. Select all the expenses related to admin.
  2. Right click on one of the selected expenses.
  3. Select Group.
  4. Select the cell in the PivotTable now named Group 1 and rename it to Admin in the formula bar.

tip-2

 

 

 

 

  1. Repeat step 2 for all subsequent grouping levels to be created.
  2. To remove the grouping, right click on the group name and select Ungroup.

tip-3

 

 

 

 

 

 

The PivotTable will thus be set up with the different grouping levels summarising the data.