Categories
Excel

Insert Subtotals For a Range of Data

In this tutorial, we will use Excel’s subtotal capability to create subtotal values for the sorted categories. You can quickly create subtotals by using the Subtotal command on the Data tab. This command inserts subtotals for each group of data in your worksheet, based on a column that you specify.

For example, if you have a sales report with data grouped by “quarter”, you can insert subtotals for each quarter to see the total “cost” amount for each one. Here are the steps to use the Subtotal feature via the Data tab:

  1. Sort your data by the column that you want to group by. For example, if you want to group by Quarter, sort your data by the Quarter column.
  2. Select your data range and go to the Data tab. Click on the Subtotal icon in the Outline group.
  3. In the Subtotal dialog box, choose the column that you want to group by from the At each change in drop-down list. For example, choose Quarter if you want to group by quarter.
  4. Choose the function that you want to use to summarize your data from the Use function drop-down list. For example, choose Sum if you want to sum up the cost (or choose Count, Average, Max, Min, etc.).
  5. Select the columns that you want to apply the function to from the Add subtotal to list. For example, select Cost if you want to sum up that column.
  6. Click OK. Excel will insert subtotal rows for each group of data and a grand total row at the bottom of the table.
Subtotal Groups

Excel will create subtotal groups (Quarter 1 Total, Quarter 2 Total, and so on) for your data and add an outline to the left of your worksheet. You can use the outline buttons to expand or collapse the subtotal groups.

Removing Subtotals

You can also remove the subtotals by clicking on Subtotal in the Data tab and then clicking on Remove All in the Subtotal dialog box.

What is the difference between Total and Subtotal?

Subtotal in Excel performs calculations (sum, count, average, etc.) on groups of data within a table. While the Total performs calculations on the entire range of data, regardless of the groups (for example, Grand Total).

Subtotal

  1. Insert Subtotals For a Range of Data
  2. Format Subtotal Rows Using Conditional Formatting
  3. Make the Subtotal Function Dynamic