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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

4 thoughts on “How to Group Numbers in Pivot Table in Excel”

  1. 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?

    Reply

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster