Grouping of numbers can be done for creating a frequency distribution table in a Pivot Table. This can be done in carrying out analysis of numerical values by carrying out grouping in several ranges. For understanding this in a better manner, an example can be considered. Analysis of the number of students who scored between 30 and 50 can be done. The same can be done for students who scored between 40 and 50. Frequency distribution of people within a group can also be analyzed. This would assist in the identification of people who fall in the age group of 30 to 40 or more.  

Grouping Numbers in Excel using Pivot Table

For instance, if you have sales data then you can create a pivot table with sales and stores in rows area. The sales data can be entered in the area where value needs to be entered. This would give you a tabular column. The value in the column does not consist of SUM but it consists of COUNT. This can be done by altering the value of sales value in field settings. This displays the COUNT. A lot of data renders Pivot table less useful. However, grouping of sales value can be done for every store for frequency distribution. For this,

•  Select cells within the rows labels which consists of the value of sales

•  Choose Analyze > Group  > Group Selection

Within the group dialog box, there is a need to specify the start and the end point. By value needs to be entered as well which will help in creating groups having an interval that would be equivalent to the By value. Click on OK. This would help in developing a Pivot Table that offers frequency distribution of the sales transactions carried out.

The Pivot table can be then analyzed for figuring out the stores that have high transactions. Information such as the stores that require improvement in sales can also be analyzed with this data. The grouped sales data can be moved to the column area for developing a matrix which can be read easily.   

Ungrouping Numbers in Pivot Table

Just select the group and choose Analyze > Group > Ungroup.

Sometimes, an error message is displayed such as “Cannot group selection” while using numbers for grouping. This may occur if cells consist of text and not numbers. This may require you to check the data source for removal of text and insert appropriate value that is numeric.