How to Group Items in Excel (3 Easy Methods)

It is necessary sometimes to group several items according to their categories in Excel. Grouping items helps the readers to understand the dataset quickly and displays the summary pretty well. In this article, I will describe grouping items in Excel and show you the most suitable methods to do this. Go through the full article and enhance your knowledge in this regard.

Download Practice Workbook

You can download and practice from our workbook here for free.

What Is Group Item Command in Excel?

The term Group items basically means the grouping of multiple items according to their definite categories. It has several advantages that are pointed out below.

• It makes a summary of a large dataset to the point.
• 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. These mainly take place on the left side of the groups just beside the Excel row headings. 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 some other handy tools for accessing the groups quickly. It is mainly positioned on the top-left side of the workspace, which means you can find these tabs on the left of Excel column headings and on the top of the Excel row headings. If you click on the levels, it would show you different levels of your grouped Excel file accordingly.

3 Methods to Group Items in Excel

Suppose, you have a dataset containing Country, Product, Units Sold, Units Price, and Profit columns. Now, you want to group your items by the countries. To accomplish this, follow any of the methods below.

1. Use Subtotal to Group Items

You can use the Subtotal feature of Excel to group items in Excel. Follow the steps below to achieve this.

📌 Steps:

• First and foremost, click on any cell of your desired range or table where you want to group your data. Subsequently, go to the Data tab >> Outline group >> Subtotal tool.

• As a result, the Subtotal dialogue box will appear. Now, as we want to group the dataset by countries, select Country at the At each change in: options. Following, select the Use function: options as Sum. And, tick on the option Profit from the Add subtotal to: options. Last but not the least, click on the OK button.

Consequently, you will see that your dataset is divided into groups, which are grouped by the countries. And, each group has the respective sum of profits calculated. Moreover, there are toggle buttons to hide and show the groups and level tabs. For example, the result sheet will look like this.

Read More: How to Group Columns Next to Each Other in Excel (2 Easy Ways)

2. Use Auto Outline Feature

You can also group items in Excel through the Auto Outline feature of Excel. Follow the simple steps below to accomplish this.

📌 Steps:

• At the very beginning, you have to add the subtotals of your categories manually or by the Subtotal feature just like the upper stated method. At this time, your dataset will look like this.

• Now, click on any of the cells between the desired range. Subsequently, go to the Data tab >> Outline Group >> Group tool >> Auto outline option.

Thus, you can see your dataset has exploded into several groups according to the countries. You can understand this by noticing the toggle buttons and level tabs on the left side of the Excel file. For example, the fill will look like this now.

Read More: How to Group Similar Items in Excel (4 Common Ways)

3. Use Excel Group Command

This is another frequent approach to group items in Excel. Go through the steps below to learn about grouping items manually by rows.

📌 Steps:

• Initially, just like the second method, you have to add the subtotal manually or by the Subtotal feature of Excel to follow this method. Your dataset will look like this afterward.

• Now, select the group-wise range. For an instance, select the B5:F8 data series for the Canada country group. Subsequently, go to the Data tab >> Outline Group >> Group tool >> Group option.

• Consequently, the Group dialogue box will appear. Now, put the radio button on the Rows option and click on the OK button.

• As a result, you will see that the Canada country group has been created.

• Similarly, group the other two countries accordingly.

Thus, your dataset is now grouped according to the countries. For an instance, the data sheet will now look like this.

Read More: How to Create Multiple Groups in Excel (4 Effective Ways)

Keep the Summary Row on Top While Grouping

Sometimes, it might be needed to keep the summary row on top of the respective groups rather than keeping it below. You can follow some simple steps to do this.

📌 Steps:

• At the very beginning, go to the Data tab >> Outline group >> Outline icon

• At this time, the Settings dialogue box will appear. Uncheck the option Summary rows below detail. Last but not least, click on the OK button.

Thus, you will see that creating subtotals and auto outlining will now keep the summary row on top.

💬 Note

Sort the row/column by which you want to group your data at first. It will make your grouping easier to do and avoid pop up of unwanted warnings or error dialogue boxes.

Conclusion

To conclude, in this article, I have shown you 3 methods to group items in Excel. I have discussed the advantages and all the other information that you must know about grouping items. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy to find more articles like this. Have a good day!

Related Articles

• How to Remove Grouping in Excel (2 Suitable Examples)
• Group Data in Excel Chart (2 Suitable Methods)
• How to Group Time Intervals in Excel (3 Suitable Ways)
• Make a Grouped Bar Chart in Excel (With Easy Steps)

<< Go Back to Group Cells in Excel | Outline in Excel | Learn Excel