Mursalin Ibne Salehin
In this article, we will learn how to add subtotals in Excel. It is very difficult to compute the sum or average of different groups in large datasets. In that case, applying the SUBTOTAL function manually will be a time-consuming task. Fortunately, Excel has a built-in Subtotal feature to help you out with this problem. So, without further ado, let’s explain the steps to add subtotals in Excel.
Download Practice Book
Download the practice here.
What Is Subtotal in Excel?
In mathematical language, the subtotal is a summation of a set of numbers. Suppose, you bought 3 apples at $10/unit and 6 oranges at $6/unit. Then, the total cost will be $66 and the subtotal buying price of apples will be $30 and for oranges, it is $36.
But in Excel, the Subtotal feature not only calculates the sum but also computes the average, count, product, and many more operations. To do so, it uses the SUM, COUNT, AVERAGE, MIN, MAX, and other functions. As the Subtotal feature requires grouping the dataset, you can easily find the desired information.
4 Methods to Add Subtotals in Excel
To explain the methods, we will use a dataset that contains information about the Sales Amount of some sellers. We will sort the dataset and use the Subtotal feature to know the total sales for each product and region.
1. Add Automatic Subtotals in Excel
In the first method, we will determine the subtotal of the sales amount for each product. Follow the steps below to know the whole process.
STEPS:
- In the first place, select any cell in the dataset.
- Then, go to the Data tab and select Sort.
- It will open the Sort window.
- Secondly, you need to sort the data. Here, we want to sort the data by Product in ascending order. So, we have selected Product in the ‘Sort by’ field and A to Z in the ‘Order’ field.
- Select Cell Values in the ‘Sort On’ field.
- Make sure you have checked the ‘My data has headers’ field.
- After that, click OK to proceed.
- After sorting, the dataset will look like the picture below.
- In the following step, go to the Data tab and select Subtotal.
- The Subtotal message will pop up.
- In the Subtotal dialog box, as we have grouped the data by Product, we need to select Product in the ‘At each change in’ field.
- Now, you need to select Sum in the ‘Use function’ field. You can also select different functions for other purposes.
- Select Sales Amount in the ‘Add subtotal to’ section.
- Finally, click OK to see results like the image below.
- Moreover, if you select 2, you will only see the totals of each product and the Grand Totals.
- Also, if you click on 1, you will only see the Grand Total.
Read More: How to Sort Subtotals in Excel (with Quick Steps)
2. Insert Multiple Subtotals in Excel
In Excel, you can also insert multiple subtotals. The steps are slightly different from the previous method. Here, we will use the same dataset. But we will apply the subtotal feature two times here. So, without any delay, let’s jump to the steps.
STEPS:
- First of all, select any cell in the dataset. We have selected Cell B4.
- In the second step, select Sort from the Data tab in the ribbon.
- Thirdly, select Product, Cell Values, and A to Z in the respected fields. We have selected Product because we want to sort data by Product.
- Next, select Add Level to add another level.
- After sorting by Product, we want to sort the data by Region. That’s why we have selected Region in the ‘Then by’ field.
- Also, select Cell Values and A to Z in the respected fields.
- Click OK.
- After clicking OK in the Sort window, the dataset will look like this.
- Now, go to the Data tab and select Subtotal.
- After that, select Product, Sum, and Sales Amount like the picture below.
- Then, click OK to add the first Subtotal to the dataset.
- Again, select Subtotal in the Data tab to open the Subtotal dialog box.
- To add the second subtotal in the same dataset, select Region in the ‘At each change in’ field.
- And most importantly, deselect the ‘Replace current subtotals’ section.
- Click OK to proceed.
- Finally, after clicking OK, you will see results like the screenshot below.
- Interestingly, if you click on 4, it will only show the totals.
3. Apply Subtotals in Excel Table
Unfortunately, you can not apply the Subtotal feature directly to an excel table. But we can convert the table into a range and then add subtotals to it. Let’s observe the steps below to know the process.
STEPS:
- In the beginning, select any cell in the table.
- After that, right-click on the mouse to open the context menu.
- Select Table and then Convert to Range from the context menu.
- A message box will pop up, click Yes to proceed.
- Now, the table is converted into a range.
- After that, apply Method-1 to add subtotals.
- After inserting the subtotals, press Ctrl + T to convert the range to a table.
- Finally, click OK to see results like the picture below.
4. Add Different Subtotals in Same Column
In the previous methods, we have added a single subtotal in a column and multiple subtotals in different columns. Here, we will add different subtotals in the same column. For that purpose, we will use a dataset that already contains a subtotal in the Product column. This was done in Method-1. We calculated the sum of the sales of the products. In this case, we will also determine the average.
Let’s pay attention to the steps below to know more.
STEPS:
- Firstly, go to the Data tab and select Subtotal.
- Secondly, select Average in the ‘Use functions’ field and deselect the ‘Replace current subtotals’ option. You don’t need to make any more changes.
- In the end, click OK.
- Finally, you will see results like the picture below. Here, the average sales amount for each product is also added.
How to Remove Subtotals in Excel?
To remove the added subtotals, go to the Data tab and select Subtotal. It will open the Subtotal message box. Then, select Remove All.
Read More: How to Remove Subtotal in Pivot Table (5 Useful Ways)
What Is Duplicate Grand Total Error in Excel?
Sometimes, you may see a duplicate grand total in the dataset. This happens due to the addition of the second subtotal. It also appears if there are errors in the calculated columns.
How to Remove Excel Duplicate Grand Totals?
You can use the IFERROR function to ignore the errors in the calculated column. For example, you can use the formula below:
=IFERROR(E3*D3,"")
Or, you can just hide the row that contains the duplicate grand total.
Things to Remember
In the above methods, the most important thing is that you need to select each field correctly in the Subtotal dialog box. Any wrong selection will cause incorrect results.
Conclusion
In this article, we have demonstrated 4 easy methods to Add Subtotals in Excel. We have used different cases to explain them. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
