How to Group Numbers in Pivot Table in Excel

You can group numbers in Pivot Table to create frequency distribution tables. This helps in analyzing numerical values by grouping it into ranges.

A simple example of this could be to analyze how many students scored marks between 40 and 50 and how many got marks between 50 and 60 and so on..

Another example could be to create a frequency distribution of age of a group of people. This would help in identifying how many people fall in the 30-40 age group and how many in 40-50 age group and so on.

Let’s take an example of retail sales data to see how you can group numbers in Pivot Table in Excel.

Click here to Download the Example File to follow along.

Group Numbers in Pivot Table in Excel

Suppose you have retail sales data as shown below:

Group Numbers in Pivot Table in Excel - Dataset

Using this data, I’ve created a Pivot Table with Stores and Sales in the Rows area and Sales in the Value area. This will give you a Pivot Table as shown below (tabular form):

Group Numbers in Pivot Table in Excel - Pivot Table Tabular

Note that the values column has COUNT instead of SUM. This can be made by changing the value field settings of the Sales value to display COUNT (as shown below).

Group Numbers in Pivot Table in Excel - Count display

As of now, the Pivot table isn’t of much use as it shows a lot of data. But you can group the sales value for each store and create a frequency distribution.

To do this:

  • Select any cells in the row labels that have the sales value.
  • Go to Analyze –> Group –> Group Selection.Group Numbers in Pivot Table in Excel - Analyze Tab
  • In the grouping dialog box, specify the Starting at, Ending at, and By values. In this case, By value is 250, which would create groups with an interval of 250.Group Numbers in Pivot Table in Excel - gruop by
  • Click OK.

This would create a Pivot Table that shows the frequency distribution of the number of sales transactions within the groups that we created.

Group Numbers in Pivot Table in Excel - frequency distribution

This Pivot Table now has a frequency distribution that can be used for analysis such as:

  • Which stores are doing more high-value transactions?
  • Which stores need to improve sales by trying to increase transaction value?

You can also move the grouped sales to the column area to create a matrix that is even easier to read.

Group Numbers in Pivot Table in Excel - in Column

Click here to download the example file.

How to Ungroup Numbers in Pivot Table

To ungroup these number groups, select any of the group and go to Analyze –> Group –> Ungroup.

Group Numbers in Pivot Table in Excel - ungroup

Error While Grouping Numbers in Pivot Table

Sometimes, you may get the ‘Cannot group selection’ error (as shown below) while creating groups with numbers.

Group Numbers in Pivot Table in Excel - error

This may happen if you have cells that contain text instead of numbers.

In such cases, you need to go back to the data source and remove the text with appropriate numerical value.

Sometimes, numbers are stored as text in Excel. In such case, you need to convert these text to numbers before grouping it in Pivot Table.

You May Also Like the Following Pivot Table Tutorials:

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)

  • Crisp

    Interesting

  • Rena McLeod

    Hello there:

    I am trying to create pivot table/chart for an overtime report. I want to summarize by the workgroup and have the hours fall into a range (0-25), (26-100), (101-200),(201-300),(301-400), (>400). My pie chart will be by the hour range. Also, is there a way to import the data (downloaded from SAP into a .csv file) and automatically populate these graphs and pivot tables?