Leading the way in Microsoft Office Development

What are Subtotals ?

Subtotals are a way of providing additional aggregate information within a table of data.

They provide a way to quickly add up the values in each field and is much quicker that trying to use the SUM() worksheet function.

Automatic subtotal formulas can be added to a table that is setup as a database.

You can use Excel's automatic subtotals to quickly summarise the data in your database.

This feature can be used to calculate both subtotals as well as grand totals and can be accessed by selecting (Data > Subtotals).

This allows you to quickly insert the SUBTOTAL() worksheet function combined with Outlines to help you summarise your information.

Preparing your Data

Before you can add subtotals to your data you must check that all the rows you want to subtotal are next to each other.

The quickest way to do this is probably by sorting the data.

Our datacontains our food categories in the first column so our table will be grouped by cetegory.

Excel monitors the values in the first column and every time this value changes a subtotal is inserted.

Adding Subtotals

If you want to add subtotals in the middle of your table then select a single cell in your table before selecting (Data > Subtotals).

At each change in - Allows you to choose the column or field which you would like to subtotal. This list corresponds to the column labels at the top of your table.

Use function - Allows you to select the type of function you want to use for the subtotals:

Add subtotal to - Identifies the columns in which the subtotals will appear.

Replace current subtotals - Removes any existing subtotals in the list before applying the new subtotals.

Page break between groups - Inserts page breaks automatically after each group of subtotalled data.

Summary below data - Inserts the subtotal and grand total rows below the detail data.

To remove all of them select the "Remove all" check box.

You can have a subtotal with more than one summary function. Add the first function and then select (Data > Subtotals) choosing the additional function from the "use function" drop down box.

You can create nested subtotals by selecting and data and choosing (Data > Subtotals) and choosing a different labels from the "At each change in" drop-down box.

If you filter on a particular value, Excel will automatically generate a row of SUBTOTALs at the bottom of the filtered results. The first parameter in the SUBTOTAL(FncNum, Range) is the summary function used in the subtotal.

Data | Subtotals lets you do Outlining specifying the data breaks and what kind of subtotals you want at those breaks. The Outlining plus and minuses at the left let you control the level of detail that is displayed.

You may want to Copy the SUBTOTAL results and Paste Special | Values to preserve the results for additional calculations.

When you've filtered data or hidden rows or columns, selecting a block of cells will also, disappointingly, select the cells that are not visible. There is a hidden command that will let you select only the visible cells. It's hidden in that you have to add this command to your toolbar in order to use it.

Right-click in the gray area of the toolbar to the right of Help

Choose Customize

Go to the Commands tab

Choose the Edit category

Scroll all the way to the bottom of the list of Edit Commands

You should find the Select Visible Cells command

Click and drag this command and drop it where you want it to appear on a toolbar.

Now anytime you have cells hidden, you can click the Select Visible Cells icon to select all the currently visible cells WITHOUT selecting those cells you can't see.

This feature actually inserts SUBTOTAL() functions into each of the summary rows.

They formulas then automatically update when you modify the corresponding values.

If you want to remove all the subtotal from a table select (Data > Subtotals) and select the Remove All button.

Things to Remember

You can have several layers of subtotals or subtotals for smaller groups within your original subtotal groups.

You can also use the Subtotals feature to calculate the average, minimum or maximum values in each category.

An alternative to using Subtotals is to use a Pivot table. A pivot table might be more appropriate if you have a lot of data and you want the want more flexibility with the formulas.