How to Group Rows in Excel?

by

Many Excel users often work with large datasets in Excel. And large datasets are often split into different categories row-wise.

In such cases, it would become difficult to get an overview of the categories. One thing that would help would be to group similar items under each category to better organize the data.

Excel provides us with the functionality to group rows so that we may organize data in a better way.

The grouped rows may be collapsed or expanded as required.

Take a look at the dataset below.

data set to group rows in Excel

The dataset shows items sold by different representatives in different regions.

For large datasets, it may be helpful to organize the regions in groups so that we may have an idea of how many regions there are.

Also, we may want to see the total sale made in a particular region without seeing the individual entries.

So in this tutorial, I will show you different methods by which you can group rows in Excel.

Method 1: Group Rows in Excel Using the Group Option

In this method, we will look at the ‘Group Rows’ option in the ribbon in Excel to group rows containing similar data.

As an example, we will use the following dataset that we saw earlier. Here we will group all the rows for the Central region.

data set to group rows in Excel
  1. Select all rows containing the Central region. These are rows 2 to 8.
select the rows that you want to group
  1. Go to the Data tab.
click the data tab
  1. Under the Data tab, click on the Group button.
Click on the Group Option
  1. All the rows containing the Central region will be grouped. This is evident from the line and minus sign that appears on the left side of the worksheet area (as shown below).
rows has been grouped
  1. Click on the Minus sign to collapse all the rows. 
click on minus sign to collapse rows
  1. The result will be as shown.
click on + to expand the rows

As you can see that the grouped rows have collapsed, and the minus sign will change to a plus.

  1. Click on the Plus sign to expand all the grouped rows. The result will be as shown.
data rows have been grouped

So in this method, we have seen how to group rows and collapse and expand them as we like.

Note: When we use this method to group rows in Excel, all the group rows would be held when you collapse the group. This would be useful in situations where you have multiple groups and you want to only focus on showing some of the groups while hiding the rest

Also read: How to Show the Total Row in Excel

Method 2: Group Rows in Excel Using Keyboard Shortcut

In this method, we will look at the keyboard shortcut keys to group rows containing similar data.

As an example, we will use the dataset used in the previous method. Here we will group all the rows for the Central region.

  1. Select all rows containing the Central region. These are rows 2 to 8.
select the rows that you want to group
  1. On your keyboard, press Shift + Alt + Right Arrow.
  2. You will see that all rows containing the Central region will be grouped. This is evident from the line and minus sign shown.
rows have been grouped

So in this method, we have seen how to group rows using a keyboard shortcut.

As with the previous method, you can collapse or expand the grouped rows using the minus or plus signs respectively.

Also read: How to Remove Groupings in Excel?

Method 3: Group Rows in Excel Using the Auto Outline Option

In this method, we will look at the Auto Outline option to group rows.

As an example, we will use the dataset as we did in previous methods.

However, to use Auto Outline, we will have to modify the dataset slightly.

We will have to insert additional rows that will differentiate between the groups. The dataset will be as shown.

insert additional row after every group

Here I have added three additional rows, as shown. In each of these rows, I have also calculated the total sale made in each region.

  1. Go to the Data tab.
click the data tab
  1. Click on the arrow next to the Group button.
click on the group drop down _icon
  1. The following dropdown menu will appear.
group submenu appears
  1. From this menu, select the Auto Outline option.
click on auto outline option
  1. You will see that all the different regions have been grouped. Note the minus sign appearing before each.
rows have been grouped
  1. Click on all minus signs to collapse all grouped rows.
click on - to collapse rows
  1. The result will look like the one below.
result after all rows have been collapsed

As you can see, the rows have collapsed, and we can now see the total for each region without seeing the individual entries.

Note: For this method to work, you need to insert a new row in between the groups and also ensure that there is at least one column that has the sum of all the values in that column (for the rows that you need to group)

Also read: How to Swap Cells in Excel

Method 4: Group Rows in Excel Using the Subtotal Option

In this method, we will use the Subtotal option to group rows.

The Subtotal option will not only group the rows, but will also provide a summary of the grouped data.

In the previous method, we had to create this summary by inserting additional rows and manually calculating the total sale made in each region.

The Subtotal option will insert the additional rows for us automatically and calculate the sum of the total sale made in each region.

We will use the same dataset as we did in the first method.

data set
  1. Select the entire dataset as shown. This will be from cell A1 to cell G13.
select the entire data set
  1. Go to the Data tab.
click on the data tab
  1. Under the Data tab, select the Subtotal option.
click on the subtotal _icon
  1. The Subtotal window will appear as shown.
sub total window
  1. Under the ‘At each change in’ option, select Region. (This is the criteria on which we want to group our rows.
select at change in option
  1. Under the ‘Use function’ option, select Sum. (This is the operation we want to perform on the data.)
specify the function to use for summary
  1. Under the ‘Add subtotal to’ section, check the Total option. Uncheck all other options. (This is the data on which we want to perform the sum function we selected in the previous step)
select the column to add subtotal
  1. Check the ‘Replace current subtotals’ and ‘Summary below data’ options. Make sure that the ‘Page break between groups’ option is unchecked.
uncheck the page break between groups option
  1. Click on OK.
click OK
  1.  The result will look like the one below. (Note the line and minus sign before each row group)
subtotal has grouped the rows
  1.  Click on the three minus signs as shown.
click on - to collapse rows
  1.  The end result will look as shown.
subtotal result after rows had been collapsed

You can see that all the grouped rows have collapsed.

This summary of data is similar to the one we saw in the previous method.

So in this method, we have seen how to group rows and create a data summary simultaneously.

In this tutorial, I have shown different methods as to how you can group rows.

The first two methods group rows that are manually selected. Use these methods if you want to group some rows selectively.

Method 3 groups rows together but requires that the row groups be differentiated from each other. If your dataset is small and inserting new rows under each row group is not a problem, then Method 3 is very useful.

Method 4 is a fully automatic method where not only are rows grouped based on a given criterion, but the data is also summarized as per the selected function.

If your dataset is large and inserting new rows or calculating subtotals is cumbersome, go with Method 4. 

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment