How to Group Items in Excel (3 Easy Methods)

Download the Practice Workbook

Group Items.xlsx

What Is the Group Item Command in Excel?

The term Group items groups multiple items according to their defined categories. It has several advantages:

It makes a summary of a large dataset.
It is easy to understand and analyze an Excel dataset by grouping items.
It enables the application of several formulas and calculations according to their categories distinctively.
It enables easy expansion and contraction of cells.

Toggle Buttons:

Toggle buttons are the buttons that are used to hide and show the groups. Each group will have toggle buttons. They show up on the left side of the groups just beside the Excel row headings or above the column headers.

For example, let’s hide the Canada country group by clicking on the toggle button. Afterward, we can also show this group by clicking the toggle button.

Level Tabs:

Level tabs are positioned on the top-left corner of the workspace. If you click on the levels, Excel will show you the different levels of your groups.

3 Methods to Group Items in Excel

We have a dataset containing Country, Product, Units Sold, Units Price, and Profit columns. We want to group items by the countries.

Method 1 – Use the Subtotal to Group Items

Steps:

Click on any cell of your desired range or table where you want to group your data.
Go to the Data tab, and in the Outline group, select the Subtotal tool.

The Subtotal dialog box will appear. Select Country at the At each change in: options.
For the Use function: option, put Sum.
Check the Profit option from the Add subtotal to: list.
Click on the OK button.

The dataset is now divided into groups by countries. Each group has the respective sum of profits calculated. You will get toggle buttons to hide and show the groups and level tabs.