Contents
Excel SUBTOTAL Function
When to Use the Excel SUBTOTAL Function in Excel
Excel SUBTOTAL function is particularly handy when you have a dataset with multiple rows of data and you have applied filters to it. These filters make it easy to view specific portions of your data, but what if you want to perform calculations on just the visible (filtered) cells?
It allows you to apply various functions like SUM, AVERAGE, COUNT, and more to visible cells, excluding those that are hidden by filtering.
That is where the SUBTOTAL function comes into play. It helps you avoid manually adjusting your calculations when you apply or change filters.
What Excel SUBTOTAL Function Returns
The Excel SUBTOTAL function returns a value based on the function you specify and the filtered range of cells. It performs calculations only on the visible cells and ignores those that are hidden by filtering.
Syntax of Excel SUBTOTAL Function
The syntax of the Excel SUBTOTAL function is as follows:
=SUBTOTAL(function_num, ref1, [ref2], …)
Input Arguments
Here are the key input arguments for the Excel SUBTOTAL function:
● function_num: This is a number that represents the specific function you want to apply. You can choose from a range of functions, such as SUM, AVERAGE, COUNT, and more.
You can choose a number between 1 and 11 or 101 and 111 to indicate which function you want to use with SUBTOTAL. When you pick a number from 1 to 11, it includes manually hidden rows, but numbers from 101 to 111 exclude them.
Note : No need to memorize the numeric value; it will be displayed when you enter the formula.
● ref1, [ref2], …: These are one or more references to the ranges or cells you want to include in your calculation. You can specify multiple reference ranges, separated by commas.
Extra Notes
● If you use a function_num between 1 and 11, SUBTOTAL takes manually hidden rows into account.
● If your function_num falls between 101 and 111, SUBTOTAL disregards manually hidden rows.
● In filtered lists, SUBTOTAL consistently ignores values in hidden rows, no matter the function_num.
● SUBTOTAL avoids counting other SUBTOTAL formulas in references to prevent duplication.
● When dealing with vertical data, SUBTOTAL considers values in hidden columns; however, in horizontal ranges, hidden column values are always included.
● If you have any 3-D references in your list, SUBTOTAL will show an error message as #VALUE!.
Examples of the Excel SUBTOTAL Function
Let’s take a look at a few practical examples of how to use the SUBTOTAL function in Excel.
Example 1: SUM of Filtered Data
Suppose you have a dataset with applied filters and you want to find the sum of the visible cells. You can use the following formula:
=SUBTOTAL(9, C5:C16)
In this case, 9 represents the SUM function, and C5:C16 is the range of cells you want to sum.
Example 2: Sum Excluding Hidden Rows
Suppose you want to find the sum of a range of data while excluding hidden values. You can use the following formula:
Formula: =SUBTOTAL(9, C5:C16) vs =SUBTOTAL(109, C5:C16)
Function code 9 will calculate the sum, including the hidden rows, while function code 109 will calculate the sum, excluding the hidden rows.
Example 3: Automatically Create SUBTOTALs
Imagine you have a dataset, and your goal is to generate automatic subtotals to understand the yearly and region-wise sales totals. You can achieve this by following these steps:
First, select the table of data you want to work with. Then, navigate to the ‘Data’ ribbon or menu in Excel. There, you will find the ‘Subtotals’ option.
Click on it to open the Subtotal dialog box.
In this dialog box, you can effortlessly define how you want your totals to be grouped and calculated.
For example, you can specify if you want to subtotal based on a specific column or criterion. Excel will then do the work for you, automatically generating the subtotals according to your selections.
This feature can save you a lot of time and effort when dealing with large datasets and needing organized, grouped totals for analysis or reporting.
Conclusion
In conclusion, the Excel SUBTOTAL function is an essential tool for working with filtered data in your Excel worksheets. It simplifies the process of performing calculations on visible cells while excluding hidden ones.
Whether you are dealing with financial data, inventory management, or any other Excel application, the SUBTOTAL function will save you time and ensure your calculations are accurate.
Remember that practice is key to mastering Excel functions. Experiment with different functions and reference ranges to become proficient in using the SUBTOTAL function for your specific needs.
Frequently Asked Questions (FAQs)
Q1: Can I use the SUBTOTAL function with functions like MAX or MIN?
A1: Yes, you can use the SUBTOTAL function with a variety of functions, including SUM, AVERAGE, MAX, MIN, and more. Simply specify the corresponding function_num to apply the desired function to the visible cells.
Q2: What happens if I apply the SUBTOTAL function to a range without filters?
A2: If there are no filters applied to the range, the SUBTOTAL function will behave like the standard function you have chosen. It will include all cells in the calculation.
Q3: Can I use cell references as function_num?
A3: No, function_num should be a numeric value representing the function you want to use. You can’t use cell references for this argument.
Q4: What if I want to include hidden values in my calculations?
A4: You can use function_num values from 1 to 11 to include hidden values in your calculations.
Q5: Can the SUBTOTAL function work across multiple worksheets?
A5: Yes, if you have a 3D reference that includes multiple worksheets, you can use the Excel SUBTOTAL function to perform calculations across those sheets by specifying the reference range accordingly.
Other Related Excel Functions
Mastering Excel SUM Function: Tips and Examples for Efficient Data Calculation (6 Examples)
Mastering Excel SUMIF Function: A Comprehensive Guide for Conditional Summation
Mastering Excel SUMIFS Function: A Comprehensive Guide to Conditional Summing
Join me on Instagram, YouTube and WhatApp Channel for your daily dose of valuable tips and tricks! Catch insightful videos that will enhance your knowledge and skills. Don’t miss out – follow me now!