# How to group data in an Excel Pivot Table 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.

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.

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

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

Category: Excel Tips

### 24 responses to "How to group data in an Excel Pivot Table 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

• Hello,
Thanks for the question. Could you please clarify what you mean by "to keep the value 0 out of this in a separate group"? You can also send a sample of what you need to get to suppor@ablebits.com so that we can assist you better.

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. Sandeep Joshi says:
5. Carla says:

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

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

6. johnny yip says:

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

7. Chaka T. says:

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

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

9. Ann says:

Hello,
I have a row of medical record numbers (MRN). Some MRNs have multiple admissions and so are repeated several times. Others only occur once. But I need to calculate average ages, lengths of stays for each of the admissions. How do I group by the medical record numbers?

10. VK says:

I have data that ranges from negative numbers to positive numbers.. I need to have it consolidated into categories of ranges like \$100,000

help?

11. Christian Ranas says:

is there anyone can help me?

i need to search the rate for every company > function

This is the format of my pivot table.
Sum of Rate
Company Function Rate
A Welder 7
B Mechanic 8
C Labour 9

Name Employer Function Rate
justin A Welder The formula will link to the pivot table

the Rate will search on the pivot table From the Employer and Function then it will show the rate.

thanks

• Alexander says:

Hi Cristian,

If you can send a sample workbook with your data to support@ablebits.com and include the result you expect to get, our support team will try to help.

12. Merced says:

I am having trouble with grouping in multiple reports. If I group one value in one report, it groups it in the other as well. How do I group the same value differently in both reports without affecting the other?

• Alexander says:

Hello Merced,

Please describe your problem in more detail and send your workbook with sample data and expected result to support@ablebits.com. We'll try to help.

13. cristine says:

Hi,

can you please help me on how to get the equal result of quarterly response when I got different numbers of data on each quarter? I want to see the flow or percentage of results, but since im having different numbers of data on a quarter, it would be really hard to differentiate the quarterly results and compare it.

14. Cristin says:

I have the same question as Merced. Was it ever answered?

15. sam says:

hi i was looking alittle help, it would be much appreciated.

i have a spreadsheet with various names addresses etc along with 4 columns of dates (which i use date range formulas) to highight the dates on the columns within the date range specified. my issue is that i want to create a summary sheet but i want the dates in 1 column and not spread over 4. (note only one name contains 1 date).
cheers

16. Will says:

Hi there,

I have arranged products manually into different groups over four levels in a pivot chart. For example:
Manufacturer>Model>Type>Item. Is there any way of adding a new item to an existing group when I add a new order/product to the source data.
Many thanks

17. Alison Bezanson says:

My question relates to manual groupings in pivot tables. Say I create groupings like the following:

0-10
11-25
26-30

Is there a way to force new values into these groupings when the data is refreshed? For example, let's say in the original data I did not have the value 22, but when I pull in new data, I now have that value. I have to edit the 11-25 group to include that value. Is there any way to do this without having to edit individual groupings?

Thank you!

Alison

18. Creyf Roderick says:

Hi,

I have succesfully grouped and renamed my date but I want to use the same groups in other Pivot table. This works fine but Exell does not use same names but uses the standard names (group 1, group 2, ...) Where can I change the group names so he uses this name in all pivot tables?

Hello,

can you please tell me how to group columns?

Example :
Raw : Item Names
Columns : Branch 1 - (Forecast / Sale / Stock / variance)
in continuation : branch 2 ... 17 with all 4 cloumns.

How to fix this with slicer style??

Would be happy to hear from you.

Thanks