Sanjida Ahmed
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.
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.
- As the data set is unsorted, we need to sort the data table first. To do that, go to Data and click on Sort.
- 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.
- Consequently, all the data will be sorted by Item like this.
- Now, we need to go to the Data tab and then select Subtotal from the Outline group.
- Consequently, a new dialogue box named “Subtotal” will open up.
- 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.
- 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)
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.
- As a result, a new dialogue box titled “Subtotal” will open up like this.
- 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.
- From the navigation pan, we can navigate through different levels of subtotals.
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.
- 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.
- Consequently, an additional subtotal showing the average subtotal will also be displayed inside the dataset.
- By using the Navigation pane on the left side, we can navigate through the subtotals like this below.
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)
Now if we go to the Data tab to add the Subtotal feature, we will see that the feature is unavailable.
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.
- After that, in the Table Design tab, select the Convert to Range option in the Tools
- Consequently, a new dialogue box will appear asking the Table to convert into a normal range. Here, click Yes.
- As a result, Excel will convert the 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.
- 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.
- Here, click As a result, your dataset will be converted into an Excel table even if it contains subtotals.
- You can also navigate through the subtotals using the Navigation pan from the left side.
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.
- As a result, a new dialogue box will appear named Subtotal. From here, click on Remove All.
- As a result, Excel will remove all the subtotals.
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.
