How to Insert Subtotals in Excel (4 Easy Methods)

Excel is widely used to store important numeric information of students or employees. So it is often needed to extract a group of subtotal values from a large dataset to understand the actual scenario. In this article, we will show you how to insert subtotals in Excel.


What Is Subtotal?

Before diving into the calculation part, let’s know the meaning of subtotal.

In generic terms, subtotal refers to the total of one set of a larger group of sets. For example, suppose you got 100 marks in your last semester, where the marks of the math course were obtained from the three class tests that you had. In the first class test, you got 10, in the second you got 15, and in the last class test, you got 20. So now you want to know only your math score out of the total 100 marks. To get that easily, you can use subtotal.

Similarly, in Excel, you can use the subtotal function to break down a larger set of data into a smaller set and then perform various other Excel functions such as SUM, AVERAGE, MAX, MIN, COUNT, PRODUCT, etc. to achieve the desired result.


Download Workbook

You can download the practice workbook from here.


4 Useful Methods to Insert Subtotals in Excel

Here you will learn the 4 simple ways on how to insert Subtotals in Excel in different circumstances. For illustration, I have taken a dataset containing some sales information about 2 products (Soap and Detergent) of a company in different regions.

Dataset of Subtotals feature in Excel

Now, we will apply the Subtotal feature to this data set to obtain the total sales amount of different subcategories such as Total Sales of Soap and Detergent. To do that, we will use 4 different methods. Let’s explore those methods one by one.


1. Insertion of Single Subtotal Feature in Excel

In this method, we will use the simplest way to insert the Subtotals feature in Excel. To know more, follow the steps below.

Steps:

  • First, we need to select the range of data that we want as the subtotal category. Hence, we select B5:E11.

 

Selection of data set

  • As the data set is unsorted, we need to sort the data table first. To do that, go to Data and click on Sort.

Choosing Sort option from the data tab

  • As a result, a new dialogue box will open up titled “Sort”. From here, we need to choose the column upon which we want to sort the data. Here, we have chosen Then we clicked OK.

Sorting data by item.

  • Consequently, all the data will be sorted by Item like this.

Sorted data

  • Now, we need to go to the Data tab and then select Subtotal from the Outline group.

Choosing the Subtotal option from the Data tab

  • Consequently, a new dialogue box named “Subtotal” will open up.

Choosing different options from the subtotal dialogue box

  • From here, we need to pick some parameters based on which the Subtotal will be applied.
  • Under the ‘At each change in label, from the dropdown box, select the category name that you want your dataset to be sorted (In our case, we wanted to sort the data according to Item, so we picked Name as the category).
  • In the ‘Use function’ section, we need to choose the function that we want to use to calculate the Subtotal. Here, we can use SUM, MAX, MIN, AVG, and other functions. As I want to sum the Sales Amount, I picked SUM.
  • Lastly, on Add subtotal to:, we need to pick the Column on which the Function will perform. Here, I selected the Sales Amount.
  • If you already have one or more subtotals in your data set and you want to keep them, unselect Replace current subtotals. Otherwise, make sure that this option is selected.
  • If you want to add Page break between groups, click on it.
  • Finally, If you want to have a Summary below data, select the last option. And then, click OK.
  • Consequently, we will see that our data set now has the Subtotal feature like this below.

Result after insertion of subtitle feature in Excel

  • From the picture, we can see that the Total Sales Amount of Detergent and Soap has been subtotaled.
  • On the left corner, there is a navigation tab from where we can navigate across the data set. (See the animated figure below)

Navigating through the subtotals from the navigation pan on the left side.


2. Insertion of Multiple Subtotals in Different Columns

In this method, I am going to show how we can add more than one subtotal in Excel.  Here, in the previous section, we have already shown how we can apply subtotal upon the Item column. So, in this section, we will just continue from there and add another subtotal based on Region. To do that, follow the steps below.

Steps:

  • First, select the whole data set, then go to the Data.  And from there, select Subtotal.

Choosing the subtotal option from the data tab

  • As a result, a new dialogue box titled “Subtotal” will open up like this.

Choosing different parameters from the subtotal dialogue box in Excel

  • In the dialogue box, as we know that we want to create a Subtotal based on the Region column, we choose Region under the “At each change in” label. The rest of the labels will be the same as in the previous section.
  • Most importantly, we must uncheck the Replace current subtotals as it will ensure that the existing subtotals will not be deleted.
  • Finally click As a result, the data will contain multiple subtotals.

Result after inserting multiple subtitles in the data set.

  • From the navigation pan, we can navigate through different levels of subtotals.

Navigating through the multiples subtotals in excel by navigation pan


3. Addition of Multiple Subtotals in the Same Column

In this section, I will add a subtotal with a different function to an existing subtotal that we have created in the 1st section. This time, we will use the AVG function to get the average of the subcategories. To do that, follow the steps below.

Steps:

  • First, select the dataset that already contains a subtotal with the SUM Then go to the Data tab and then select Subtotal.

choosing the subtotal option from the data tab in Excel

  • As a result, a new dialogue box will appear titled Subtotal. Here, we do not need to change anything from what we have done in the first section except in the Use Function In the Use Function label, we chose Average because we want to obtain a subtotal of the average in the same column (Sales Amount).
  • Another important thing is to uncheck the Replace current subtotals It will ensure that the previous subtotals remain in the worksheet.
  • Finally, click OK.

Choosing different parameters from the subtotal dialogue box in EXcel

  • Consequently, an additional subtotal showing the average subtotal will also be displayed inside the dataset.

Result after applying multiple subtotal features in Excel

  • By using the Navigation pane on the left side, we can navigate through the subtotals like this below.

Navigating through differents subtotals in Excel


4. Application of Subtotals in Excel Table

Unfortunately, we can not directly apply the Subtotal feature in Excel Table. For illustration, we have taken a sample table. (See the figure below)

Insertion of subtotal feature in Excel table.

Now if we go to the Data tab to add the Subtotal feature, we will see that the feature is unavailable.

The unavailability of the subtotal feature in the Excel table

Now, in this circumstance, to apply the Subtotal feature, we need to follow the steps below.

Steps:

  • First, we need to convert the table into a range. To do that, click anywhere on the table. As a result, a new tab will appear in the ribbon named Table Design. Then, go to the Table Design tab.

Opening Table design tab from Excel table

  • After that, in the Table Design tab, select the Convert to Range option in the Tools

Converting Excel table into range

  • Consequently, a new dialogue box will appear asking the Table to convert into a normal range. Here, click Yes.

Converting the table into a normal range in Excel

  • As a result, Excel will convert the table into a normal range.

Result after converting an Excel table into a normal range

  • Now that we have converted the table to a normal range, we can easily follow the steps described in the first section to add subtotal.

The result after applying subtotal feature in Excel table

  • Now, we can reconvert the dataset into an Excel table by selecting the whole dataset and then pressing Ctrl+T. As a result, a new dialogue box will pop up like this.

Reconverting normal range to Excel Table with subtotals

  • Here, click As a result, your dataset will be converted into an Excel table even if it contains subtotals.

Reconverting normal range to Excel Table with subtotals

  • You can also navigate through the subtotals using the Navigation pan from the left side.

Navigating through subtotals in Excel table.


Removal of  Subtotal in Excel

If you no longer need the subtotals, simply follow the steps below to remove the subtotal(s).

Steps:

  • Select the range of data. Then go to the Data tab and select Subtotal.

Selecting the entire dataset in Excel for removing subtotals

  • As a result, a new dialogue box will appear named Subtotal. From here, click on Remove All.

Removing existing subtotals from Excel.

  • As a result, Excel will remove all the subtotals.

Results after removing subtotals in Excel

Read More: How to Remove Subtotal in Pivot Table (5 Useful Ways)


Things to Remember

  • Always try to pick the right options from the Subtotal Dialogue box as any incorrect choice will result in an undesired subtotal.

Conclusion

In this article, you have learned how to insert subtotals in Excel in the most effective and easiest way. I hope that this article has been very beneficial to you. Feel free to ask any questions you have.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo