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

The following dataset contains the names of the sales representatives for each month and the corresponding sales. We want to group the sales by quarter.

Method 1 – Using a Keyboard Shortcut

Steps:

• Select the rows you want to group together by clicking the row numbers.

• Press SHIFT+ALT+Right Arrow(➜).

You will see the following result. To hide the grouped rows, click on the negative icon left of the row numbers.

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

• Select the other rows which you want to group together.

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.

• Insert a new row below row 7 to format the dataset properly to create multiple groups.
• Enter the following formula in cell D8 to show the total sales for the first quarter:

• Enter the following formula in cell D12 to show the sales total for the second quarter:

• Use the keyboard shortcut to group rows 5:7 and 9:11. You will get the following result.

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

• Create multiple groups with columns in the same way. The result will look as follows.

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

Method 2 – Using the Group Command

Steps:

• Format the dataset properly as in the earlier method.
• Select the rows or columns which you want to group together.
• Select Data >> Outline >> Group, as shown below.
• Repeat this procedure for other rows or columns.

You will get the same result.

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

Method 3 – Using the Auto Outline Feature

If your dataset is formatted properly, you don’t need to repeat the same process to create multiple groups as in the earlier methods.

• Click anywhere in the dataset and select Data >> Outline >> Group >> Auto Outline, as shown below.

Excel will automatically create multiple groups based on your dataset.

• If the dataset is not formatted properly, then Excel will show the following error.

Method 4 – Using an Excel Pivot Table

Steps:

• Select anywhere in the dataset.
• Select Insert >> PivotTable. Excel will automatically detect the range of the dataset.
• Or, use the upward arrow to input the data range for the PivotTable.
• Mark the radio button for the Existing Worksheet and enter the location.
• Click OK.

• Check the checkboxes for the listed fields in the PivotTable Fields pane at the right. Drag them to their respective areas.

• Excel will detect similar data types 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 so that Excel can differentiate between various subsets of data within it. 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 the Practice Workbook

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

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 the Same Value in Excel
• How to Group Duplicates in Excel

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