Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

Excel Pivot Table -- Grouping Data

  1. Grouping Data
  2. Video: Grouping Dates
  3. Grouping Dates
  4. Grouping Dates by Week
  5. Video: Grouping Numbers
  6. Grouping Numbers
  7. Video: Grouping Text Items
  8. Grouping Text Items
  9. Ungrouping Text Items
  10. Problems when Grouping Data
  11. Video: Count Unique Items with PowerPivot
  12. Count Unique Items
  13. Grouping in One Pivot Table Affects Another
  14. Download the Sample File
  15. Pivot Table Tutorial List

Grouping Data

In a 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.

grouped numbers

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

  3. In the Grouping dialog box, select one or more options from the 'By' list.
  4. 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
  5. 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.

numbers not grouped

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

grouping dialog box

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

grouped numbers

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

grouped numbers

Video: 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.

Watch this video to see the steps for creating groups from text items. Then, rename a group, or add more items. Later, you can ungroup one or more of the groups.

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. Right-click on one of the selected items, and click Group

    ungroup one group

This creates a new field in the field list, with the original field name and a number, such as City2

ungroup one group

The new field is positioned above the original field in the pivot table layout, and the grouped items have a generic name, such as Group1.

ungroup one group

To rename the group:

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

    ungroup one group

To add more items to a group:

  1. Select all of the items in the group
  2. Press the Ctrl key, and select the items that you want to add to the group
  3. Right-click on one of the selected items, and click Group
  4. NOTE: If the group was renamed, that name will be overwritten with a default name.

ungroup one group

Ungrouping Text Items

After you manually group text items, you might want to ungroup some, or all, of the groups in a pivot field.

To ungroup a single group:

  1. Right-click on an item in the group
  2. Click Ungroup, and the group reverts to individual items.

    ungroup one group

To ungroup a single group:

  1. Click at the top of a group heading, or click at the top of the pivot field, to select all of the items

    select all grouped items

  2. Right-click on an item in the group
  3. Click Ungroup, and all of the selected groups revert to individual items.

    ungroup all groups

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.

Video: Count Unique Items with PowerPivot

In a pivot table, you may want to know how many unique (distinct) customers placed an order for an item, instead of how many orders were placed. This feature isn't available in a normal Excel pivot table (see the workaround in the next section). However, if you have the PowerPivot add-in installed, you can use it to show a distinct count for a field.

Download the sample file for this video: StoreSales2012_2013.zip

Count Unique Items

In a 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:

  1. add a column to your database, with the heading 'CustItem'
  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.

Download the Sample File

Download the zipped sample file for this tutorial

 

Pivot Table Tutorials

Pivot Table Introduction 
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007 
Custom Calculations 
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Field Settings
Filter Source Data  
Filters, Top 10 
Filters, Report Filters
GetPivotData Function
Grand Totals
Grouping Data
Layout, Excel 2007
Multiple Consolidation Ranges
Pivot Cache   
PivotTable Style
Printing a Pivot Table   
Protection  
Running Totals  
Show and Hide Items 
Sorting
Subtotals 
Summary Functions
Unique Item Count

Pivot Table Books

Beginning Pivot Tables (Excel 2007) 
Pivot Tables Recipe Book (Excel 2003) 
Pivot Tables Recipe Book (Excel 2007) 

Pivot Table Add-Ins

Pivot Power 
Pivot Play PLUS 

Pivot Table Videos

Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
GetPivotData
Group Data
Layout, Excel 2007
Report Filters, Add
Running Totals
Select Sections
Subtotals, Create Multiple
Top 10 Filters

 

Learn how to create Excel dashboards.


 

Privacy Policy

 

Contextures Inc., Copyright 2013
All rights reserved.

 

Last updated: November 23, 2013 2:19 PM