How to Group Numbers in Excel Pivot Table (with Simple Steps)

Get FREE Advanced Excel Exercises with Solutions!

Grouping means organizing data in a manner to view them at a glance. The Excel Pivot Table is a wonderful tool with a built-in feature for grouping. The Pivot Table can perform grouping based on date, text, number, etc. But here, we will discuss how to group numbers in an Excel Pivot Table with steps in detail.


Step 1: Inserting Data in Excel

First, we need to insert data into the dataset. Our dataset consists of a super shop’s date, branch, region, and sales information. The super shop has several branches in different locations.

Insert Data for Excel Pivot Table


Step 2: Creating an Excel Pivot Table

In this step, we will create a PivotTable based on our dataset.

  • First, go to the Insert tab.
  • Click on the PivotTable option from the Tables group.

Create an Excel Pivot Table

  • The PivotTable from table or range window appears.
  • In the Table/Range box, select the cells from the dataset.
  • Choose the Existing Worksheet option.
  • Finally, press the OK button.

Create an Excel Pivot Table

  • Look a PivotTable appears in the dataset.


Step 3: Organizing Data in Pivot Table

In this step, we will organize our data in the PivotTable according to our desire.

  • We insert Branch in the rows and Sales in the values section.

Organize Data in Excel Pivot Table

Sales data are shown against each branch.

  • Now, insert Sales in the rows section.

We want to know the occurrence number against each sales amount.

  • Go to the values section.
  • Click on the down arrow of the Sum of Sales option.
  • Select Value Field Settings from the menu.

Organize Data in Excel Pivot Table

  • The Value Filed Settings window appears.
  • Choose the Count option from the Summarize Values By section.

Organize Data in Excel Pivot Table

  • Finally, press OK and see the PivotTable.

We can see the number of occurrences in the Count of Sales column.


Step 4: Grouping Numbers in Excel Pivot Table

In this step, we will group the numbers in the Excel PivotTable.

  • Go to the Date tab.
  • Choose Group from the Outline group.

Group Numbers in Excel Pivot Table

We have another alternative way to avail Group option.

  • Click on any cell on the PivotTable. Now, press the right button of the mouse.
  • Click on the Group option from the Context Menu.

Group Numbers in Excel Pivot Table

  • Now, the Grouping window appears.
  • Here, the starting and the ending point is set by default.
  • The By box indicates the difference.
  • We set 250 You can set this value as per your need.

Group Numbers in Excel Pivot Table

  • Finally, press the OK button.

Here, Sales are grouped at a certain interval.

We also can change the data presentation as per our desire and data pattern.

Read More: How to Use Excel Pivot Table to Group by Different Intervals


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, we described the steps of how to group numbers in an Excel Pivot Table. I hope this will satisfy your needs. Please, give your suggestions in the comment box.


Related Articles


<< Go Back to Group Pivot Table | Pivot Table in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo