How to Summarize Subtotals in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Summarizing the subtotals in Excel helps us to understand the overall output and group-based output at a time that would take a lot of time if we calculated individually. We’ll show 3 easy methods in this article to summarize subtotals in Excel with sharp steps and clear illustrations.


What Is Subtotal in Excel?

The subtotal in Excel helps to create different groups according to the dataset. Also, it has the feature to hide or show the summary of any subgroup.


How to Summarize Subtotals in Excel: 3 Suitable Ways

To explore the methods we’ll use the following dataset that contains some salespersons sales in different regions.

how to summarize subtotals in excel


1. Applying Subtotal Command to Summarize Data

First, we’ll use the Subtotal command of Excel to summarize the data that you will get in the Outline section of the Data ribbon.


How to Apply Subtotal Command

In this section, we are going to see the way to apply the Subtotal command. Let’s proceed to the procedure.

Steps:

  • Before applying the Subtotal command we’ll have to sort the column properly for which you want to find subtotal otherwise you won’t get the output accurately. We’ll find it in the Salesperson column. Select the data range from the column and click as follows: Home > Editing > Sort & Filter > Sort A to Z.

Sorting Data to Summarize Subtotals

  • Then to apply the Subtotal command, click as follows: Data > Outline > Subtotal.

Soon after you will get the Subtotal dialog box.

Applying Subtotal Command to Summarize Data

  • Next, from At each change in the drop-down box, select the header of the column for which you want to calculate the subtotal.

Applying Subtotal Command to Summarize Data

  • Later, from the Use function drop-down box select the function which you want to apply to get the subtotal. We’ve selected Sum.

Applying Subtotal Command to Summarize Data

  • After that, choose the column where you want to add subtotal.
  • Then mark your required option from the three marking options given below in the dialog box.
  • Finally, just press OK.

Applying Subtotal Command to Summarize Data

Now have a look, we got the subtotal based on the salesperson column. It grouped our data and showed the total sum for every salesperson. The grouping has 3 layers, the above one is the third layer which shows everything in detail.

  • Click on 2 and it will take you to the second layer which will show only every salesperson’s total and grand total.

  • After clicking on 1 you will get the 1st layer and that will show only the Grand Total.

Read More: How to Group and Summarize Data in Excel


How to Show or Hide Nested Subtotal

You can easily show or hide any nested group from any layer of grouping that will help you to customize your summary.

Steps:

  • To hide any group or nested group, just click on the minus sign (-). I hid Robin’s individual sales here.

Applying Subtotal Command to Summarize Data

See, it’s only showing the Total for Robin.

  • To get back to it again, click on the plus sign (+).

Applying Subtotal Command to Summarize Data

Soon you will get back the summary.

Read More: How to Summarize Text Data in Excel


2. Using SUBTOTAL Function to Summarize Subtotals

Now we’ll summarize the subtotal using the SUBTOTAL function. It will allow you to apply a group of functions by an argument k for summarizing data. But the problem is that it can’t make groups like the previous method and has to apply separately for each operation. Here, we’ll find the sum, average, and max value for the summary.

Using SUBTOTAL Function to Summarize Subtotals

Steps:

  • Insert the following formula in Cell D14, to sum up the grand total-
=SUBTOTAL(9,D5:D12)
  • Later, just hit the ENTER button for the output.

Using Excel SUBTOTAL Function for Summing to Summarize Subtotals

  • Then for the average, apply the following formula in Cell D15
=SUBTOTAL(1,D5:D12)
  • Press the ENTER button to get the average sales.

Using SUBTOTAL Function to find Average when to Summarize Subtotals

  • Lastly, to get the maximum sales, type the following formula in Cell D16
=SUBTOTAL(4,D5:D12)
  • Hit the ENTER button to finish.

And then you will get the summary like the image below.

Read More: How to Summarize Data Without Pivot Table in Excel


3. Using Excel Status Bar to Summarize Subtotals

There’s another exclusive way in Excel by which you can get the summary of subtotals very quickly at a glance. The lowest bar under the Tab bar is called the Status Bar, whenever you select a data range you will see some summary in the status bar like Average, Count, Sum, etc.

Steps:

  • Just select the data range from your preferred column and then just take a look at the Status Bar and you will get the summary there. My status bar shows the Average, Count, and Sum.

Using Excel Status Bar to Summarize Subtotals

  • By right-clicking on the Status Bar, you can select your required functions from the context menu for the summary.

Selecting functions for Subtotals from context menu

Read More: How to Create a Summary Sheet in Excel


How to Remove Subtotal Command

Removing the subtotal command that we applied in the first method is quite easy. Follow the easy procedures step by step.

Steps:

  • Select any data from the dataset.
  • Next, click as follows: Data > Outline > Subtotal.

  • After getting the Subtotal dialog box, just click on Remove All.

Now see, the subtotal operation is removed.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


Conclusion

That’s all for the article. Hope the above procedures will be good enough to summarize subtotals in Excel. Feel free to ask any questions in the comment section and give your feedback. Visit our site to explore more about Excel.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo