Oct
6

How to group data in an Excel PivotTable to help further summarize and analyze your data

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

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.

PivotTable: Group by value

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.

Setting your own Starting value

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.

The data is grouped 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.

PivotTable: Group by date

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.

Grouping data manually

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.

Give your groups more descriptive names

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.


Troubleshooting Errors

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.

The data that contributed to that total will be extracted to a new worksheet.

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.

Creating groups in a PivotTable that you have created from multiple consolidation ranges

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.

Adding 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.

Assembling 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.

Finish to create groups from multiple consolidation ranges

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.

Creating groups from multiple consolidation ranges - resulting PivotTable

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

See also

9 Responses to "How to group data in an Excel PivotTable to help further summarize and analyze your data"

  1. goodmorningboy says:

    Question regarding the "group by value" section.
    I was able to group my data by tens, 50s and so on, no problem with that.
    But I need to keep the value 0 out of this, in a separate group, is there a way to do this? (not manually :D )

    ty

  2. goodmorningboy says:

    I mean that I have rows like this in my pivot table:

    -m
    ...
    -3
    -2
    -1
    0
    +1
    +2
    +3
    ...
    +n

    I need to group these in this way

    7
    08 -> 14
    15 -> 21
    ...

    So, it's something like:

    Start at 0
    End at X
    By 7

    but doing that way, the value 0 fall in the group [0 -> 6] and I want to keep it separated.
    Hope I was a bit clearer :)

  3. goodmorningboy says:

    Errata corrige:

    I need to group these in this way
    group 1: 7
    group 4: 8 -> 14
    group 5: 15 -> 21
    group ...: …

  4. Carla says:

    Hi, I would like to group using percentages. How can I do this?

    0%-5%: 20
    5%-10%: 7
    10%-15%: 24
    ....

  5. johnny yip says:

    To group your value actually u can use formular of lookup.

  6. Chaka T. says:

    How do you auto insert a page break between each of the groups above i.e Fall, Spring, Etc.

  7. Group by Data Pivot Table says:

    hi Everyone!
    I am in dire need of help. I would like to know how can I group a set of dates into month without showig the other dates of the month after clicking the filter per month.

    Appreciate anyone's help.

Post a comment



Ultimate Suite for Excel Professionals
 
 
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard