Insert Subtotals in Excel

Subtotals are dynamic in that they adjust instantly based on filters. They are very useful when reviewing a table, as you can make small adjustments to see different perspectives on the information.

Create a Subtotal

  1. Determine which column you want to subtotal.
  2. Sort the selected column (Column A in this example) so that the like items in it are grouped. This will let you generate a subtotal for each item group in the column.
  3. Click the Subtotal button in the Outline group on the Data tab to launch the Subtotal dialog box.

Insert Subtotals Image 1

    1. Insert Subtotals Image 2Select the column you chose to subtotal by from the At each change in: dropdown menu.
  1. Select how you would like the data in your selected column
    subtotaled from the Use function: dropdown menu. The selected function is applied to the data in the column(s) you select from the Add subtotal to:Some of the most common selections are:
  • Sum: Adds the data in the selected column.
  • Count: Counts and displays the number of rows that contain data in the selected column.
  • Average: Computes an average from the data in the selected column.
  • Max and Min: These selections identify the largest or smallest number in the selected column.Insert Subtotals Image 3
  1. In the Add subtotal to: box, select the columns that will be subtotaled. The default selection is the final column.
  2. Below the Add subtotal to: box are additional options for subtotal layout. Click a checkbox to select the associated option:
  • Replace current subtotals: Replaces any existing subtotals on the sheet with the ones you create here.
  • Page break between groups: Puts each subtotal on its own printed page.
  • Summary below data: Places the subtotals at the bottom of each data group and places the grand total at the bottom of the data. If unchecked, the subtotals appear above the data groups and the grand total appears at the top of the data.
  1. Click the OK button to generate your subtotals and Grand Total.
  2. Use the controls in the left margin to expand or collapse the data for each group. You can also click the control next to grand total to collapse all data.
  3. To remove subtotals from your list, click the Subtotals button and click Remove All in the Subtotal dialog box.

Insert Subtotals Image 4