Most of us are familiar with the simple Filter tool found in the Sort & Filter group on the Data tab. If you apply the filter and use the SUM function, it will total Both visible And hidden data.

But what if you want total Only the Visible Data, and not the hidden rows that are shown in the filtered data? This is where the SUBTOTAL function becomes useful! One of the easiest ways (and little known ways) to apply the SUBTOTAL function is to:

1. First Filter your dataset
2. Select the cell directly below the range you wish to total
3. Click on the AutoSum button

Rather than using the SUM function, Excel will under these circumstances automatically use the SUBTOTAL function and will provide you with a total of Only the Visible Data. How Cool is that!

Just be sure to filter your data first, and then hit AutoSum. It couldn’t be easier!