Grouping Pivot Table Data

Basic Description

It is often useful to group a pivot table by the row or column values. Excel can do this automatically for numeric values (including dates & times). This is shown in the following examples.

Pivot Table Grouping 1

If you want to group the sales figures by month, you can this as follows:

grouping

  • Right click on left column of the pivot table (containing the dates) and select the option Group…
  • You will be presented with an options box (shown on the right).
  • Select the value Days and click OK.

This will group the figures by days, as shown in the pivot table below.

Row Labels Sum of Product Price
1-Aug                  3,241,973
2-Aug                3,236,475
3-Aug                  3,391,902
Grand Total                9,870,350

 

If you want to group the Customer ID into the ranges like range 500 from the starting value , you can do it. Which is as follows:

goruping2

  • Right click on left column of the pivot table (containing the Customer ID) and select the option Group…
  • You will be presented with the options box shown on the right.
  • Excel has defaulted to the minimum and maximum values of 4 and 5721, as these are the minimum and maximum Customer ID within the data.
  • We want to group the Customer ID into 500 periods, so we change the value in the lower box (labelled By:) to a 500.
  • Click OK.
  Row Labels Sum of Product Price
  4-503 644499
  504-1003 3352515
  1004-1503 5644697
  5504-6003 228639
  Grand Total 9870350

 

Ungrouping a Pivot Table

To ungroup the values in a pivot table, simply:

  • Right click on left column of the pivot table (containing the grouped values)
  • Select the option Ungroup…

 

Common Pivot Table Grouping Error

If you attempt to group a pivot table, but find that the Group… option is greyed out, or that a window pops up, saying “Cannot Group That Selection”, this is usually because the data column in the original data sheet contains one or more non-numeric values (or non-date values). In order to fix this, you will need to return to your original data sheet and amend the non-numeric values (or delete the rows containing the non-numeric values). Once you have done this, right click on the pivot table and select Refresh. This will update the values in the pivot table, and you should now be able to group the selected row or column values.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment