Helen Bradley explains how to group data in Excel PivotTables to help further summarize and analyze your data.
When you're working with a PivotTable in Excel you may find that you have a lot of data in the table that needs to be summarized even further. This can be done by grouping the data and in this post I'll explain how to do this.
- Group by value
- Group by date
- Grouping data manually
- Troubleshooting Errors
- Drill down into groups
- Grouping and Multiple Consolidation Ranges
Consider the situation where your PivotTable data includes a series of invoices that you are analyzing. You can summarize the invoices by grouping the invoices together into groups of 5,10 or even 100 invoices. So, consider this example where the Order ID is a row label and a numeric field. Right now there is one row for each order which is cumbersome - we can group these to simplify the table.
In this PivotTable each individual OrderID is represented in one row of the table, this is summarized data but not highly so.
To do this, right click one of the Order IDs in the PivotTable and choose Group. As the Order ID is a numerical field the dialog will be already configured for numerical grouping with a Starting At, an Ending At and a By value. Right now Excel is suggesting you group the items in multiples of ten starting at the Starting value.
The default Starting At value that Excel offers is the first OrderID: 10248. However you can create a neater or different grouping by setting your own Starting At value. To start at 10240, type this value into the Starting At box - you can set the starting point wherever you like even if that value doesn't exist in your data.
Here we're setting up the parameters for grouping the data - it will be grouped by OrderID into groups of ten consecutively numbered orders.
Here we've set the Starting At value, we've left the Ending At value for Excel to manage and we've left the By value at 10 as this works for our data - you can make groups larger or smaller by changing this value - to, say 5, 20, 50 or 100 and then click Ok. The data is now grouped into more manageable chunks based on the OrderID.
Here is the data grouped by OrderID so it's more summarized than before.
Group by date
If the data that you are looking at is date data then you can do something similar. To group the data by date, right click on a date in a column or row of your PivotTable and choose Group. You can group by Seconds, Minutes, Hours, Days, Months, Quarters or Years and set the starting and ending times. For groupings like Year and Month the interval is set to 1, but for Days you can set your own interval so you can group at an interval of 7 Days to group data into weeks. To do this, select Days as the grouping value, set the number of days to 7, set the Starting At value to a date that you know to be the beginning of a week and click Ok. You'll then have data grouped by week.
A large amount of data is to be grouped into weeks (7 days), starting on a Sunday - the day of the Starting At date.
Grouping data manually
Sometimes it's not possible to allocate a group to a series of records as simply as describing a numerical sequence or a date sequence. Sometimes a group is a group simply because you say that it is so. In this case, you can create your own groupings for your data. With the PivotTable on the screen, click in the first item of data that is part of a group and then Ctrl + Click on each other item that belongs in that group. From the PivotTable Options tab on the Ribbon, select Group Selection and those items will be added to a new group. You can now go ahead and select other row entries that belong to the next group and create a group for them.
When you have multiple field names selected you can group them into groups that make sense to you.
Using this tool you can group data into smaller collections that make sense to you. Because the group names are, by default, Group 1 and Group 2, you will need to edit these to rename them - to do this, click in the cell that contains the group name and in the Formula Bar type over the group name with a more descriptive name.
Once you have created the groups you can name them anything you like.
You can Collapse and Expand groups by clicking on the plus/minus (+/-) symbol to the immediate left of the group name.
Sometimes, when you try to group a selection you'll encounter an error message saying that you cannot group that selection. There are a number of reasons this might happen. One is that, if you are trying to group data into your own custom groups you must select two more entries to create a group - you cannot create a group from just one entry.
If there are blank cells in a field such as a date or number field, then you will get an error message because of this. You will also encounter an error if you have a text entry in a date or numeric field. In each case, check the original data and fix the problem then refresh the PivotTable before trying again.
Drill down into groups
One benefit of grouping your data is that you can extract a subset of the grouped data into a new worksheet. To do this, locate the group and double click in the Total cell containing the value you are interested in. The data that contributed to that total will be extracted to a new worksheet in the workbook.
Double clicking on any value in the PivotTable creates a new worksheet containing all the data that contributed to that value.
Grouping and Multiple Consolidation Ranges
It's also possible to create groups in a PivotTable that you have created from multiple consolidation ranges such as data on multiple sheets in a workbook. To do this in Excel 2007 and 2010 you'll need to add the missing PivotTable and PivotChart Wizard tool to the Quick Access Toolbar (or the Ribbon in Excel 2010).
To do this, click the Quick Access Toolbar dropdown button, select More Commands, locate the All Commands group and scroll to find the PivotTable and PivotChart Wizard. Click Add to add it to the Quick Access Toolbar. You can now create a PivotTable from a series of sheets that are all similarly laid out. You'll need data that has the same number and same column headings although each sheet may contain different numbers of rows of data.
Omitted from Excel 2010, you can add back the PivotTable and PivotChart Wizard back into Excel by adding it to the Quick Access Toolbar.
To do this, add a new worksheet to your workbook, click your new PivotTable and PivotChart Wizard button and select Multiple Consolidation Ranges and PivotTable and click Next. Select 'I will create the page fields', and click Next. Now you'll select each of the ranges so go to the first worksheet and select all the data including the table headings and click Add. Repeat this to add the data from the next sheet and continue until you've added the ranges for all the worksheets that you want to use in this PivotTable.
The PivotTable and PivotChart Wizard lets you assemble PivotTables from multiple ranges.
In the PivotTable and PivotChart Wizard you need first to select the data ranges to use.
Next select the number of page fields to add - typically you'll want one or two. Now, select each range in the panel above and type the group name(s) to associate it with. Select the next range in the panel above and do the same for it - if the group name already exists you can select it from the dropdown list instead of typing it.
So, for example, a date range may be a part of a group which splits months into Quarters and it may also be part of a group that splits months into seasons: Winter, Spring, Summer and Fall. The groupings can be anything that makes sense to you for grouping your data. Click Next, click in the cell which will contain the top left corner of your PivotTable and click Finish.
In the PivotTable and PivotChart Wizard you can assign ranges to pages such as Quarters and Seasons as shown here.
The PivotTable will be created automatically and the groups will be created as Report Filter fields in the Field list although you can drag the fields into the Row Labels or Column Labels panels if desired.
Here we've moved the Seasons page from the Report Filter to be a Row Label.
Whether you group data in an already created PivotTable or whether you create them at the time you create a PivotTable from multiple data ranges, the outcome is the same and you have yet another tool in your toolkit for summarizing data in Excel PivotTables.img title="Grouping data manually" src="http://www.ablebits.com/_img-blog/excel-pivottable-grouping/group-manually.jpg" alt="Grouping data manually" /dropdown button, select
- Merge two excel spreadsheets
- Vlookup, hlookup and lookup functions in Excel 2010
- How to add multiple subtotals to one Excel table