Learn Excel’s Subtotal feature

Most used functions

Learn Excel’s Subtotal feature

Article updated on December 21, 2017

You can group rows or columns manually using the "Group" feature. That will tie a range of cells together so they can be collapsed or expanded. First you need to prepare your data, remove blanks, sort the data and insert subtotals manually.

Inserting subtotals manually and grouping rows are time consuming tasks but they can be done automatically if you use "Subtotal" feature instead. Here is how.

Example data.

Sort data

Select A1:C17
Right click on your selection
Click "Sort"
Click "Custom Sort..."
Sort by "Salesperson"
Add a Level
Then by "Region"
Click OK

Insert Subtotals

Remember that you can´t insert subtotals if you are working with an excel table. You must convert the table to a normal range.

Select cell range A1:C17
Go to tab "Data" on the ribbon
Click "Subtotal" button
Click "OK"

This picture shows subtotals, a grand total and symbols so you can quickly collapse/expand rows.

Click symbol to collapse rows and click symbol to expand rows.

You can also expand or collapse all rows on a hierarchy level by pressing 1, 2 or 3:

Subtotals on multiple levels

You can add more subtotals to your data list. We inserted subtotals before when a change occurred in column Salesperson, let's add a new subtotal for each change in column Region.

Select cell range A1:C20
Click "Subtotal" button on tab "Data"
Click the drop down menu below the "At each change in:"
Select Region
Clear checkbox "Replace current subtotals"
Click OK

Add data bars

Now it is time to spice up your data table with some chart bars. Hold on, I am not going to insert a chart. Conditional Formatting can provide the magic for this occasion.

Create a new column and type =C2 in cell D2.
Copy cell D2 to the cells below
Remove cells that link to subtotals and grand total
Select cell range D2:D24
Go to tab "Home" on the ribbon
Click "Conditional formatting" button
Click "Data Bars"
Click "More Rules..."
Enable checkbox "Show Bar Only"
I chose a "Solid Border" from the "Border" drop down menu.
Click OK

Remove all subtotals

You can easily remove all subtotals, select the cell range you want to remove subtotals from. Go to tab "Data", click "Subtotal" button.

Click "Remove All" button.

Build a pivottable/pivotchart

A pivot table allows you to summarize a data table in many different ways, using different criteria. It is easy and you will be analyzing your data in no time. Pivottables are subtotals on steroids.

I highly recommend reading this blog post: Analyze trends using pivot tables, there is also a quick introduction to pivot tables.

You can also compare data, year to year, month to month and whatever time frame you like.

Download excel *.xlsx file

Subtotals on multiple levels.xlsx

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Auto refresh a pivot table

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

Identify numbers in sum using Excel solver

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]

How to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]