How to group a pivot table by numbers

In addition to letting you group data manually, pivot tables can also group certain kinds of data automatically. One kind of automatic grouping works on numeric data.

Let’s take a look.

This pivot table shows total sales and orders for the sales data we’ve looked at previously. One field we haven’t looked at yet is Quantity. If we take a quick look at the source data, we can see that quantity refers to the number of items in each order.

Let’s break down total sales by order quantity. To start off, let’s add quantity to the pivot table. When we first add the quantity field, we get a detailed breakdown by quantity. In our sales data, we have 13 orders with a quantity of 1, 18 orders with a quantity of 2 items, 4 orders with a quantity of 3 items, and so on.

This is interesting, but not very useful, since there’s far too much detail. Let’s group the quantity field into larger buckets. To automatically group a numeric field, first select a cell in the field, then click the Group Selection button on the ribbon.

Excel will display a grouping dialog box, customized for numbers. By default, Excel will have already filled in the lowest value and the highest value in the field. In this case, let’s group by 500. When we click OK, the pivot table displays a breakdown of total sales and orders according to the quantity in each order, in groups of 500.

We can now easily see that there are over 2900 orders with a quantity of 1-500 items, representing total sales of over $245,000.

When grouping by number, you can adjust the grouping at any time. Just select a value in the grouping field, right-click, and choose Group from the menu. Let’s change grouping to break down this information in buckets of 250.

You can also set upper and lower limits on the grouping. For example, we can easily set an upper limit of 1000 on our grouping. Excel will then group all orders with a quantity of more than 1000 in a single group.

To ungroup a field, just choose Ungroup on the ribbon.

You can also right-click to group and ungroup a field. Just select a value in the field, and right-click to access grouping commands.

Like fields that have been sorted or filtered, grouped fields will stay grouped even when they have been removed and re-added to a pivot table.