How to Insert Subtotals in Excel

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 dive 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.


The Easiest Way to Insert Subtotals in Excel

Here you will learn the ultimate way of how to insert subtotals in Excel. The steps to do that are described below,

Step 1: Select the range of data that you want as the subtotal category.

Step 2: Go to the tab Data -> Subtotal (in the Outline command tool).

insert subtotals in excel from command tool

Step 3: In the pop-up Subtotal box,

subtotals window

  • 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 name, so we picked Name as the category).
  • Under the Use function label, from the dropdown box, select the function name that you want to apply to your dataset (In our case, we wanted to know the summation of the data, so we picked SUM as the function).

You can pick any function that you need by scrolling down the scroll bar from the Use function dropdown list (see the picture below).

selecting more functions by scrolling down to insert subtotals

  • Under the Add subtotal to label, select the check box beside the names containing values that you want to use to know the subtotal results (In our case, we wanted to know the subtotal value of each member, so we picked the Name option as the subtotal column).
  • If you already have an existing subtotal result and you want to remove that, then select the check box beside Replace current subtotals, otherwise, clear the check box (To understand the difference, see the fig. 1 & 2).
  • If you want to insert an automatic page break for each subtotal, then select the Page break between groups check box, otherwise keep it unmarked.
  • If you want your subtotal results at the bottom of each category, then select the Summary below data check box, otherwise, uncheck the box.
  • Click OK.

fig. 1: subtotal values with the marked Replaced current subtotals check box

subtotal results of the dataset

fig. 2: subtotal values with the unmarked Replaced current subtotals check box

This will produce the subtotal result of each and every category of the dataset along with the Grand Total of your entire dataset.

Grand Total = Summation of all subtotal values.


Remove Subtotal

If you no longer need the subtotals, simply follow the steps below,

Step 1: Select the range of data.

Step 2: Go to Data -> Subtotal.

Step 3: Select the Remove All from the bottom-left side of the Subtotal pop-up box.

to remove subtotals from excel

It will remove all the subtotal values of your dataset.


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 if you have.


You May Also Like to Explore

Sanjida Ahmed

Hello, this is Sanjida, an Engineer who loves Sports a lot. Here I try to solve Excel problems with you. Hope I could be a great help to you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo