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.
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).
Step 3: In the pop-up Subtotal box,
- 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).
- 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.
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.
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.
It will remove all the subtotal values of your dataset.
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.