The SUBTOTAL function in Excel performs a specified calculation (e.g., sum, average, count) on a range of data, but unlike other similar functions, it adjusts automatically when the dataset is filtered. This means you can exclude hidden rows (either filtered or manually hidden) from the calculation.
Syntax of SUBTOTAL Function in Excel
=SUBTOTAL(function_num, ref1, [ref2], ...)| Argument | Description |
|---|---|
function_name(required) | A number that specifies the type of calculation, usually a function. (e.g., 1 for AVERAGE, 9 for SUM) |
ref1(required) | The first cell range to include in the calculation. |
[ref2](optional) | Additional cell ranges to include if needed. |
Function Names in Excel SUBTOTAL Function
The number (1–11 or 101–111) specifies the function to use in the subtotal. Numbers 1–11 include manually hidden rows, while 101–111 ignore them; filtered-out cells are always excluded.
| function_name | Function | Description | |
|---|---|---|---|
| 1 | 101 | AVERAGE | Calculates the average of cells. |
| 2 | 102 | COUNT | Counts numbers in cells. |
| 3 | 103 | COUNTA | Counts non-empty cells. |
| 4 | 104 | MAX | Finds the maximum value in cells. |
| 5 | 105 | MIN | Finds the minimum value in cells. |
| 6 | 106 | PRODUCT | Multiplies all cell values. |
| 7 | 107 | STDEV | Calculates standard deviation of cells. |
| 8 | 108 | STDEVP | Population standard deviation for cells. |
| 9 | 109 | SUM | Adds up cells. |
| 10 | 110 | VAR | Variance of cells. |
| 11 | 111 | VARP | Population variance of cells. |
SUBTOTAL vs Other Functions
SUBTOTAL outperforms basic functions like SUM or COUNT. Here’s a comparison of SUBTOTAL and other functions.
| Feature | SUM/COUNT | SUBTOTAL |
|---|---|---|
| Includes filtered data only | No | Yes |
| Ignores hidden rows | No | Yes (101-111) |
| Versatile (multiple calculations) | No | Yes |
SUBTOTAL Function – Tips and Best Practices
- Use with Filters: Always use SUBTOTAL in datasets where you frequently filter or hide rows for more accurate calculations.
- Quick Access from Ribbon: When you apply filters, Excel’s status bar shows subtotals for selected cells by default. You can quickly copy these formulas by using the SUBTOTAL function.
- Combine with Tables: Convert your dataset into an Excel Table. Tables automatically integrate filtering, and SUBTOTAL works seamlessly within them.
Conclusion
The SUBTOTAL function is a powerful tool that every Excel user should master. Its ability to dynamically adjust to filtered and hidden data makes it invaluable for analyzing datasets in real-time. Be it summing sales, counting entries, or finding the maximum value, SUBTOTAL helps keep your calculations accurate and flexible.
Frequently Asked Questions (FAQs)
-
What is the Syntax of SUBTOTAL Function in Excel?
The SUBTOTAL in Excel is a versatile tool for performing various calculations on a filtered data set.
=SUBTOTAL(function_num, ref1, [ref2], ...)is the syntax of SUBTOTAL in Excel. -
What is the Difference Between SUM and SUBTOTAL Function?
The
SUMfunction adds all values in a range, regardless of whether the rows are hidden or filtered. TheSUBTOTALfunction, on the other hand, adjusts based on filters and can optionally ignore hidden rows. -
How to Exclude Hidden Rows using SUBTOTAL?
To exclude hidden rows, use a
function_numbetween 101 and 111. For example, to sum visible rows only:=SUBTOTAL(109, A1:A10). -
Can SUBTOTAL handle multiple ranges?
Yes, SUBTOTAL can work with multiple ranges. For Example
=SUBTOTAL(9, A1:A10, B1:B10). This sums up the visible rows from both ranges.
