In this tutorial, we will learn about Excel SUBTOTAL Function. This is one of the powerful, easiest and most useful function in Excel. This function increases the usability of some basic functions.
What is SUBTOTAL Function in Excel?
The SUBTOTAL is a most useful and versatile function in Excel. This function works dynamically in a large dataset where data filters frequently.
This useful function works fine when values are the hidden or filtered format. We can use this function as a substitute for SUM, COUNT, COUNTA, etc.
|We have a “Complete List of SUBTOTAL Function”, see in this blog further.|
Why we use Excel SUBTOTAL Function?
Suppose that there is a sales column in a dataset. At the bottom of this column, we applied SUM function to get the total sales value for this entire range.
Now apply filters to find the total sales value of some selected items. Doing this will disappear the data which are not get selected.
Using filters, select a few line data and press “OK”, see at the bottom, the total sales value does not change at all and remain the same.
Excel SUM function includes hidden values in its result.
The SUBTOTAL function has different options for visible or non-visible cells.
There is 11 basic function store in this function, these function cannot do great alone but with SUBTOTAL function, do wonders.
Excel SUBTOTAL function Charts Explain
First column heading is “Include hidden values” and second has “Exclude hidden values”.
When needing a result including hidden values or invisible cells, use function_num from 1 to 11.
Whereas result for excluding hidden values or visible cells, use function_num from 101 to 111.
We will learn the use of these values further in this tutorial.
|Note: – No need to remember these numbers, a list will appear when to apply this functions.|
What is the syntax?
=SUBTOTAL (function_num, ref1, [ref2])
Type “=SUBTOTAL” in excel and a list will appear to choose the option, what kind of function we want to apply. We can choose the option from this chart. There are 22 options to choose in this list.
Function_num = this is the number from 1 to 11 and 101 – 111 for the desired result.
A complete function_num list is mentioned above, where each number explained for its use. (See SUBTOTAL Chart above).
Ref1 (Required) = this the range where we need to find the subtotal, a range could be from 1 to 254, first one is required and rest optional.
Returns as result?
This function returns the result for invisible and visible values for the applied function.
|Use function_num from 1-11 for result including hidden values,
For a result of the visible cell only, use function_num 101-111.
This function works equally for invisible and visible values when filters are applied.
The SUBTOTAL function works differently for manually hidden values.
This function works with the vertical data only, in horizontal data, values in hidden columns will be included.
Always ignores nested subtotal values in its the grand subtotal.
Excel SUBTOTAL Function for Hidden Rows or Values
Filters are the great tools in excel, we can get a great result by applying simple filters. There are two methods to hide data in a dataset.
Method 1 – Data Hidden by Manually
How this function will react when we hide some data manually from the dataset?
In the below image we have applied SUBTOTAL for visible and invisible data.
Both results are the same as data is normal as not hidden form nor filters applied.
To hide some rows in the dataset, select entire rows to hide and press “Right” click and then choose “Hide” option form the list and then press “OK”.
At the end of the dataset, we have applied two SUBTOTAL functions for different uses.
- SUBTOTAL (9, B2: B10) – this function will make a sum of all visible and invisible values in the column.
- SUBTOTAL (109, B2: B10) – this function will make a sum of all the visible cell only.
See the image below when values are hidden and their impact on the result.
Notice that here data is hidden by the manual method
Method 2 – Data Hidden by Applying Filter
Now apply filters and select some particular data, see the difference in result.
Select header and apply filters by pressing “CNTR +SHIFT+L” together, see below:-
Applying filters only for some particular regions, like – “Mid-West”, “South” and “West” and press “OK”. Once the “OK” button pressed, our dataset will look like below image:-
Only selected data is appearing, where rest of the data is in hidden form.
This image will clear your doubts clear, we applied SUBTOTAL for invisible and visible values.
Here, both the SUBTOTAL function are providing the same result.
By this example, we can understand that SUBTOTAL function works differently for data hidden by the manual and filtered method.
What is the difference in (9) and (109) in SUBTOTAL?
The difference between Excel SUBTOTAL (9) and (109) is as follows:-
SUBTOTAL function (9) is used to make a sum of all visible and invisible cell values, whereas function_num (109) for visible cell only.
Excel SUBTOTAL vs SUM function
Excel SUBTOTAL and SUM function do the same job if data is not hidden by manually or filtered ways.
SUM function only sums the value in an entire range where filters are not applied. When filters apply, there is no guarantee of the result drawn by this function.
Whereas SUBTOTAL function can provide a result for both kinds of data, manually hidden or filtered.
See in the below image where both functions are applied separately and result drawn by them.
SUM function includes hidden values in its total whereas SUBTOTAL function ignores hidden values and sum only visible cells.
Apply SUBTOTAL Function from Excel Ribbon
First of all, sort the total data according to alphabetically, as shown in the below-given image: – by Home –> editing group, click on “Sort & Filter”. Doing this SUBTOTAL function will be easy to apply.
After sorting the data, click anywhere within the dataset where we want to apply “SUBTOTAL”. Then go to DATA tab –> go to Outline Group –> Click on “SUBTOTAL”
Once we click on the SUBTOTAL button, the following picture will appear:-
A new box will appear with many options for SUBTOTAL, like on which column we want to apply it.
First of all, chose the area (reason) for which we want to find SUBTOTAL, choose from “East”, “Mid-West”, “South”, etc.
Secondly, “Use the function” list, choose from SUM, COUNT, and COUNTA etc.
The third option is: – “Add subtotal to” – This option belongs to column values, which one we want, either for “Sales” or “Profit“.
Replace current subtotals:- this will replace SUBTOTAL if previously applied.
This will apply page break between groups.
The summary below data: – This option will drag the summary under each group.
Press the “OK” button to execute subtotal and “Remove all” for removing the applied subtotal.
Press “OK” and following image will appear:-
SUBTOTAL Function Ignores Nested Values
Excel SUBTOTAL function always ignores nested values created by subtotal execution.
See the nested values within the small box in the SUBTOTAL table.
Here in the above image, we have already applied “SUM” and “SUBTOTAL” functions for comparing both at the bottom of the table.
In this image, we can see that the grand total of the table values is equal to the value retrieved from the SUBTOTAL function. Whereas Excel SUM function is including all the nested values.
Excel SUM function is adding every cell value including nested values, which is not justified at all.