In this article, we would learn all about the SUBTOTAL function in excel. We would cover some background about usage, syntax, arguments along with examples of SUBTOTAL function in excel.
Background – Why SUBTOTAL Function
In order to understand why SUBTOTAL function come into existence, let us take one example.
In the below image, you can see a list of number along with its total (calculated using SUM excel function).
Now, suppose I filter out the number 13 and keep the rest numbers visible, like this:
Did you see any change in the total of numbers? I say No 😎
You would notice that the total/sum does not change after filtering the data. The sum (114) remains the same even after you apply filter on the data.
Similarly, the SUM or other mathematical functions does not work on hidden excel rows. The excel behaves in a similar way when you try to manually hide the excel rows. Excel ignores hidden rows while calculating a total or sum of numbers.
Due to this limitation of the mathematical functions, Microsoft introduced a new function named the SUBTOTAL formula in excel. Using the Excel SUBTOTAL function, you can find the total of only visible rows of cells in excel.
Let’s see how 😎
When to Use SUBTOTAL Function in Excel
The Excel SUBTOTAL formula is used to find and get the SUM, AVERAGE, COUNT, PRODUCT, etc. of the values in only visible cells or rows in excel.
It means that excel ignores the hidden and filtered rows while calculating the total/average etc. of values in the excel cell. It only returns the subtotal value in a list.
Although this function is named as SUBTOTAL, it does not mean only SUM. This function has an ability to perform many other mathematical functions like AVERAGE, COUNT, etc.
Syntax and Arguments
=SUBTOTAL(function_num, ref1, …)
There are mainly two arguments of this function:
- function_num – In this argument, enter the number specifying which function to perform on the list. This denotes the type of subtotal (listed and explained in the later part of this article).
- ref1 – Specify the range of cells on which you want to apply the subtotal type (function_num). It accepts cell range or a named range.
Similarly, you can use more than one ‘ref’ arguments.
Basic Example of SUBTOTAL Function in Excel
Let us now look at one basic example to understand how the SUBTOTAL function works in excel.
Taking the same sample data, that we took in the background section above.
To find the total using the SUBTOTAL function, simply use the following formula:
As a result, excel returns the sum as 114.
Now, try to filter out some values from the data, and see its impact on the subtotal. You would notice that the total value updates automatically when you apply the filter. Now, the new value is 75, as shown in the image below:
In the above example, the function_num value 9 means SUM. To learn more about other function_num values, read below.
Calculations & Functions in SUBTOTAL Formula
As learned in the ‘Syntax and Arguments’ section above, the behavior of the SUBTOTAL function is controlled by its function_num argument.
Using the function_num argument, you can specify excel about what calculation should SUBTOTAL function perform and how.
There are in total 11 calculations that can be performed using the SUBTOTAL formula. Each of these 11 calculations has two variants “1 to11” and “101 to 111”. These two variants differ from each other in a way that how the function behaves for manually hidden rows/columns.
- The Function_num value between 1 and 11 denotes that excel will include the values in manually hidden rows while subtotalling.
- The Function_num value between 101 and 111 denotes that excel will exclude the values in manually hidden rows while subtotalling.
The table below lists all the calculations that the SUBTOTAL formula can perform along with its values.
Note that the “filtered out” rows are always excluded regardless of the function_num argument used. The function_num only controls the manually hidden values. It has no effect on filtered ones.
Do Not Miss These Points
- The SUBTOTAL function works perfectly on the vertically arranged dataset or table. On a horizontally designed table, the SUBTOTAL function does never exclude hidden rows.
In the image below, I have arranged the numbers horizontally and hide the columns E and G. You will notice that the SUBTOTAL function’s output value still remains the same.
- The SUBTOTAL feature is often useful while working with Excel Tables.
- In addition to the excel SUBTOTAL formula, check out the article on SUBTOTAL ribbon feature. Using the SUBTOTAL ribbon feature, you can quickly insert subtotals and also use the plus (+) and minus (-) buttons to explode and collapse the rows.
Thank You 🙂