Insert Subtotals to the subgroups of data in an Excel Worksheet


When you have a list data in which you want to group or summarize the data, you can use the ‘Subtotal’ command present on the ‘Data’ tab under the ‘Outline’ group. For example, consider you have a list of data which gives the viewership numbers of four different sports spread over three different months. By using the Subtotal command on this list of data, you can group and summarize the data based upon each individual sport. i.e. you can calculate the total viewership of each sport individually spread over three months and then calculate the grand total of the viewership of all the sports combined spread over three months.


In this tutorial you will be guided on how to use the “Subtotal” command to group and summarize a particular data list.

NOTE: - before using the ‘Subtotal’ command, ensure that each column of the data range for which you intend to calculate the Subtotal is present under a row label represented by the first row, contains similar or like facts in all the columns and finally ensure that there are no blank rows and columns in the data that you have selected.


Below is the snapshot of the data list to which we apply the “Subtotal” command to group and summarize the data present inside it.

Figure 1

In the above data list we wish to insert subtotals for all the four subgroups each represented by the sport Football, Tennis, Formula 1 and Cricket.


To insert Subtotals, do the following

1) Click on any cell of the data list which you have selected for inserting subtotals.

2) Then click on the “Data” tab and then under the Data tab, in the ‘Outline’ group, click on the ‘Subtotal’ command.

Figure 2

3) Now in the ‘Subtotal’ dialogue box thus opened, if you like to insert page breaks between each subgroup, then select the checkbox that says “Page Break between groups” and click on the ‘OK’ button.

Figure 3

4) Now if you observe your worksheet, the subtotals will be inserted below each subgroup with the titles ‘Football Total’, ‘Tennis Total’, ‘Formula 1 Total’ and ‘Cricket’ total respectively and the Grand Total will be inserted at the last row of the data list.

Figure 4

5) The ‘Football Total’ in the data list specifies the total viewership of the Football Sport spread over three months Jan, Feb and Mar respectively. Similarly, the ‘Tennis Total’, the ‘Formula 1’ Total and the ‘Cricket Total’ each specify the total viewership of each sport spread over three months Jan, Feb and Mar respectively.

6) To display the summary of the Subtotals and the Grand Totals, use the Outline Symbols 1,2,3 contained in boxes present next to the row numbers.

Figure 5

7) In our illustration, if we click on the outline symbol ‘3’, then the entire summary of the data list will be displayed. If we click on ‘2’, the summary of the Subtotals and the Grand Total will be displayed and if we click on ‘1’, only the Grand Total will be displayed in the worksheet. Below are the snapshots of the data list taken when outline symbols 3, 2, and 1 were selected respectively.

Figure 6

Figure 7

Figure 8

To Delete Subtotals, do the following

1) Select any cell in the data range that contains Subtotals.

2) Then click on the ‘Subtotals’ command button present on the ‘Data’ tab under the ‘Outline’ category.

Figure 9

3) Now, in the ‘Subtotal’ dialogue box thus opened, click on the ‘Remove all’ button.

Figure 10

So, this is how you can insert Subtotals to the Subgroups of a data list present in an Excel Worksheet.



Read Other Applications