How to Add Subtotals in Excel (4 Easy Methods)

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.

Add Automatic Subtotals in Excel


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.

Add Automatic Subtotals in Excel

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

Add Automatic Subtotals in Excel

  • After sorting, the dataset will look like the picture below.

Add Automatic Subtotals in Excel

  • In the following step, go to the Data tab and select Subtotal.

Add Automatic Subtotals in Excel

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

Add Automatic Subtotals in Excel

  • Finally, click OK to see results like the image below.

Add Automatic Subtotals in Excel

  • Moreover, if you select 2, you will only see the totals of each product and the Grand Totals.

Add Automatic Subtotals in Excel

  • Also, if you click on 1, you will only see the Grand Total.

Add Automatic Subtotals in Excel

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.

Insert Multiple Subtotals in Excel

  • In the second step, select Sort from the Data tab in the ribbon.

Insert Multiple Subtotals in Excel

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

Insert Multiple Subtotals in Excel

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

Insert Multiple Subtotals in Excel

  • After clicking OK in the Sort window, the dataset will look like this.

Insert Multiple Subtotals in Excel

  • Now, go to the Data tab and select Subtotal.

Insert Multiple Subtotals in Excel

  • After that, select Product, Sum, and Sales Amount like the picture below.

Insert Multiple Subtotals in Excel

  • Then, click OK to add the first Subtotal to the dataset.

Insert Multiple Subtotals in Excel

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

Insert Multiple Subtotals in Excel

  • Finally, after clicking OK, you will see results like the screenshot below.

Insert Multiple Subtotals in Excel

  • Interestingly, if you click on 4, it will only show the totals.

Insert Multiple Subtotals in Excel


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.

Apply Subtotals in Excel Table

  • After that, right-click on the mouse to open the context menu.
  • Select Table and then Convert to Range from the context menu.

Apply Subtotals in Excel Table

  • A message box will pop up, click Yes to proceed.

Apply Subtotals in Excel Table

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

Apply Subtotals in Excel 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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo