Contextures
Excel news
by email

# Excel Pivot Table Tutorial -- Grouping Data

## Grouping Data

In an Excel pivot table, you can group dates, number and text fields. For example, group order dates by year and month, or group test scores in bands of 10. You can manually select text items in a pivot table field, and group the selected items. This lets you quickly see subtotals for a specific set of items in your pivot table.

## Video: Grouping Dates

To see the steps for grouping dates, please watch this short video tutorial. There are written instructions below the video.

## Grouping Dates

To group the items in a Date field

1. Right-click on one of the dates in the pivot table.
2. In the popup menu, click Group

1. In the Grouping dialog box, select one or more options from the 'By' list.
2. To limit the dates that are grouped, you can set a Start and End date, by typing the dates in the 'Starting at' and 'Ending at' boxes
3. Click OK to close the dialog box.

## Grouping Dates by Week

To group the items in a Date field by week

1. Right-click on one of the dates in the pivot table.
2. In the popup menu, click Group
3. In the Grouping dialog box, select Days from the 'By' list.
4. For 'Number of days', select 7
5. The week range is determined by the date in the 'Starting at' box, so adjust this if necessary. In the example below, January 1, 2012, was entered as the starting date.
6. Click OK

## Video: Grouping Numbers

Use grouping to create a concise summary, instead of showing a long list of individual numbers. This video shows you the steps, and the written instructions are below the video.

## Grouping Numbers

Instead of listing numbers individually in a Row field, you can group them, to create a concise summary of the data.

In this example, the original pivot table shows the units sold, per product. In column C, it shows the number of orders where that quantity was sold.

To summarize the orders, group the units by tens. So, instead of 3 and 9 appearing in separate rows, they'll be summarized in the grouping 1-10.

To group the numbers

1. Right-click on one of the unit numbers in the pivot table.
2. In the popup menu, click Group
3. In the Grouping dialog box, enter 1 in the Starting At box
4. In this example, the highest number of units is 50, and you can type a higher number, if necessary.
5. Click OK, to apply the grouping

In the pivot table, the Units now appear in groups -- 1 - 10, 11 - 20, etc.

To make a more concise layout, you could move the grouped units field to the Columns area, as headings.

## Grouping Text Items

The Grouping dialog box isn't available if you try to group items in a text field. Instead, you can manually group the items.

1. Select the items that you want to group
2. On the Ribbon's Options tab, click Group, then click Group Selection

#### To rename the group:

1. Click on the heading cells with the default name, e.g. Group1
2. Type a new name.

## Problems when Grouping Data

If you try to group a date or number field, you may see an error message that says, "Cannot group that selection."

This problem usually occurs when the field contains records with a blank date/number field, or text in a date/number field. To fix the problem

• For blank cells, fill in the date/number (use a dummy date/number if necessary).
• If there is text in the date/number field, remove it.
• If numbers are being recognized as text, use one of the techniques to change text to real numbers. Then return to this pivot table tutorial, and try the grouping steps.

If you don't have blank cells or text in the date column, there may be a grouped field left over from the previous time that you grouped the data.

1. Check the field list, to see if there's a second copy of the date field, e.g. Date2.
2. If there is, add it to the row area, and ungroup it.
3. Then, you should be able to group the date field again.

## Count Unique Items

In an Excel pivot table, you may want to know how many unique customers placed an order for an item, instead of how many orders were placed. A pivot table won't calculate a unique count. However, you could use one of the following workarounds:

1. Use PowerPivot to create the pivot table, and use its functions to create a unique count. See the details here
2. Add a column to the database, then add that field to the pivottable. Follow the instructions below

### Add a Column to Calculate Unique Counts

For example, to count the unique occurences of a Customer/Item order:

2. In the first data row, enter a formula that refers to the customer and item columns. For example:
3.   =IF(SUMPRODUCT((\$A\$2:\$A2=A2)*(\$C\$2:\$C2=C2))>1,0,1)

4. Copy the formula down to all rows in the database.
5. Then, add the field to the data area of the Excel pivot table.

In this example, you can see that 7 unique customers placed an order for binders, and there were 13 orders for binders.

## Grouping in One Pivot Table Affects Another

If you create two pivot tables based on the same Excel Table in Excel 2007, when you change the grouping in one pivot table, the same grouping appears in the other pivot table.

Because you created the two pivot tables from the same source data, by default they use the same pivot cache, the grouped items are the same in both tables.

To use different grouping in each pivot table, you'll need to create a separate pivot cache for each pivot table.

Use the following method, suggested in the Excel newsgroups by Dave Peterson.

To create a separate pivot cache for the second pivot table:

1. Cut the second pivot table, and paste it into a new workbook.
2. Change the grouping of the second pivot table.
3. Cut the second pivot table from the new workbook, and paste it back into the original workbook.