Search

# 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.

This Tutorial Covers:

### Group Numbers in Pivot Table in Excel

Suppose you have retail sales data as shown below:

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):

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).

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.
• 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.
• 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.

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.

### How to Ungroup Numbers in Pivot Table

To ungroup these number groups, select any of the group and go to Analyze –> Group –> 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.

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:

FREE EXCEL BOOK

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

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. Perfect – thanks 🙂

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

4. Interesting