Wednesday, April 27, 2011
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!