How to use SUBTOTAL in Microsoft Excel
SUBTOTAL is the sum function in Excel recommended by experts. Here’s what to know about How to use SUBTOTAL in Microsoft Excel.
When it comes to calculating sums in Excel, the SUM function is probably the first thing that comes to mind. However, the SUM function is only effective with small spreadsheets. If dealing with large data, use SUBTOTAL.
Why is the SUM function not the optimal choice?
The SUM function adds all values, regardless of whether they are visible or not. However, when you filter data or hide some rows, SUM will still include those hidden numbers. The result is an inflated total that silently distorts your reports. This is where most people get stuck.
Meanwhile, SUBTOTAL can handle all the tasks of those basic functions without causing common troubles.
How does SUBTOTAL in Excel work?
Imagine you have a sales data table with hundreds or thousands of rows. Maybe you just want to see sales of “Product A“, so you filter data on that column. Other rows will be hidden, but the SUM function will not “understand” does this; it continues to total all rows, including hidden rows. The same thing happens with the COUNT and AVERAGE functions.
In contrast, the SUBTOTAL function has the ability to flexibly adjust itself. When using SUBTOTAL, if you filter the data, the total will automatically update to calculate only based on the currently visible rows (filtered rows).
=SUBTOTAL(function_num, range)
This function is not limited to just calculating sums. SUBTOTAL can convert between sum, average, count, min, max and many other useful calculations, just by changing the first parameter in the formula. Below is a full list of supported functions:
| Number of functions | Excel function |
|---|---|
| 101 | AVERAGE |
| 102 | COUNT |
| 103 | COUNTA |
| 104 | MAX |
| 105 | MIN |
| 106 | PRODUCT |
| 107 | STDEV |
| 108 | STDEVP |
| 109 | SUM |
| 110 | VAR |
| 111 | VARP |
Unlike SUM, the SUBTOTAL function is smart enough not to calculate itself repeatedly. If you have subtotal values for each category and then a final total at the bottom, SUBTOTAL will automatically ignore the other subtotal values. Meanwhile, the SUM function just adds it all together, causing the numbers to be inflated. If you ever see an unusually high total and wonder why, check for nested SUM functions. SUBTOTAL does not have this problem.
Unlike the SUM function, the SUBTOTAL function is smart enough not to calculate itself repeatedly. If you have subtotal values for each category and then a final total at the bottom, SUBTOTAL will automatically ignore the other subtotal values. Meanwhile, the SUM function just adds it all together, causing the numbers to be inflated. If you ever see an unusually high total and wonder why, check for nested SUM functions; SUBTOTAL does not have this problem.
How to use SUBTOTAL in Microsoft Excel
What makes SUBTOTAL the obvious choice is its flexibility—you have far more options without the added complexity of the SUM function. Let’s look at an example to see how SUBTOTAL makes things easier.
To sum cells, the formula will be as follows:
=SUBTOTAL(109, C2:C15)This formula sums cells C2 through C15 but ignores hidden cells. Apply this formula to the remaining two rows and now have the total values. They work well with entire data tables and remain effective even when filtering the table. Now the numbers are reasonable and accurate.
However, the other numbers are still unreasonable. The COUNT value still shows as 14, and averages are now calculated by dividing the subtotal by that number; this is why the average values are lower than they should be. No problem. The SUBTOTAL function also supports the COUNT and COUNTA functions.
So, for the cell containing the count value (B16), instead of writing:
=COUNTA(A2:A15)Please replace it with:
=SUBTOTAL(103, A2:A15)
Number 103 is the function code for COUNTA. Counts now automatically adjust as you filter data, and average values always display correctly. The SUBTOTAL function also supports both MAX and MIN – a really useful feature.
In total, SUBTOTAL supports 11 different functions; While it can’t replace every Excel formula, these 11 functions are all you need for most tables that aggregate data.
Post Comment