Subtotal function in Excel Formulas Helps to Stay Organized
Large Excel spreadsheets are very vulnerable to mistakes in formulas. To reduce the probability of errors, good organization and structuring of data is a must.
Subtotal() function is one of the features that can help you to organize the spreadsheets better and prevent messy calculation logics. Novice Excel users often use SUM() function in places where SUBTOTAL() would be more appropriate. For large tables this practice can produce unmanagable files and hard to find calculation errors.
SUBTOTAL is meant to calculate subtotals or summary rows in the middle of the data table. The important distinction between Subtotal and Sum is that Subtotal does not sum cells which contain another use of Subtotal. This prevents multiple sums.
| Bad practice | Better solution | |
 |
*Subtotals of categories are calculated with SUBTOTAL as well |
Using Subtotal instead of Sum() in formulas has the advantage, if you need to calculate over large number of subtotals and the table is long.
Be careful – if somebody else modifies the table and accidentally adds a row with SUM() function into the range that you sum with subtotal, then the result will be wrong.
