Unlocking SUBTOTAL Feature in Excel

To mathematically define the term SUBTOTAL, it is nothing but a summary total. It can be said to be a total before a grand total. The sum total of the subtotals makes a grand total. The excel has also provided with inbuilt functionality to automatically insert the subtotals in a table and generate a summary based on the criterion we specify. In this blog, we would unlock the subtotal feature in Excel with the help of an example.

Let us first make our sample dataset ready for understanding this feature

Sample Dataset

In the below screenshot you can see a table that shows the number of units sold in the month of January and February product wise (Apple, Banana, Grapes, and Watermelon).

SUBTOTAL Feature - Sample Data

The SUBTOTAL feature in Excel can help you to find the totals before grand total based on the criterion like Total units sold in the month of January or in February. And even going to a more minute level, we can find the total at the product level.

Subtotal Feature in Excel

You Must Know These Points

Before starting to learn this feature, one must make sure that the dataset table is structured one having proper headings and without any blank rows in the dataset. 

The sample dataset screenshot as you can see is the perfect example of the dataset table for using the subtotal feature in Excel. The table has a heading row (Row 1) and there are no blank rows in between the table records. The data is contiguous.

Now, when you know the purpose and the pre-requisites of using the SUBTOTAL feature, let us now start navigating this feature.

Where is the SUBTOTAL Feature in Excel?

To navigate to the SUBTOTAL feature follow the below path:

‘Data’ tab > ‘Outline’ group > ‘Subtotal’ button.

SUBTOTAL Feature Navigation

Using the SUBTOTAL Excel Feature

Let us first try to insert the subtotals for the month (January and February). Follow the below steps:

Click on any of the cells in the data set table A1:C15.

Then, navigate to the Subtotal option. As a result, you would notice the following:

  1. The excel would predict the table range and select the entire table area (A1:C15)
  2. Secondly, it opens the ‘Subtotal’ dialog box.

Let us take a look at each of the options in this dialog box one by one:

  • At each change in – Here, we need to select the criterion on the basis of which the subtotal is to be done. As in the present case, we want to add subtotal by the months first, therefore, select the option ‘Month’ from the drop-down options.
  • Use Function – Here we need to select the operation that you want to perform like sum the values, or find an average or count them, and so on. As in the present case, we want to find the total units sold by months, therefore, select the operation – ‘Sum’. 
  • Add subtotal to – Here, we need to tick the checkboxes to which you want the excel to perform the sum or selected operation. In our case, we have selected the column ‘Units Sold’. You may select more than one checkboxes if you have more columns like sales amount, etc.

As soon as you click on the ‘OK’ button, you would notice that the Excel would automatically insert the subtotals at each month and perform the selected operation (Sum) to the selected column heading (Units Sold).

Result of SUBTOTAL Feature

Insert More Than One SubTotal in Excel

In the previous section, we learned to insert the subtotal at month level for January and February. Now, let us learn to insert more than one subtotal in a table in Excel. For example, let us now add a subtotal product-wise.

Follow the below steps to achieve the same.

Click on any of the cells in the range A1:C15 and then again navigate to the subtotal option (tab – Data).

In the ‘Subtotal’ dialog box, set the ‘At each change in’ as ‘Product’ (as now we want to have the subtotal at the product level). The operation would be ‘Sum’ and the total to be added to the ‘Units Sold’.

The important point here is to note that you need to uncheck the checkbox ‘Replace current subtotals’. By unchecking this, we are instructing excel not to remove the existing subtotal (at month level) and insert this new subtotal additionally.

Uncheck Replace Current Subtotals

As a result, you can see that the excel would additionally insert a new subtotal based on the product (without deleting the previous one – at month level).

Multiple Subtotals Result

Now let us learn to insert the subtotals above the data record.

Inserting Subtotal Above Data Records in Table

To insert the subtotals above the data records in the table, you need to just uncheck the option ‘Summary below table’ in the ‘Subtotal’ dialog box and the task is completed.

Refer to the below screenshot.

Getting Subtotal Above Data

As a result, the subtotals would appear above the data in the table.

Result of Summary Above Data

Generally, this is not used when you are creating a report for analysis as the grand totals also get placed above the table which is not generally accepted format by the report user.

Remove Subtotal in Excel

To remove the subtotals in excel, click on any of the cells in the table. Then navigate to the ‘SubTotal’ dialog box.

There, you would find a button named ‘Remove All’. Click on this button and as a result, the excel removes all the subtotals in the dataset.

This brings us to the end of this blog. Share your views and comments in the comment section below.

Leave a Comment