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

This article illustrates how to create multiple groups in Excel. Working with a large dataset often becomes problematic. You can create multiple groups within your dataset in Excel to handle them more efficiently. This allows you to display the summary of each group instead of all contents. This article highlights 4 effective ways you can create multiple groups in Excel. Have a quick look through the article to learn those methods.

4 Ways to Create Multiple Groups in Excel

Assume you have the following dataset. It contains the names of the sales representatives of each month and corresponding sales. Now you want to group the sales by each quarter. Then apply any of the methods below.

1. Create Multiple Groups with Keyboard Shortcut

Follow the steps to quickly create multiple groups in excel using a keyboard shortcut.

📌 Steps:

• First, select the rows which you want to group together by clicking the row numbers.

• Then, press SHIFT+ALT+Right Arrow(➜). After that, you will see the following result. You can hide the grouped rows by clicking on the negative icon at the left of the row numbers.

• If you select a range of cells instead, you have to confirm whether to group rows or columns.

• Next, select the other rows which you want to group together.

• But, you may not get the desired results. Because the dataset is not formatted properly. Therefore, you may end up with a single group instead of multiple ones.

• Now, insert a new row below row 7 to format the dataset properly to create multiple groups. Then, insert the following formula in cell D8 to show the total sales for the first quarter.

• Then, enter the following formula in cell D12 to show the sales total for the second quarter.

• Now, use the keyboard shortcut to group rows 5:7 and 9:11 After that, you will get the following result.

• You can show/hide the group contents by clicking on the icons at the left of the row numbers.

• You can create multiple groups with columns in the same way. Then, the result will look as follows.

Read More: How to Group Columns Next to Each Other in Excel

2. Create Multiple Groups with Group Command

You can also create multiple groups using the Group command in excel. Follow the steps below to do that.

📌 Steps:

• First, format the dataset properly as in the earlier method.
• Then, select the rows or columns which you want to group together.
• Next, select Data >> Outline >> Group as shown below.
• Then, repeat this procedure for other rows or columns.
• After that, you will get the same result.

Read More: How to Group and Ungroup Columns or Rows in Excel

3. Apply Auto Outline Feature to Create Multiple Groups

If your dataset is formatted properly, you don’t need to repeat the same process to create multiple groups as in the earlier methods. Instead, click anywhere in the dataset and select Data >> Outline >> Group >> Auto Outline as shown below. Then, excel will automatically create multiple groups based on your dataset.

But, if the dataset is not formatted properly, then excel will show the following error.

4. Utilize Excel Pivot Table

Alternatively, you can quickly insert a PivotTable to create multiple groups within your dataset. Follow the steps below to do that.

📌 Steps:

• First, select anywhere in the dataset. Then, select Insert >> PivotTable. Excel will automatically detect the range of the dataset. Otherwise, use the upward arrow to input the data range for the PivotTable. Then, mark the radio button for Existing Worksheet and enter the location. Click OK after that.

• Now, check the checkboxes for the listed fields in the PivotTable Fields pane at the right. You can drag them to the respective areas too.

• After that, excel will detect the similar types of data in the dataset and automatically group them together as follows.

• Otherwise, select the rows or columns in the PivotTable and right-click to group them.

Read More: How to Group Similar Items in Excel

Things to Remember

• You must create your dataset in such a way so that excel can differentiate between various subsets of data within the dataset. For example, keep an empty or differently formatted row or column between two subsets of data.
• You can use the SHIFT+ALT+Left Arrow(⬅) shortcut to ungroup any grouped data.

Download Practice Workbook

You can download the practice workbook from the download button below.

Conclusion

Now you know how to create multiple groups within a dataset in excel. Which method do you prefer the most? Do you have any further queries or suggestions? Please let us know in the comment section below.

Stay with us and keep learning.

Related Articles

• How to Group Time Intervals in Excel
• How to Remove Grouping in Excel
• How to Use Grouping and Consolidation Tools in Excel
• How to Group Cells with Same Value in Excel
• How to Group Duplicates in Excel

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