In the previous article we discussed the SUBTOTAL function https://windsongtraining.ca/an-example-of-using-the-subtotal-function-in-excel/ https://windsongtraining.ca/an-example-of-us…unction-in-excel/‎. In this post we will look at the SUBTOTAL command found on the Data tab in the Outline group.  Each Excel function provides a specific calculation.  We use any of the over 500 functions that Excel provides by typing an = sign in a cell followed by the function name and the arguments that the function requires. Excel commands are initiated by clicking with the mouse while functions require more comfort with programming.

The SUBTOTAL command allows us to deal with large quantities of data more quickly than the SUBTOTAL function. It automatically creates groups and uses one of the 11 common functions like SUM, PRODUCT, COUNT, AVERAGE, MIN, and MAX to summarize data.

In this article, we will look at the example of the SUBTOTAL command used for the data set for the top 25 car sales by country in 2020 (https:/.factorywarrantylist.com/car-sales-by-country.html) (Figure 1).

Figure 1

We would like to know:

  1. How many top brand vehicles were sold on each continent?
  2. How many vehicles were sold of each brand?

Let’s address the first question: How many top brand vehicles were sold on each continent?

The easiest way to answer the above question is to sort data by continent and then use the SUBTOTAL SUM function.

Step 1: Apply the Filter command in the top row of the data set (Figure 2).

Figure 2

Step 2: Apply a filter in column C and sort A to Z (Figure 3).

Figure 3

 Figure 4 shows the data set sorted by continent.

Figure 4

Step 3: Use the SUBTOTAL SUM command:

  1. Select the whole table,
  2. Go to the Data tab,
  3. Click on the SUBTOTAL command (Figure 5).

Figure 5

Step 4: Lets’ look at the SUBTOTAL dialog box. Our question was: How many vehicles were sold on each continent? We need to set up the dialog box according to what we are looking for (Figure 6).  The first dropdown will have the title of each column and you have to select the title that you want.    In the selection box you have to click the box that you want to have the sum which was selected in the second dropdown.  You can leave the other values at default and click ok.

Figure 6

After clicking “OK “in the Figure 6 dialog box, Excel produces the requested subtotals (Figure 7).

Figure 7

The second question that we were interested in was: How many vehicles were sold of each of the top brands?

Since we already inserted filters in the top row of the table we will start from:

Step 2: Apply a filter in column E (Top Brand) and sort A to Z (Figure 8).

Figure 8

Step 3:Use the SUBTOTAL command to sum up the sales by the top brands (Figure 9). Set up the dialog box according to what we are looking for.   

Figure 9

After clicking “OK “in the Figure 9 dialog box, Excel produces the requested subtotals (Figure 10).

Figure 10

The great advantage of using the subtotal command is that we can add subtotals that use the same function to more than one column, at the same time.

To show you how this feature works, I added 2 more columns to our example; the number of second top brand cars sold in 2020 and the second top brand per top 25 countries (Figure 11)

Figure 11

The first, second and third steps remain the same: we add and apply a filter for the continent and use the subtotal command on the Data tab for the top brand and second top brand of the number of cars sold in 2020 at the same time (Figure 12).

Figure 12

After clicking “OK” we get the following results (Figure 13).

Figure 13

The SUBTOTAL command can be much faster to use than the SUBTOTAL function when we have data with a large number of groups to subtotal. Note that the SUBTOTAL command separates the groups and generates a grand total for us automatically, with the click of a button.