How to insert subtotals in Excel

  • Post author:
  • Post category:Uncategorized

Inserting subtotals can be done manually, where we insert a subtotal for each group of items in our list.  We can also use the Excel SUBTOTAL function, which offers several functions to be used to insert subtotals.  

Figure 1. Excel SUBTOTAL function

However, this task could become tedious when handling large data sets.  Fortunately, Excel has a tool for subtotals that makes inserting subtotals in Excel so much easier.  

Figure 2.  Final result:  How to insert subtotals in Excel

The image above shows that we are able to summarize data in a range of data by inserting subtotals.  This article shows how to insert subtotals in Excel for versions 2016, 2013 or lower.  

How to insert subtotals in excel?

Suppose we want to insert a subtotal for the sum of sales for each group of items in column B:  Pen, Pencil and Marker. We follow these steps:

Step 1.  Select the whole list/database B2:D11

Figure 3.  Sample data to insert subtotals in Excel

Remember to include the headings in the selection.  Otherwise, we might come up with erroneous results for subtotals.

Step 2.  Click Data tab, then Subtotal button in Outline Tools

Figure 4.  Subtotal button in Data > Outline tools

Step 3.  The Subtotal dialog box will appear.  The Subtotal tool offers several functions like Sum, Count, Average, Max, Min, Product and even standard deviation functions.  

Figure 5.  Subtotal functions in Excel

For this example, enter the following values in the textboxes:

At each change in:   Item

Use function: Sum

Add subtotal to: Sales

We choose Sum because it is the function that we want for the calculation of our subtotals. We choose Sales because it contains the values we want to subtotal.  In summary, we want to insert subtotals that show the sum of sales for every item in our list.  

Figure 6.  Insert subtotals using the SUM function

The resulting table below shows that we have successfully inserted subtotals in Excel, for Pen, Pencil and Marker.

Figure 7.  Output:  Insert subtotals in Excel