Groups and Subtotals in Excel

Excel sheets that contain a lot of information can sometimes look cluttered and even hard to read. Excel allows you to group data to easily hide and show different sections of a worksheet. In addition, you can generalize different groups with the command Subtotal and give structure to an Excel worksheet. In this lesson, we will look at both of these tools in as much detail as possible and with illustrative examples.

Group rows and columns in Excel

1. Select the rows or columns you want to group. In the following example, we will highlight columns A, B, and C.
2. Click the Data on the Ribbon, then click command Group.
3. The selected rows or columns will be grouped. In our example, these are columns A, B, and C.

To ungroup data in Excel, select the grouped rows or columns, and then click the command Ungroup.

How to hide and show groups

1. To hide a group in Excel, click the icon Hide details (minus).
2. The group will be hidden. To show a hidden group, click the icon Show details (a plus).

Summing up in Excel

Team Subtotal allows you to automatically create groups and use basic functions such as SUM, COUNT and AVERAGE to make summarizing easier. For example, the command Subtotal is able to calculate the cost of stationery by groups in a large order. The command will create a group hierarchy, also called an outline, to organize the information on the sheet.

Your data must be properly sorted before using the command SubtotalYou can check out the Sorting Data in Excel series of lessons for more information.

Create a subtotal

In the following example, we will use the command Subtotalto determine how many T-shirts of each size (S, M, L, and XL) have been ordered. As a result, the Excel worksheet will be structured into groups for each T-shirt size, and then the total number of T-shirts in each group will be calculated.

1. First, sort the data for which you want to summarize. In this example, we are running a subtotal for each T-shirt size, so the information in the excel sheet should be sorted by column Size from smallest to largest.
2. Click the Data, then press command Subtotal.
3. A dialog box will open Subtotals. From the drop down box With every change in, select the column you want to total. In our case, this is the column Size.
4. Click on the arrow button in the field Operationto select the type of function to use. We will choose Quantityto count the number of T-shirts ordered for each size.
5. In the Add totals for select the column in which you want to display the total. In our example, this is the column Size.
6. If all parameters are correct, click OK.
7. The information on the sheet will be grouped, and subtotals will appear under each group. In our case, the data is grouped by T-shirt size, and the number of ordered T-shirts for each size is indicated under the corresponding group.

View groups by level

When summarizing subtotals in Excel, the worksheet is broken down into different levels. You can switch between these levels to be able to adjust the amount of information displayed using the outline icons 1, 2, 3 on the left side of the sheet. In the following example, we will switch between all three levels of the structure.

Although only three levels are shown in this example, Excel allows you to create up to 8 levels of nesting.

1. Click the bottom level to display the minimum amount of information. We will select level 1, which contains only the total number of t-shirts ordered.
2. Click the next level to display more detailed information. In our example, we will select level 2, which contains all rows with totals, but hides the rest of the data on the sheet.
3. Click the highest level to expand all the data on the sheet. In our case, this is level 3.

You can also use icons Show or Hide detailsto hide or show groups.

Delete subtotals in Excel

Over time, the need for subtotals disappears, especially when you need to regroup the data in an Excel sheet differently. If you no longer want to see subtotals, you can delete them.

1. Click the Data, then press command Subtotal.
2. A dialog box will open Subtotals… Click on Remove all.
3. All data will be ungrouped and the totals will be deleted.

To remove only groups, leaving subtotals, use the item Delete Structure from the command dropdown menu Ungroup.