How To Group Rows And Cells in Excel – And Outline Them Too!

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Spreadsheets often have different levels of data. And those levels can be difficult to parse when you’re trying to read them.

That’s where grouping and outlining comes in.

Grouping cells can make your data easier to read, and it can even help you automate some addition within the outline.

Groups and outlines can be a bit complicated, but if you work with nested data, they’re very convenient.

Let’s jump in!

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

What is nested data?
And why outline it?

Before we get started, you need to understand what I mean when I say “data with different levels” or “nested data.”

Here’s one of the spreadsheets we’ll be using in our examples:

sample-spreadsheet

Our data looks like this:

  • Classes
    • Semester total
  • Classes
    • Semester total
      • Year total
  • Classes
    • Semester total
  • Classes
    • Semester total
      • Year total

As you can see, there are consistent levels of information, and they’re repeated: nested within one another.

Nested data doesn’t have to repeat this many times, but it should give you a good idea of what I mean.

Why would you create an outline for nested data?

We’ll look at some of the cool things you can do with it later, but it’s mostly about keeping things organized and easier to view. Especially if your spreadsheet is very complicated, outlines can make skimming and extracting data a lot easier.

Get your FREE exercise file

Outlines work best with specific types of data, so we’ve created an example workbook that you can use to follow along.

We’ll be covering a lot of ground in this article, so grab it now and let’s get started!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Grouping your data

The first thing you’ll need to do is group your data.

This tells Excel which pieces of data belong together, and which levels they’re part of.

The easiest way to do this is to have Excel automatically outline your data.

Open up the example workbook. We’ll let Excel auto-outline the data to see how well it does.

Click into any cell, then go to the Data tab in the Ribbon. Click Group > Auto Outline.

auto-outline

Now, look to the left of the data in the spreadsheet:

outlined-spreadsheet

Each bar represents a level of organization. The bars further to the right are subordinate levels.

Kasper Langmann, Co-founder of Spreadsheeto

In our case, Excel grouped the data perfectly. It separated the data into three levels and didn’t include the total rows.

If Excel doesn’t group your data correctly, though, you can group cells manually. Let’s try that with our spreadsheet.

First, click Ungroup > Clear Outline:

clear-outline

The bars on the left side of the window have disappeared, and we’re back where we started.

Now, select rows 2 through 13:

select-rows

We’ve selected these rows because they represent one of the largest sets of data: everything from year one.

Kasper Langmann, Co-founder of Spreadsheeto

Click Group (in the Data tab) and you’ll see a bar on the left side of the screen that spans those rows:

grouped-rows

That set of rows is now a group.

Let’s create a nested group. Select rows 2 through 6, and click Group:

nested-group

You can probably see where it’s going from here. Select rows 8 through 12 and hit Group again.

Then, create the same groups for year two. Now you’ll have the same outline as the one Excel created automatically.

Pro tip: group columns

In our example, we’ve grouped rows, but you can also group columns. Select the columns you want to group and hit the Group button.

It works exactly the same as grouping rows.

This is useful when you have data that goes left-to-right instead of top-to-bottom. That’s common with data that’s spread over a range of time.

Adding data to the outline

Spreadsheets are rarely complete—and you’ll probably want to add more data to your outline.

Let’s try that in the example workbook.

I’ve added a new row of data: the grand total.

grand-total

This is now the outermost/topmost level of our outline, and we need to tell Excel to include it.

Kasper Langmann, Co-founder of Spreadsheeto

Adding this level to the outline is easy: highlight rows 2 through 27 (the rows that are included in the grand total), and press Group.

new-group

As you can see, we now have a new level in the outline.

Pro tip: re-auto-outlining

When you add more data to your outline, you can click anywhere in your dataset and click Group > Auto Outline again.

Excel will ask you if it’s alright to modify the existing outline, and when you click OK, you’ll get an updated outline with the new data.

Using outlines to quickly view data

Now that we have an outline, let’s see what we can do with it.

One of the most useful things you can do is to minimize specific groups. Click the box at the bottom of a group bar to minimize that group:

minimize-groups

This makes a complicated spreadsheet much easier to read.

You can also minimize all the groups at a specific level by clicking one of the numbers at the top of the outline:

minimize-shortcuts

The number 1 shows the least amount of data, while the highest number shows all the available data.

Kasper Langmann, Co-founder of Spreadsheeto

Automatically subtotaling groups

Excel can also automatically add subtotal rows for you. To see how this works, open up the second sheet in the example workbook.

You’ll see that there are no subtotal rows like there were in the first sheet, and that we’ve added a column for the year.

To automatically calculate subtotals, click the Subtotal button in the Ribbon:

subtotal-button

You’ll see a new pop-up menu with options for subtotalling:

subtotal-options

For our purposes, we’ll select “Year” from the At each change in menu, and “Students” in the Add subtotal to list. Hit OK.

year-total

Excel has now created subtotal rows for each year.

We can repeat the process and select “Session” instead of year (making sure to uncheck the Replace current subtotals box), and we’ll get the rows we had in the first sheet:

new-subtotals

Pro tip: subtotal in the right order

If we would have subtotalled the sessions before the year, we would have gotten a different result:

wrong-subtotals

To avoid this, create subtotals for the outermost levels in your outline first, then move in.

If you haven’t created an outline yet, you can still use the Subtotal command.

In fact, it will create an outline for you, completing two steps at once!

Styling subtotal lines

In our outline, we’ve simply bolded each subtotal line.

But Excel can automatically style your subtotals for you.

Select the cells in your outline, and click the arrow in the bottom-right corner of the Outline box in the Ribbon:

more-outline-options

This will bring up another dialog box.

Click Apply Styles, and Excel will change the formatting of the subtotal and grand total rows.

automatic-styles

And if you’d like to automatically apply those styles in the future, check the Automatic styles box.

Tips and tricks for outlines

When you’re working with outlines, there are a few things to keep in mind.

First, when you have some of your groups minimized, and you select the visible rows, you’re actually selecting the invisible rows as well, just like when you hide rows the traditional way.

To select only the visible rows (if you want to copy and paste them, for example), you’ll need to go about it differently.

Once you’ve minimized the groups you don’t want to copy, head to the Home tab. From there, click the Find & Select button, and choose Go To Special:

go-to-special

You can also use groups and outlines to create graphs and charts of specific subsets of data.

As a quick example, take a look at this chart that includes all the data in our spreadsheet:

full-chart

If we minimize most of the groups and create the same chart, however, we get a smaller one:

smaller-chart

This isn’t especially useful in this case, as we don’t need charts or graphs for this data, but I’m sure you can see how convenient it would be with a different dataset.

Kasper Langmann, Co-founder of Spreadsheeto

Manage your spreadsheets with outlines

Grouping and outlining might seem complicated at first, but once you have some practice with them, you’ll be able to group items, generate subtotals, and better manage your spreadsheet in no time.

If you work with nested data, it’s worth putting in the time up front!