MS Excel: Subtotal Function and Filter

The Subtotal function provide the subtotal of the numbers in a column.  It allows us to perform a calculation in the worksheet, and the same calculation can also be performed on the subsets of the data by applying filters. The magic of this function is that it ignores values in rows hidden by a filter.

The syntax for the Subtotal function is:

        Subtotal(function_num, ref1, ref2, ...)

function_num is the type of subtotal that you'd like to create. Image below explains 11 types which we can select:

ref1, ref2, ... are the ranges of cells that you want to subtotal.

Please go through the example with images below, it will help a lot in understanding Subtotal function.

Here I want to know the average monthly salary for each city. Normal AVERAGE function won't be helpful here because once we'll apply filter to it will still give us the average of all the records in the data. 

Now as per the requirement if I want to know the average monthly salary payable in Augusta region, the SUBTOTAL function will provide me the desired result while AVERAGE function will still give me the average of complete data.

I believe, I've not confused you and this post will be helpful to you while at work.  Let me know your feedback or suggestion.  I SHARE COZ I CARE! "Happy Learning"

Download SAMPLE FILE

Filter Asterisk Character ("*")

Hello friends I hope you are keeping track of our Excel tips and learning new things everyday. Now it's time to get ready for another Excel tips. Today's post will be short and but very unique. Today we are going to learn, how to filter the asterisk character ("*"). Suppose you have a data which contains few asterisk character ("*") and you want to filter rows containing asterisk character ("*").

Now if you have been new to this situation you will simply say it's very easy. But mind it, It's not! There is a special technique to this and I'll take you step to step through it.

1. Apply filter to your complete data which contains asterisk ("*")

2. Click on filter drop down, Select Text Filters and than Contains...

3. It will open Custom AutoFilter pop-up box, here against the drop down with contains is another blank drop down. In this use Tilde ("~") before Asterisk ("*") and hit OK.

With this I wrap today's post, practise it and share it with everyone! "Happy Learning"