Excel -- Pivot Tables -- Grouping Data

  1. Grouping Dates
  2. Grouping Dates by Week
  3. Problems when Grouping Data 
  4. Count Unique Items

Download the zipped sample file

 

In a Pivot Table, you can group the items in a Row or Column field.

For example, items in a date field can be grouped by month, and items in a number field can be grouped by tens.
    

Grouping Dates

To group the items in a Date field

  1. Right-click the Date field button.
  2. Choose Group and Show Detail | 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

 

Grouping Dates by Week

To group the items in a Date field by week

  1. Right-click the Date field button.
  2. Choose Group and Show Detail | 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 at right, December 29, 2003 (a Monday), was entered as the starting date.
  6. Click OK

Problems when Grouping Data

If you try to group a date or 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 shown here to change them.

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 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 add a column to the database, then add that field to the pivottable.

For example, to count the unique occurences of a Customer/Item order, add a column to your database, with the heading 'CustItem'

In the first data row, enter a formula that refers to the customer and item columns. For example:


  =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)

Copy the formula down to all rows in the database.

Then, add the field to the data area of the pivot table.

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

  

1. Pivot Tables -- Dynamic Data Source
2. Pivot Tables -- Data Field Layout
3. Pivot Tables -- Show and Hide Items
4. Pivot Tables -- Clear Old Items
5. Pivot Tables -- Field Settings
6. Pivot Tables -- GetPivotData
7. Pivot Tables -- Grouping Data
8. Pivot Tables -- Multiple Consolidation Ranges
9. Pivot Tables -- Printing   
10. Pivot Tables -- Custom Calculations 
11. Pivot Tables -- Pivot Cache     
12. Pivot Tables -- Protection   
13. Pivot Tables -- Grand Totals
   
 

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:47 PM