Excel – Grouping pivot table entries

Excel Grouping: YOUR OBJECTIVE

You may sometimes determine that your pivot table data is too voluminous or complex and that you therefore would like to view it in a fashion that will be easier to read and understand. Filtering is one possibility, of course, but you also have the option of combining rows or column into groups. You might want, for example, to group your pivot table entries by order-processing specialists, order values or order dates, etc.

This learning module “Excel Grouping” will show you how to group various sorts of entries in a pivot table.

Excel Grouping: HOW DOES IT WORK?

To obtain a more useful data structure, you can group items in a pivot-table’s rows or columns. These are sometimes, but not always, groupings that cannot be created by other means.

For instance, the original Customer Orders table has no column for the responsible order-processing specialist. To group your customers by the responsible order-processing specialist, you would need to do something manually to associate orders with individual order-processing specialists.

But you can use numerical values or dates to establish groups as well. Such groupings are often used to reduce the number of columns or rows displayed in an otherwise large pivot table.

The different types of groupings are explained in the following sections.

Excel: Grouping text values

Excel Grouping:To group text items that appear in the rows or columns of a pivot table, you must first select the items that you want to group. In our example, since we know that a particular order-processing specialist always deals with customers with company names that begin with A to H, we want to group all such customers and then indicate the name of the order-processing specialist who worked on them.

After selecting the group of interest (shown above), click the button in the Group group (PivotTable Tools, Options tab).

As shown below, a group field named Group1 will be created.

You can create other pivot-table groups in the same fashion. Each new group is assigned a group designation consisting of the name “Group” immediately followed by the next sequential number (Group2, Group3, etc.).

HINT

Excel Grouping:After you create the first group, all remaining items will automatically be converted to groups containing just one group member. You can nevertheless select and group items just as described above.

After creating a group, you can edit its name to better reflect the meaning of that particular group. To do so, click the corresponding group field name and overtype it with a name of your own invention. In the example (see below), we’ve changed the name Group1 to Jason Black (a fictitious order-processing specialist).

Excel Grouping: For each group you create, a new field button for the new outline level will be added to the PivotTable Field List task pane, under the list of all fields. In our current example, this is Customer2. The same new field label will also appear as an entry in the area where you created it; in this example, it appears in the Row Label area, too.

Excel: Grouping by numerical value

At your company, you’ve been asked to produce an analysis of the orders your company received over a number of months. You want to get a clear picture of how many orders your company received (order volume) and the value of those orders. To accomplish this, you are going to create a pivot table. You’ll bring the Customer names into the Row-Labels area and the ORDER VALUE field into the Column-Labels area. Then, to determine the number of orders that correspond to a particular order value, you will drag the ORDER VALUE field into the Values area as well. Finally, you will change the calculation type to Count (Click the arrow next to Sum of ORDER VALUE; then click Value Field Settings…; finally, set Summarize value field by to Count). The following pivot table is the result:

Although this pivot table delivers a lot of information, because of the wide range of Order Values, it is very difficult to read and understand. To make more sense of the data, it would be wise to combine the Order Values into groups that show thousand dollar spreads: 1-1000 dollars, 1001-2000 dollars, 2000-3000 dollars, etc. To do sthiso, select a numerical item among the column labels in the pivot table, and then click the excel grouping button (Options tab, Group group). This opens the excel Grouping dialog box. Here you can specify how you want to group the numerical values: enter the first value in the Starting at: input box, and the last value in the Ending at: input box. Lastly, specify the grouping interval in the By: input box. Then click OK.

In our example, Starting at: was set to 1, Ending at: was set to 4000, and to group in intervals of 1000, By: was set to 1000.

To apply these settings, click . Below you can see how the pivot table will change:

Now, even though the data has been summarized differently, you may have noticed that no new field button was added to the Pivot Table task pane. This is because no new excel grouping field was created.

Excel: Grouping by date

Excel Grouping by date works essentially the same way as grouping by numerical values.

In this example, the Order Date field was dragged into the Column Labels area and the Order Value (in the Values area) was set to Sum. As you can see, however, this causes the pivot table to become quite long and confusing. Summarizing the table by month would deliver more useful information, namely, the value of orders per month.

To group the dates, select a date item from among those in the Column Labels in the pivot-table report, and then click the excel grouping button (Options tab, Group group). The excel Grouping dialog box will appear. 

The values you see next to the Starting at: and Ending at: options are preset to the lowest and the highest values in the column labels. The preset values are just suggestions; you can change them, if you like. Select the kind of summary you want, i.e., the time interval in the By: selection list. In this example, a summary by Months has been selected. After clicking the dates in the pivot table’s column headings will be regrouped by the specified interval.

HINT

Excel: Grouping by more than one criterion: when you are in the excel Grouping dialog box, you can click more than one time interval (e.g., Quarters, Years, etc.), which could, depending on your purpose, increase the pivot table’s usefulness.

Excel Groupings: How to remove them?

Groupings are easily undone. To do this, select the grouping you want to remove, and then click the button (Options tab, Group group).

HINT

Excel: Groupings of text values are removed by deleting (deselecting) the corresponding field button in the PivotTable Field List task pane.

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen