23. Creating Subtotals and Grouping Data
This chapter shows you how you can summarize and group together data using Excel’s Subtotal and grouping tools. The ability to group and subtotal data enables you to summarize a long sheet of data to fewer rows. The individual records are still there so that you can unhide them if you need to investigate a subtotal in detail.
Creating Subtotals
The easiest way to create subtotals in Excel is using the SUBTOTAL function. If you need more power or flexibility, Excel also offers a Subtotal tool. After creating your totals, you can format and sort them based on your specific requirements.
Using the SUBTOTAL Function
The SUBTOTAL function calculates a column of numbers based on the code used in the function. With the correct code, SUBTOTAL can calculate averages, counts, sums, and eight other functions listed in Table 23.1. It can also ignore hidden rows when the 100 version (101, 102, and so on) of the code is used.
The syntax of the SUBTOTAL function is as follows:
SUBTOTAL(function_num, ref1,[ref2],...)
Figure 23.1 shows the SUBTOTAL function in action versus the SUM function. The SUBTOTAL function with a code of 109 ignores any cells in the range that include SUBTOTAL functions themselves, as shown in the Grand Total. Column E uses the SUM function instead of SUBTOTAL and does not ignore the hidden rows or previous SUM formulas in the Grand Total.
FIGURE 23.1 SUBTOTAL can ignore hidden rows and other SUBTOTAL calculations, as shown in the Grand Total. The SUM function adds all data in its range.
Summarizing Data Using the Subtotal Tool
The SUBTOTAL function is very useful, but if you have a large data set, it can be time consuming to insert all the Total rows. When your data set is large, use the Subtotal tool from the Data tab in the Outline group. This tool groups the sorted data, applying the selected function.
Caution
You cannot use the Subtotal tool on a data set that has been converted to a Table (Insert, Tables, Table).
From the Subtotal dialog box, shown in Figure 23.2, you can select the column to group the data by, the function to subtotal by, and which columns to apply the subtotal to.
Figure 23.3 shows a report where the quantity sold was summarized by region. When data is subtotaled with this method, group/ungroup buttons are added along the row headers. You can use the (–) buttons to group the data rows together, showing only the Total, as was done for the Central region. Click a (+) button to expand the data.
FIGURE 23.3 Use the Subtotal tool to quickly summarize the quantity by region. You can then group the data, showing only the Total rows, like the Central Total.
To create the report shown in Figure 23.3, follow these steps:
1. Sort the data by the column the summary should be based on, the Region column.
2. Select a cell in the data set.
3. On the Data tab, click the Subtotal button. The Subtotal dialog box, shown in Figure 23.2, opens.
4. From the At Each Change In field, select the column by which to summarize the data, Region.
5. From the Use Function field, select the function to calculate the totals by. Because you want to sum the quantities, choose SUM, but there are many functions to choose from.
6. From the Add Subtotal To field, select the column(s) the totals should be added to, Quantity. Notice that, by default, the last column is already selected.
7. Click OK. The data is grouped and subtotaled, with a grand total at the very bottom.
Placing Subtotals Above the Data
By default, subtotals appear below the data being summarized. If the subtotals need to appear above the data instead, deselect Summary Below Data in the Subtotal dialog box. This also places the Grand Total row at the top of the data, directly below the headings.
Expanding and Collapsing Subtotals
When data is grouped and subtotaled, outline symbols appear to the left of the row headings, as shown in Figure 23.3. Click the numbered icons at the top (1, 2, 3 in Figure 23.3) to hide and unhide the data in the sheet. For example, clicking the 2 hides the data rows, showing only the Total and Grand Total rows. Clicking the 1 hides the Total rows, showing only the Grand Total. Clicking the 3 unhides all the rows.
Below the numbered icons, next to each Total and Grand Total, are the expand (+) and collapse (–) icons. These expand or collapse the selected group.
Removing Subtotals or Groups
To remove all the subtotals and groups, click the Remove All button in the Subtotal dialog box. To remove only the group and outline buttons, leaving the subtotal row intact, select Data, Outline, Ungroup, Clear Outline. You can also use Ctrl+8 to toggle the visibility of the outline buttons.
Caution
You cannot undo Clear Outline. If you accidentally select the option, click a cell in the table, bring up the Subtotal dialog box and click OK. The symbols will be replaced.
Copying the Subtotals to a New Location
If you hide the data rows, copy all the Subtotal rows, and paste them to another sheet, all the data, including the hidden data rows, appear in the new sheet. To copy and paste only the subtotals, follow these steps to select only the visible cells:
1. Click the Outline icon so that only the rows to copy are visible.
2. Select the entire data set. If the headers are to be included, you can do this quickly by selecting a single cell in the data set and pressing Ctrl+A.
3. On the Home tab, select Go to Special from the Find & Select drop-down list, and then select Visible Cells Only, as shown in Figure 23.4. (The dashed lines in the figure are shown for emphasis only. They appear in the next step.)
4. On the Home tab, click the Copy button.
5. Select the cell where the data is to be pasted.
6. On the Home tab, click the Paste button. The SUBTOTAL formulas are converted to values automatically.
Tip
A shortcut for step 3 is to press Alt+; (semicolon).
Formatting the Subtotals
If you hide the data rows, select the subtotal rows, and apply formatting to them, all the data, including the hidden data rows, reflect the new formatting. To format just the subtotals, follow these steps to select only the visible cells:
1. Click the Outline icon so that only the rows to copy are visible.
2. Select the entire data set. If the headers are to be included, you can quickly do this by selecting a single cell in the data set and pressing Ctrl+A.
3. On the Home tab, select Go to Special from the Find & Select drop-down list, and select Visible Cells Only, as shown in Figure 23.4.
4. Apply the desired formatting.
Tip
A shortcut for step 3 is to press Alt+; (semicolon).
Subtotaling by Multiple Columns
Figure 23.5 shows a report in which the Revenue, COGS, and Profit columns are summed by Region and Product. To subtotal by multiple columns, sort the data set by the desired columns and then apply the subtotals, making sure Replace Current Subtotals is not selected. The subtotals should be applied in order of greatest to least.
FIGURE 23.5 Apply subtotals to both Region (the major column) and then Product (the minor column) to get subtotals of both.
For example, if the data is sorted by Region, with the products within each region sorted, apply the subtotal to the Region column and then the Product column, like this:
1. Sort the data by the columns the summary should be based on. Because the report is to be by Region and then Product, sort by Region first, and then sort by Product.
2. Select a cell in the data set.
3. On the Data tab, click the Subtotal button.
4. From the At Each Change In field, select the major column, Region, by which to summarize the data.
5. From the Use Function field, select the function, SUM, to calculate the totals by.
6. From the Add Subtotal To field, select the columns the totals should be added to—Revenue, COGS, Profit.
7. Click OK.
8. Repeat steps 3 to 7 for the secondary column, selecting the minor column, Product, and unselecting Replace Current Subtotals.
Sorting Subtotals
If you try to sort a subtotaled data set while viewing all the data, Excel informs you that doing so removes all the subtotals. Although the data itself cannot be sorted, the subtotal rows can be, and the grouped data remains intact. To do this, collapse the data so that only the subtotals are being viewed, and then apply the desired sort.
Adding Space Between Subtotaled Groups
When you insert subtotals into a data set, only subtotal rows are added between the groups. The report might appear crunched together for some reviewers (see Figure 23.6), and they might request that rows be inserted, separating the subtotaled groups from each other. You can insert extra space into a subtotaled report in two ways.
If the report is going to be printed, you probably don’t need to insert blank rows. You just need to create the illusion because the actual need is for more space between the subtotal and the next group. You can create the illusion by adjusting the row height of the subtotal rows. To increase the amount of space when the subtotal is placed below the data, follow these steps:
1. Collapse the data set so that only the subtotals are in view.
2. Select the entire data set, except for the header row.
3. Press Alt+; (semicolon) to select the visible cells only.
4. On the Home tab, click the Format button, and select Row Height.
5. Enter a new value in the Row Height dialog box.
6. Click OK.
7. On the Home tab, click the Top Align button.
8. Spacing now appears between each group, as shown in Figure 23.7.
Tip
If the subtotal row is above the data then skip step 7 as, by default, alignment is set to Bottom Align.
Grouping and Outlining Rows and Columns
You can manually group selected rows and columns by clicking the Group button on the Data tab. This is helpful if you have a sheet designed for multiple users and you want to only show them rows and/or columns specific to the user. After the data is grouped, an Expand/Collapse button is placed below the last row in the selection or to the right of the last column in the selection.
Caution
An outline can only have up to eight levels.
If the data to be grouped includes a calculated Total row or column between the groups, you can use the Auto Outline option found in the Group drop-down. This option creates groups based on the location of the rows or columns containing formulas. If the data set contains formulas in both rows and columns, though, the option creates groups for both rows and columns. This tool works best if there are no formulas within the data set itself, unless you do want the groups to be created based off those calculations.
Use the Group option for absolute control of how the rows or columns are grouped. For example, if you have a catalog with products grouped together, users can expand or collapse each group to view the products, as shown in Figure 23.8. By default, the Expand/Collapse buttons appear below the data. To get them to appear above the grouped data, first apply a subtotal to the data set with Summary Below Data deselected. Then undo the change and apply the desired groupings.
To manually group rows with the Expand/Collapse button above the grouped data set, follow these steps:
1. Select a cell in the data set.
2. On the Data tab, click the Subtotal button.
3. A message might appear telling you that Excel cannot determine which row has column labels. Click OK.
4. In the Subtotal dialog box, deselect Summary Below Data and click OK.
5. Excel inserts subtotal rows in the data. Click the Undo button in the Quick Access toolbar to remove the rows. Although it might appear that you’ve just undone all the previous steps, these steps were required to configure where the outline icon would appear.
6. Select the first set of rows to group together. Do not include the header. For example, to create the Hitachi grouping in Figure 23.8, select rows 4 and 5 to group. To create the Haier group, select only row 2.
7. On the Data tab, click the Group button.
8. Repeat steps 6 and 7 for each group of rows.
Press the F4 key to repeat the last command performed in Excel. So, after grouping one set of rows, select the next group and press F4, select another group and press F4, and so on. As long as you don’t perform another command, pressing F4 groups the selected rows.
You can clear groups using one of two ways from the Data, Outline, Ungroup drop-down:
• Ungroup—Ungroups the selected data. This method ungroups a single row from a larger group if that is all that is selected.
• Clear Outline—Clears all groups on a sheet unless more than one cell is selected, in which case the selected item is ungrouped. If used on data that was subtotaled using the Subtotal button, the subtotals remain; only the groupings are removed.