Group Data in Excel

If you deal with a large amount of data, it’s often a good idea to manage your data efficiently. That’s why grouping is a good idea to consider. You can show or hide a certain part of your worksheet to present only data that is important. Grouping is similar to hiding but gives you much more control.

Grouping columns

In this example, I have a list of people and their orders.

For now, we don’t want to see their names, just orders and payment methods. That’s why we are going to group the first and last names.

To do it, select columns A and B, then navigate to Data >> Outline and click Group.

If you click the minus icon at the top, the columns will collapse and only the payment method and fruit columns are going to be visible.

The minus sign changed to plus, indicating that the columns are collapsed. Press the plus button to expand the hidden columns.

The keyboard shortcut for grouping:

Alt + Shift + →

This shortcut will group any column or row you selected. If you didn’t select and column o row, the grouping window will appear instead.

Ungrouping columns

To ungroup columns, first, expand them. Select the columns you want to ungroup and click Data >> Outline >> Ungroup.

If you select the whole table, Excel will ask you whether you want to ungroup rows or columns. In this case select Columns.

If you didn’t expand grouped columns, and you choose to ungroup them, they are going to stay hidden. In this case, you will have to unhide them.

You don’t have to unhide all the columns. For example, if you want to keep the first names hidden, and ungroup only the last name, you can click a single column and then click ungroup.

The keyboard shortcut for ungrouping:

Alt + Shift + ←

This shortcut will ungroup any column or row you selected, otherwise the ungrouping window will appear.

Grouping multiple columns

The grouped columns don’t have to be adjacent. You can click different columns and group them.

Click columns A and group it, then column C and also group it. You can’t select multiple columns (with the control key) at once and then click the Group button because Excel is going to return a warning message.

After you group two non-adjacent columns, your table is going to look like this.

Grouping rows

You can group rows in a similar way you group columns. Just select a few of them and click Group.

Let’s say you want to group and hide rows with debit card payments and pending, so only cash will be visible.

First, you have to sort the table by payment method. Then select payments that are not in cash, and select the Group button.

Subtotals

The subtotal command is used to automatically create groups. It can use a few functions, such as SUM, AVERAGE, and COUNT to summarize data.

In this example, I’d like to count the number of orders of different fruit types. Before we do this, the data has to be sorted by fruit. After you do this you can use subtotals.

Click any cell inside the table and navigate to Data >> Outline >> Subtotal.

After you click the button, the Subtotal window is going to appear. Select values as shown in the picture below.

Click OK and look at the data.

Each fruit type is separated into a group with a total number at the bottom. All of these types are counted as the grand total and displayed on the list.

In the upper-left corner, there are three numbers. Clicking on them gives you different results.

Number 3

Expands all groups.

Number 2

Groups values and displays only subtotals.

Number 1

Displays only the grand total.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.