10 Useful Tips for Summarizing Data Using Excel’s Subtotal Feature

by Avantix Learning Team | Updated: September 9, 2018

Applies to: Microsoft® Excel® 2010, 2013, 2016 and 365 (Windows)

You can insert subtotals in Microsoft Excel data sets or lists using the Subtotal feature (which appears on the Data tab in the Ribbon) to summarize data.

Below is the Subtotal dialog box:

Microsoft Excel subtotal dialog box.

Recommended article: No Mouse? Using Keyboard Only Navigation in Microsoft Office (Part 1: The Ribbon)

Check out the following tips when working with the Subtotals feature in Microsoft Excel.

1. You can’t use the Subtotal feature with Excel tables

If your data is an Excel table (created using the Table feature on the Insert tab or the Format tab in the Ribbon), you can’t use the Subtotals feature. To see if you data set is a table, click in the data and take a look at the Ribbon. If you see Table Tools Design in the Ribbon, your data set is an Excel table. Below is the Table Tools Design tab in the Ribbon:

Table Tools tab in Excel Ribbon.

2. Clean up your data set

Data sets should be set up correctly with unique column headings or field names in the first row, no merged cells and no blank rows or blank columns within the data set.

In order to use the Subtotal feature effectively, the data set must be clean or consistent. For example, if you have city data with Toronto spelled as Toronto, Tornto or Tononto, you would get 3 different subtotals. It’s best to clean up these types of inconsistencies first before inserting subtotals.

3. Always sort first and then add subtotals

Always sort your data set first and then subtotal by the first sort key. So if you want to subtotal by city, sort by city first and then add subtotals at each change in city.

4. Collapse or expand the outline

When you insert subtotals using the Subtotal feature, Excel applies an outline and an outline pane appears on the left of the worksheet. You can click the numbers on the top left to display different levels in the outline (also called collapsing or expanding the outline).

If you insert subtotals once, Excel creates three levels in the outline, but Excel can display up to eight levels.

The levels show the following if you insert one set of subtotals:

  • 1 displays grand totals
  • 2 displays subtotals and grand totals
  • 3 displays data, subtotals and grand totals

Below is the outline pane with 3 levels:

Microsoft Excel outline pane with 3 levels.

5. Remove subtotals when you no longer need them

You can remove subtotals at any time. Simply click in the data set (or the header row), click the Data tab in the Ribbon, click Subtotals in the Outline group and then click Remove All.

Below is the Subtotal dialog box (note the Remove All button at the bottom):

Excel remove all button in Subtotal dialog box.

6. Show or hide the outline pane

You can show or hide the outline pane by pressing Control + 8.

The outline pane will be removed automatically if you remove subtotals.

7. Apply multiple subtotal functions

You can apply multiple subtotal functions in your data set. For example, you may want to calculate the sum, average and number of orders for each product. In order to do this, you would need to sort by product and then insert subtotals 3 times and uncheck the Replace Current Subtotals option in the Subtotal dialog box.

Below is the Subtotal dialog box with Replace Current Subtotals unchecked:

Replace current subtotals button unchecked in Excel Subtotal dialog box.

8. Format subtotals and grand totals

It can be tricky to format subtotals and grand totals as users often accidentally format the data set or records as well.

To format only subtotals and grand totals:

  1. Click the appropriate number in the outline pane to display subtotals and grand totals.
  2. Select only visible cells by pressing Alt + ; (semi-colon). Alternatively, press Control + G to display the Go To dialog box, click Special to display the Go to Special dialog box, click Visible Cells Only and then click OK.
  3. Apply the desired formats such as bold or a cell style.
  4. Click the appropriate number in the outline pane to display all data.

Below is the Go to Special dialog box:

Excel Go to Special dialog box with Visible Cells Only selected.

9. Remove unwanted labels in subtotals

When you insert subtotals, Excel adds labels such as Total, Average and Count in the subtotal row.

To remove the labels, you can use Excel’s Find and Replace as follows:

  1. Click the column (such as column B) with the labels you want to replace.
  2. Press Control + H. The Find and Replace dialog box appears.
  3. Enter the label you wish to find (such as Average) in the Find What box.
  4. Ensure there is nothing entered in the Replace With box.
  5. Click Replace All or click Replace and then Find Next to go through each subtotal.
  6. Click OK and then click Close.

Below is the Find and Replace dialog box:

Excel Find and Replace dialog box to replace text in labels.

10. Copy subtotals and grand totals to another worksheet

If you collapse the outline and then try to copy only the subtotals and grand totals to another worksheet or workbook, Excel will copy the data set, the subtotals and grand totals by default.

To copy only the subtotals and grand totals:

  1. Click the appropriate number in the outline pane to collapse the outline and display only subtotals and grand totals. If you do not want to include grand totals, select the rows with the subtotals by selecting the row headings.
  2. Select only visible cells by pressing Alt + ; (semi-colon). Alternatively, press Control + G to display the Go To dialog box, click Special to display the Go to Special dialog box, click Visible Cells Only and then click OK.
  3. Press Control + C to copy the visible cells to the Clipboard.
  4. Click in a target cell (such as A1) in a new worksheet.
  5. Press Control + V to paste the cells. The visible cells will be copied down and to the right of the target cell.

The Subtotal feature is very useful for summarizing data in Excel. These tips will help you get the results you want.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, join our email list.

Recommended Microsoft Excel training

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW ALL COURSES >

Our instructor-led classroom training courses are delivered at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some courses may also be delivered at an alternate downtown Toronto location). Contact us if you’d like to arrange custom training at your office on a date that’s convenient for you.

To request this page in an alternate format, contact us.

Copyright 2018 Avantix® Learning Inc.

You may also like

How to Freeze Row and Column Headings in Microsoft Excel

As many Excel worksheets can become quite large, it can be useful to freeze row and column headings so that they are locked in place when you scroll through your worksheet. In Excel, you can freeze both row headings and column headings or just one.

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Summary
10 Excel Tips for Working with the Subtotal Feature
Article Name
10 Excel Tips for Working with the Subtotal Feature
Description
You can insert subtotals in Microsoft Excel data sets or lists using the Subtotal feature. Check out these 10 great tips which include showing and hiding subtotals, formatting subtotals, copying only subtotals and grand totals and more ...
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This