Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Calculate SUBTOTAL in Excel? 2 Useful Methods

How to Calculate SUBTOTAL in Excel? 2 Useful Methods

(Note: This guide on how to calculate SUBTOTAL in Excel is suitable for all Excel versions including Office 365)

SUBTOTAL is a function in Excel that helps you to calculate the average, sum, product, and other functions for the elements in the table.

Imagine that you are the sales manager of a particular sales firm. Your responsibility will be to keep track of the number of sales made in each division. If the data were less, keeping track of them would have been feasible. When there are a lot of divisions and units, keeping track of the accumulated sales report might be an exhausting task. In such cases, what do you do?

This is where the Excel SUBTOTAL function is used. 

In this article, I will show you what a SUBTOTAL function is, and how to use the SUBTOTAL function in Excel in two methods with examples.

You’ll Learn:

  • What Is SUBTOTAL Function in Excel?
  • Using the SUBTOTAL Function From the Main Menu
  • Using the SUBTOTAL Formula
    • Additional Case While Using the SUBTOTAL Formula

Watch our video on how to calculate subtotals in Excel

How to Calculate SUBTOTAL in Excel - Workbook
How to Calculate SUBTOTAL in Excel – Workbook

Related Reads:

How to Calculate Percentile in Excel? 3 Useful Formulas

How to Calculate Factorial in Excel? Along with 2 Easy Examples

How to Merge Cells in Excel? 3 Easy Ways

What Is The SUBTOTAL Function in Excel?

The SUBTOTAL function helps in grouping and organizing the contents and helps in performing operations based on the user preferences. One advantage of using this function is that the SUBTOTAL houses various operations like sum, product, average, variance, etc, which makes it easier to calculate. 

In addition to that, using the SUBTOTAL function in Excel allows you to perform operations separately on visible and hidden values.  

Using the SUBTOTAL Function From the Main Menu

This is a relatively easy method to use the SUBTOTAL function. Consider an example. The table consists of units of sales made by the company in one month categorized by region and commodity type.

Note: SUBTOTAL only works if the separation data in the table are grouped. For example, if you want to calculate the SUBTOTAL based on the branch, then the sales and commodities of the particular branch should be grouped.

Since we are calculating the SUBTOTAL based on the branch, we need to group the branch category. To do this we can use Sort.

Select the cells of the table. Right-click and select Sort. You can select ascending (A to Z) or descending (Z to A) depending on your preference. 

Sort the Data
Sort the Data

This sorts the table based on the branch. Since we selected the A to Z sort, the table is arranged in terms of East, North, South, and West.

Selected the Sorted Data
Selected the Sorted Data

Once the change values are grouped together, we can now calculate the SUBTOTAL.

Navigate to Data. Under Outline, click on SUBTOTAL.

Navigate to Data
Navigate to Data

This opens up a new SUBTOTAL dialog box. 

Select the necessary data and click OK
Select the necessary data and click OK

In the At each change in: dropdown, select the option which you have grouped together or categorized the rows. This is where the SUBTOTAL function performs on similar data. 

In this case, since we are calculating the SUBTOTAL based on different branches, we can select Branch. In case you want to calculate the SUBTOTAL based on the commodity type, you can select the commodity type from the dropdown.

Select at each change in:
Select at each change in:

The second option is the Use function:. We know that the SUBTOTAL function houses a variety of operations like sum, count, average, product, etc. You can choose your preferred operation from the dropdown.

Select the Function to use
Select the Function to use

Finally, in the Add subtotal to: dropdown, select the column you want the function to act on. In this case, we have selected the Sales Made because we want the total of sales made in each branch. 

If you want to calculate the average of any other data, you can select the option and the function will operate on it.

Select the Add subtotal to: checkbox
Select the Add subtotal to: checkbox

Now, click Okay.

How to Use the SUBTOTAL Function in Excel from main menu
How to Use the SUBTOTAL Function in Excel from main menu

This gives you the SUBTOTAL and the Grand Total for the selected value. From the table, you can see the SUBTOTAL computes the value depending on the operation based on the criteria. The Grand total is the total of all the values. In other words, Grand Total calculates the total of all SUBTOTALs. 

While projecting data, you might not want an extensive report containing all the elements. In such cases, you can use the 1,2,3 buttons or (+) (-) symbols to show or hide the data. 

Show or Hide the data
Show or Hide the data

For example, if you only want to project the Total Sales based on the branch, you can click on the (2) button. This hides the extensive details like the Commodity Type and the Sales Made and shows only the sales made based on the branch. You can also use the (+) or (-) symbols to hide or show data.

How to hide the unnecessary data
How to hide the unnecessary data

If you want only the total Sales Made in a month irrespective of the branch or any other criteria, just click on the (1) button. This hides the branch details and projects only the Grand Total.

Show only the Grand Total
Show only the Grand Total

Note: If you add any filters, the SUBTOTAL function only works on the filtered values. For the SUBTOTAL to operate on the values, clear all filters and the function adds and calculates the values.

Suggested Reads:

3 Best Methods to Find Duplicates in Excel

How to Use Excel Split Screen? 3 Simple Ways

How to Copy Only Visible Cells in Excel? 3 Easy Ways

Using the SUBTOTAL Formula

Another way to arrive at the SUBTOTAL in Excel is by using a formula. 

Consider an example where you have the data for the sales made on individual lifestyle products.

Note: The table containing the data should always be PivotTable or normal data entry. The SUBTOTAL function does not work on Excel tables. Learn how to create a PivotTable from our previous guide.

If you want to calculate the SUBTOTAL, enter the formula =SUBTOTAL (function_num, ref1, [ref2], …) where function_num is the operation you want to perform on the data, and ref1 through ref2 is the range of the data.

I will explain the formula using another example. Consider a table consisting of the Product, Units Sold, and the Total Price of the commodities of a lifestyle brand over a period of one month.

To calculate the SUBTOTAL, enter the formula in the destination cell.

Once you enter the =SUBTOTAL(, Excel automatically shows you suggestions for the operations that you want to perform on the data.

Enter the Formula
Enter the Formula

In this case, we want to calculate the sum of the Total Price. So from the dropdown, double-click on 9, or select Sum and press the Tab key. This adds the function argument you want to perform on the data.

Select the operation
Select the operation

Now, for the range argument. You can either enter the range or select the range and it will get populated. In this case, we want to perform the sum operation on the Total Price and have entered the range C4:C8.  

Pass the range
Pass the range

Press Enter.

This gives you the SUBTOTAL of the Total Price. 

How to Find Subtotal in Excel using the formula
How to Find Subtotal in Excel using the formula

Additional Case While Using the SUBTOTAL Formula

While entering the operating parameter for the SUBTOTAL formula, you might wonder why there are two sets of operations with the same name.

Using the formula, we can perform several operations like calculating the average of the values, counting the number of entries, and so on.

2 Sets of Operations and their functions
2 Sets of Operations and their functions

The first set of operations (1-11) is used to calculate the values present in the table even if they are visible or hidden. The second set of operations (101-111) is used to calculate only visible values. They ignore the values that have been hidden manually.

Note: The above method “Using SUBTOTAL Function from Main Menu” only takes the first set of operations. The SUBTOTAL function in the Data option only includes hidden values when performing operations. 

From the above example, the SUBTOTAL sum of the Total Price of products is 271,000. Imagine if you don’t want to calculate the Total Price of the sock and you don’t want to display the row. You can hide it by selecting the row, then right-clicking it, and selecting Hide. In this case, I have hidden row 8.

Hide the unwanted values
Hide the unwanted values

Since we used the first set of operations from the above example, we can see the total price is the same even though we have hidden the sock row. 

Use the first set of formula
Use the first set of formula

Now, let us enter the second set of operations to calculate the SUBTOTAL. To calculate the sum of the products, we have passed the argument and operation number as 109. This ignores the hidden cell and gives the sum value of the visible cells.

Use the second set of formula
Use the second set of formula

In case the row is unhidden, the formula automatically takes the value into account and adds it to the SUBTOTAL.

The formula automatically updates when row is unhidden
The formula automatically updates when row is unhidden

Also Read:

How to Count Cells with Text in Excel? 3 Different Use Cases

How to Hide Formulas in Excel? 2 Different Approaches

How to Select Non Adjacent Cells in Excel? 5 Simple Ways

Frequently Asked Questions

Can we calculate subtotal for rows in Excel?

The subtotal function is only meant for vertical ranges and columns. You cannot use the SUBTOTAL function to calculate the subtotal and grand total for rows. 

What are the features of the SUBTOTAL function in Excel?

Using the SUBTOTAL function you can calculate the average, sum, count, min, max, and product of values in a range. You can select the functions from the dropdowns when calculating SUBTOTAL from the main menu or you can use them in terms of their corresponding numbers in the operation parameter to get the desired value from the selected columns when using the formula.

Why are there two sets of operations in the SUBTOTAL formula?

The first set of functions(1-11) is used to calculate the SUBTOTAL irrespective of the visibility of cells. The other set of functions(101-111) only operates on the visible cells and ignores any hidden values.

Closing Thoughts

In this article, we saw what SUBTOTAL is, and how to use the SUBTOTAL function to calculate the values using 2 easy methods.

Using the SUBTOTAL helps to easily calculate multiple values, perform different operations on large tables, and help present them in an easy aesthetic way.

If you need more high-quality Excel guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Randomize a List in Excel? 2 Different Methods

How to Create an Estimate in QuickBooks 2018

How To Use Fill Function in Microsoft Excel 2013

How to Use the CEILING Function in Excel? A Step-by-Step Guide

Getting Started with Power Pivot: Advanced Excel

How to Convert XML to Excel? – 2 Easy Methods 

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)