Last updated: December 6, 2009 2:26 AM
Search Contextures Sites ![]()
Excel Pivot Table Tutorial -- Grouping Data
- Grouping Dates
- Grouping Dates by Week
- Problems when Grouping Data
- Count Unique Items
- Excel Pivot Table Tutorial List
Download the zipped sample file for this Excel pivot table tutorial
In an Excel 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.
![]()
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 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.
- Check the field list, to see if there's a second copy of the date field, e.g. Date2.
- If there is, add it to the row area, and ungroup it.
- Then, you should be able to group the date field again.
=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 Excel pivot table.
In this example, you can see that nine unique customers placed an order for binders, and there were 14 orders for binders.
Download the zipped sample file for this Excel pivot table tutorial
Contextures Inc., Copyright © 2009.
All rights reserved.