The SUBTOTAL function returns the subtotal from a list or database in Excel. SUBTOTAL will give you instant answers for your filtered data
The syntax for SUBTOTAL is different to other Excel functions that you may have come across. A function number is needed to tell Excel which calculation you want to perform with the SUBTOTAL function
The function number is required and can be chosen from 1-11 or 101-111. 1-11 will include manually hidden rows whereas 101-111 will exclude manually hidden rows from the calculation.
The below table from the Microsoft website details what each function does:
Source: Microsoft Website
SUBTOTAL in Action
In the accompanying workbook to this article, you will find 1,000 rows of sales data for a full year. The list includes customers and products. Scroll to the bottom of the data (ctrl+shift+down arrow) where there are two rows totaling the data, one using SUBTOTAL and one using SUM.
Currently, the figures are the same, this is because you haven’t applied any filters to the data. The formula for the SUBTOTAL row is:
Function 9 is ‘SUM’ and D3:D1002 is the range to SUM
Click on the drop-down arrow on the ‘Customer’ field and select Alan Greene (you may need to tick ‘select all’ first to un-select all the records).
Scroll to the bottom of the file and observe how the SUBTOTAL formula has only totaled the displayed rows. The SUM function has remained the same and doesn’t really provide us with any meaningful analysis of the data.
Why is This Useful?
Using subtotal can help speed up how you handle and analyse your data. Using SUM for a calculation such as this will not display to correct sales totals for Alan Greene and cause you to spend more time calculating what the sales are for the displayed data.
With your data nicely laid out in a tabular format the best way to analyse it would be with a pivot table. An article series on pivot tables will follow soon.