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.

  1. Select 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.
  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.

PRYOR+ 7-DAY FREE TRIAL


Experience 7-days of unlimited learning, then reap the benefits from a year of training at one low price. No credit card. No commitment. Individuals and teams.


Choose a Seminar and Save $10:

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small> Basics

Microsoft® Excel® Basics

LIVE ONLINE SEMINARS

Jun. 1

Central Time Zone

Jun. 6

Eastern Time Zone

Jun. 7

Central Time Zone

Jun. 9

Pacific Time Zone

Jun. 13

Central Time Zone

Jun. 15

Central Time Zone

Jun. 16

Eastern Time Zone

Jun. 20

Central Time Zone

Jun. 21

Pacific Time Zone

Jun. 23

Eastern Time Zone

Jun. 28

Eastern Time Zone

Jul. 6

Pacific Time Zone

Jul. 7

Central Time Zone

Jul. 11

Eastern Time Zone

Jul. 12

Central Time Zone

Jul. 14

Central Time Zone

Jul. 18

Pacific Time Zone

Jul. 20

Central Time Zone

Jul. 21

Eastern Time Zone

Jul. 25

Eastern Time Zone

Jul. 26

Central Time Zone

Jul. 27

Central Time Zone

Aug. 1

Pacific Time Zone

Aug. 3

Central Time Zone

Aug. 4

Eastern Time Zone

Aug. 9

Central Time Zone

Aug. 11

Central Time Zone

Aug. 15

Eastern Time Zone

Aug. 16

Central Time Zone

Aug. 18

Pacific Time Zone

Aug. 23

Central Time Zone

Aug. 25

Pacific Time Zone

Aug. 29

Eastern Time Zone

Aug. 31

Central Time Zone

Sep. 7

Central Time Zone

Sep. 12

Central Time Zone

Sep. 13

Pacific Time Zone

Sep. 15

Eastern Time Zone

Sep. 19

Central Time Zone

Sep. 21

Central Time Zone

Sep. 22

Eastern Time Zone

Sep. 26

Pacific Time Zone

Sep. 27

Central Time Zone

Sep. 29

Central Time Zone

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>: Beyond the Basics

Microsoft® Excel®: Beyond the Basics

LIVE ONLINE SEMINARS

Jun. 2

Central Time Zone

Jun. 7

Eastern Time Zone

Jun. 8

Central Time Zone

Jun. 10

Pacific Time Zone

Jun. 14

Central Time Zone

Jun. 16

Central Time Zone

Jun. 17

Eastern Time Zone

Jun. 21

Central Time Zone

Jun. 22

Pacific Time Zone

Jun. 24

Eastern Time Zone

Jun. 29

Eastern Time Zone

Jul. 7

Pacific Time Zone

Jul. 8

Central Time Zone

Jul. 12

Eastern Time Zone

Jul. 13

Central Time Zone

Jul. 15

Central Time Zone

Jul. 19

Pacific Time Zone

Jul. 21

Central Time Zone

Jul. 22

Eastern Time Zone

Jul. 26

Eastern Time Zone

Jul. 27

Central Time Zone

Jul. 28

Central Time Zone

Aug. 2

Pacific Time Zone

Aug. 4

Central Time Zone

Aug. 5

Eastern Time Zone

Aug. 10

Central Time Zone

Aug. 12

Central Time Zone

Aug. 16

Eastern Time Zone

Aug. 17

Central Time Zone

Aug. 19

Pacific Time Zone

Aug. 24

Central Time Zone

Aug. 26

Pacific Time Zone

Aug. 30

Eastern Time Zone

Sep. 1

Central Time Zone

Sep. 8

Central Time Zone

Sep. 13

Central Time Zone

Sep. 14

Pacific Time Zone

Sep. 16

Eastern Time Zone

Sep. 20

Central Time Zone

Sep. 22

Central Time Zone

Sep. 23

Eastern Time Zone

Sep. 27

Pacific Time Zone

Sep. 28

Central Time Zone

Sep. 30

Central Time Zone

Advanced Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>-Macros, PivotTables, Charts and More

Advanced Microsoft® Excel®-Macros, PivotTables, Charts and More

LIVE ONLINE SEMINARS

Jun. 3

Central Time Zone

Jun. 8

Eastern Time Zone

Jun. 14

Eastern Time Zone

Jun. 16

Central Time Zone

Jun. 22

Central Time Zone

Jun. 23

Pacific Time Zone

Jun. 27

Eastern Time Zone

Jul. 6

Central Time Zone

Jul. 12

Pacific Time Zone

Jul. 13

Eastern Time Zone

Jul. 18

Central Time Zone

Jul. 19

Eastern Time Zone

Jul. 28

Eastern Time Zone

Jul. 29

Central Time Zone

Aug. 2

Eastern Time Zone

Aug. 9

Pacific Time Zone

Aug. 12

Central Time Zone

Aug. 17

Eastern Time Zone

Aug. 18

Central Time Zone

Aug. 22

Central Time Zone

Aug. 25

Eastern Time Zone

Aug. 31

Pacific Time Zone

Sep. 9

Central Time Zone

Sep. 14

Eastern Time Zone

Sep. 15

Pacific Time Zone

Sep. 19

Central Time Zone

Sep. 27

Eastern Time Zone

Sep. 28

Central Time Zone

Related Excel® Articles

PRYOR+ 7-DAY FREE TRIAL


Experience 7-days of unlimited learning, then reap the benefits from a year of training at one low price. No credit card. No commitment. Individuals and teams.

Categories