Skip to content

SUBTOTAL FUNCTION

Summary

Subtotals will provide a total each time the data in the desired column changes. For example your sales report contains, say Dept 1 Dept 2, …..Dept 9, it will add a subtotal when the column with Jan changes to Feb.  This page is for the Subtotal feature that is found in the ribbon. Subtotals in a Pivot Table is covered in the Pivot Table pages.

In Using the Function

  • The column that creates the subtotal must be sorted as Excel reads down that column and when it changes, it creates the subtotal function.
  • It will create subtotals and a grandtotal.
  • In the sales report, noted above the report must be sorted on the Dept column.
  • When Excel sees that column data changing it adds the SUBTOTAL function in the desired column(s) with the data.
  • It ignores cells existing subtotal functions avoiding double counting.
  • It ignores cell that have been filtered and are not visible.
  • It’s not limited to SUM, other calculations may be AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, VARP
  • While it’s summing the desired data, it enters the SUBTOTAL function

Rules

  • The subtotals are entered when the data in the column changes. Example, Excel scans down the Sport column, when it sees Baseball, it then inserts a row above Baseball and enters the =SUBTOTAL function
  • This means that you must organize the data with the categories to be subtotaled as such. In this case Golf, Baseball, etc.  Sorting is one method of accomplishing this. You may need to use the Custom Sort feature.
  • If you find that the Subtotal function is too restrictive, then use a different function or set of commands, such as SUMIF or Pivot Tables
  • The Subtotal command will appear grayed out if you are working with a Microsoft Excel table. To add subtotals in a table, you must first convert the table to a normal range of data, and then add the subtotal. Note that this will remove all table functionality from the data except table formatting.

Steps

  • Organize the data.
  • Place the cursor in the data
  • Click on Data (in the menu), then in the Outline group:
    • Click on Subtotal
  • Make the appropriate choices in the pop up menu such as
    • Use the SUM function
  • Add Subtotals to the:
    • Units Sold column
    • Monthly Sales column

Additional Notes

  • Grand Total are derived from the detail data, not from the subtotal values
  • The result is in the Excel Outline format so that you can display or hide rows for each subtotal
  • Formatting-The text in the first column is Bolded by Excel, but the subtotaled numbers are not.  You can add Bold or other desired formatting by applying Conditional Formatting or with a Macro
 

Data File

The data file contains several exercises and sample data along with explanations. Review each sheet tab in the file.

Subtotals.xlsx