How to Group Rows and Columns in Excel Step-By-Step

🖋️ By Kasper Langmann, Certified Microsoft Office Specialist. 📅 Updated on August 29, 2023.

Working on complicated and extensive spreadsheets is so difficult – it’s bound to give you a headache 🥴

Luckily, Excel has grouping and outlining functions.

They make the data more readable and understandable by grouping it together. Not only that, but you can also create outlines and collapse rows to focus on a single section of data.

Using these functions can be challenging at first but worry not. We’re here to teach you just that.

If you want a free practice copy to try these functions, download our sample workbook here.

How to group rows in Excel

Before we begin, we need to be familiar with something 🧐

There are two primary ways to group rows in Excel. One, group rows automatically. And two, group rows manually.

Grouping rows automatically is much easier and faster. Especially when you have only one level of information, like this:

1st level of grouping data

But sometimes Excel may group incorrect data when grouping automatically. That’s especially when you have different levels of information, like this:

Multiple grouping levels

In such a case, we group the data manually.

With that being said, let’s see how to group rows in Excel both automatically and manually below.

How to automatically group rows in Excel

Suppose we have the following data that we want to group.

Example data

To group this data automatically:

  1. Select any cell from the data set.
Selecting the first row in left corner
  1. Go to the Data Tab.
Data Tab in our downloadable excel template.
  1. Under the Outline group, select Auto Outline from the Group option.
Selecting the auto outline option from the Outline group
  1. Choose the Rows option from the Group dialog box.
  2. The grouped data and outline appear automatically 😉
Excel group by cell value

These small boxes on the left side with the minus sign are outline symbols. And they represent each level.

These boxes act like layers you can hide or show at any time to make your data more concise. It is similar to the tool used in Adobe Illustrator 🖼️

You can collapse a particular level by clicking its corresponding outline box. Like this:

Collapsing blank rows using the hide detail button

And press the plus sign to expand the rows:

Expanding the blank row with the show detail button

We also have the outline number of each group at the top.

Outline symbols indicating levels

1 always represents the least amount of data. This is the condition where the rows are collapsed. Like this:

Hiding cells with the Number 1 button

Similarly, number 2 represents ungrouped data.

Ungroup data with the number 2 button

Make sure there are no hidden rows in the Excel sheet when you group data. Excel might perform the task incorrectly.

Kasper Langmann, Microsoft Office Specialist

How to group rows manually in Excel

We saw earlier that Excel grouped rows automatically. But it didn’t include an outline of the whole data set. Using that outline, we would have been able to hide the entire data set.

So, how do we do that?

For that, we will group the rows manually 🔒

We will first remove the previous outline.

How to remove outlines

  1. Click Clear Outline from the Ungroup option under the Outline group.
Clearing the outline from the Ungroup option
  1. The outline disappears.
Data with no outline

To manually group rows, we will first create the outermost outline. And then move toward the other levels.

To create the outermost outline:

  1. Select the whole data
Selecting the entire data
  1. From the Data Tab, click Group under the Outline group.
button for how to group rows in excel manually
  1. The outermost outline appears.
Outermost outline

Collapsing this will hide the entire data set.

Now, for the other level, we will select a particular level each time and then group the rows together 😀

For instance, we will group the first level from A2:A4.

  1. Select the rows.
Selecting rows of the first group
  1. Select the Group option from the Outline group.
  2. The selected rows have been grouped, and the outline appears.
Grouping the first level
  1. Perform the same steps with the remaining levels until the result looks like this.
Inner outlines

As visible, there are three number boxes in the outline this time. Contrary to what we saw earlier in automatic Excel grouping, choosing the first box here will hide all the data.

The second box will show a part of the data, and the third box will show the entire data set.

How to group rows with the SUBTOTAL function

You can also subtotal rows in all the groups using the SUBTOTAL function. You can apply the COUNT, SUM, AVG, MIN, and MAX functions to your data.

Let’s understand this with the following example data.

Example data
  1. Click anywhere on the dataset and press the Subtotal button.
Choosing the subtotal option
  1. The Dialog box appears.

Select the places you want to add subtotal to.

subtotal dialog box

Put a checkmark on the ‘Summary below data’ option. It displays the result of the chosen function beneath the data. You can also change the function you want from the options.

The summary row looks like this:

Subtotal row

The SUBTOTAL function automatically creates an outline along with the Grand total. Hence, you can use it to get two tasks done at the same time 🤓

Kasper Langmann, Microsoft Office Specialist

And that’s it about grouping rows in Excel 🤗

How to group columns in Excel

Grouping columns in excel is the same as grouping rows. It is used when your data extends from left to right instead of top to bottom.

The only difference is that the outline appears on the top of the sheet instead of appearing on its left.

Let’s see how this works.

We will use the previous data set. And here, we want to group the columns.

Grouping columns in the data

We will first create the outermost outline and group all the data.

Column outline appears

Now we will group each column individually, starting from column B – Mangoes.

  1. Select the column heading and click Group.
  2. Choose the Column option from the dialog box.
  3. Press Enter
Outline for the first group appears
  1. Repeat the same process with the remaining columns.
Outline for all columns appear

All your columns have now been grouped.

That’s it – Now what?

Knowing how to group rows or columns is very important. It helps organize data and improves the readability of complex and detailed information.

In this article, we learned how to group data in Excel and how to make a clear outline. These are some of the most useful features of Excel, but there’s so much more to learn.

If you are new to this giant spreadsheet software, we suggest you start by mastering the essential intermediate functions. These include VLOOKUP, IF, and SUMIF functions.

Join my 30-minute free email course to learn these fantastic functions and many more.

Other resources

The best part about grouping rows and columns in Excel is that it lets you focus on a small section of data instead of the whole sheet.

If you like this article, you might want to read some other articles like the SUBTOTAL functionFILTER function, or hiding rows. 😃

Frequently asked questions

To auto-outline data in Excel, select any cell from the data set. Go to the Data Tab. Select the Auto outline option under the Outline Group from the top right corner. From the dialog box, select the way you want to group your data and press Enter. Your data has been grouped.

You need to group the data manually to group rows by cell value. Divide your data into different levels. Select each level individually and group it using the Group option.