How to Use SUBTOTAL in Excel – Shiksha Online

How to Use SUBTOTAL in Excel – Shiksha Online

4 mins read1.0K Views Comment
Rashmi Karan
Manager - Content
Updated on Mar 3, 2023 13:26 IST

The article discusses how to use the SUBTOTAL function in Excel.


SUBTOTAL in Excel is used to summarize values and exclude the filtered or hidden rows. The interesting thing is that we can indicate to the SUBTOTAL function the type of operation that we want to apply to the values. If you have a table with sales by region and city, you could calculate the subtotal for each of the regions using the same syntax. 

In SUBTOTAL, you can not only calculate the sum, but also use multiple arithmetics, and logical operations like average, maximum, or other available operations. SUBTOTAL function in Excel can be used instead of SUM, COUNT, MAX, etc. to ignore hidden rows, either by filter or manually.


What is the SUBTOTAL function in Excel?

The SUBTOTAL function in Excel helps us to calculate the subtotal of a list of values. The SUBTOTAL function allows creating a list with the SUBTOTAL command in the Outline group on the Data tab. Once you create the subtotal list, you can easily modify it by just editing the SUBTOTAL Function.

If we take a closer look at the Subtotal dialog box, we can see that we have several options. We will be able to select the field for which we want to make a total, choose one of the eleven available functions, the field to which we want to add the subtotal, replace the current subtotals, add page breaks or include/exclude the summary below the data.

Read MS Excel Tutorials

At each change in allows you to select the headers.

Use function allows you to select from functions like SUM, AVERAGE, etc.

This option serves as a kind of wizard for the SUBTOTALS function.  

Must Explore – MS Excel Courses

How to use the SUBTOTAL function in Excel?

The SUBTOTAL function always requires a numeric argument (1 through 11, 101 through 111) as its first argument. This numeric argument is to be applied to subtotals of the values ​​(cell ranges, named ranges) specified as the following arguments.  

Formula for SUBTOTAL in Excel


To use the SUBTOTAL function, the following parameters must be indicated:

function_num ( Required ): 1 through 11, 101 through 111 indicates the Excel function to use. For example, 1 & 101 = AVERAGE, 9 & 109 = SUM.

ref1 ( Required ): The first range or cell to consider in the operation.

ref2 ( Optional ): The second range or cell to be considered in the operation up to a maximum of 254 ranges.

Note: The numbers 101-111 ignore manually hidden rows, while 1-11 include manually hidden rows.

How To Use Slicer In Excel – Shiksha Online
How To Use Slicer In Excel – Shiksha Online
The article discusses the use of Slicer in Excel to sort and filter huge datasets
How to Create MIS Reports in Excel?
How to Create MIS Reports in Excel?
MIS reports are crucial to measure the business performance and contribute towards meeting the overall business goals. In this article, you will learn easy steps to create an MIS more
Standard Deviation In Excel – Shiksha Online
Standard Deviation In Excel – Shiksha Online
The article discusses standard deviation in Excel.

SUBTOTAL Function_num

Function_num (includes hidden rows) Function_num (ignores hidden rows) Function
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

Rows Hidden by a Filter

1. For example, we use the SUM function to calculate the total profits monthwise.

2. We will now apply a filter. Here we keep the data just for Jan, Feb, and March. Let’s see how it impacts the total SUM.

We can see that the SUM function only displays the data available in the visible cells. 

Related – How To Create A Pivot Table In Excel

3. Now we will apply the SUBTOTAL function, which ignores rows hidden by a filter and calculates the total result.

Where you can see that the AutoComplete feature of Excel allows you to locate the function_num without having to remember it. So in this data set, we need to use the function_num for SUM, which is 109. We will select 109 and then select the cells for calculation.

You can see how the SUBTOTAL function sums up the correct data and ignore the hidden cells.

Top MS Excel Interview Questions
Top MS Excel Interview Questions
Master your Excel interview with confidence using our curated list of top questions. From basic functions to advanced data analysis, boost your Excel proficiency and shine in any data-driven role.
Most Popular and Powerful Formulas in Excel
Most Popular and Powerful Formulas in Excel
Excel has various functions ready to process your information to get the desired results. Familiarize yourself with each of them. Once you master various functions, you can create advanced more
How to Use Solver in Excel – Shiksha Online
How to Use Solver in Excel – Shiksha Online
The article covers the Solver in Excel. Learn how to use Solver in Excel through examples and facilitate decision-making.

Similarly, you can take out the Average, Count, Standard Deviation, Min & Max, etc. Here I just replaced the function_num to get the Average of the given data.

Press Enter to see the Average of the data set.

Manually Hidden Rows

1. For example, we can also calculate a range of cells using the SUM function if the rows are hidden using the Hide feature in Excel.

Now we will hide rows manually.

Now we will apply the SUBTOTAL function. The SUBTOTAL function ignores manually hidden rows and calculates the correct result.

The Subtotal Function automatically ignores the manually hidden rows.

Tip – If you are working with very large databases, the best option is to use pivot tables and use slicers to sort and analyze the data.

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel

Download this article as PDF to read offline

Download as PDF
About the Author
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio


We use cookies to improve your experience. By continuing to browse the site, you agree to our Privacy Policy and Cookie Policy.