**Subtotal Excel formula** of excel advanced filter is a definite hit, both for beginners and experienced users who haven’t seen this feature in action before. If you work with large data set and often arrange your data for analysis, It’s practically impossible for someone to make a conclusion by sampling looking at this massive data. I’m not going to teach you how to use filter (if you’re not well versed with filter in excel, check out this video – **Excel Filter Basics**)

## Excel Advanced filter technique using the **Subtotal excel function**

To begin with, let me showcase the Subtotal Excel function’s capability. Assume that you have business data consisting of around 5000 rows like below

Now if your manager comes to you and asks for a list of top 10 clients and the minimum amount of the Top 100 clients.

**What will you do to find top 100 clients in the database?**

You can either sort the data by ‘**Amount (in Rs).**’ column where the order will be largest to smallest. Then you will paste the First 10 rows in another sheet and then apply the **MIN Function** to get the minimum amount of the top 10 clients. But if tomorrow your criteria change to something else, then will you will have to do the same thing with another criterion? **Very tedious task.** **You may argue that **If you use MIN function for entire data set without copy-pasting the first 10 rows. My answer to this that the function will return the minimum value of the entire data set and not the top 10 values.

**Before you start doing subtotal function you have to apply the filter.**

**Here’s a right way to apply a filter in Excel:**

- Firstly, Apply a filter to your data set.
- By selecting the entire data set pressing
**CTRL+ SHIFT+ Right Arrow**and then**CTRL+ SHIFT+ Down Arrow**. - If some extra rows arrows are selected, you can press
**CTRL+ SHIFT+ Up Arrow**to adjust the same.

- By selecting the entire data set pressing
- After Selecting your data set
**Go to Data –> Filter**. Or you can use shortcut key**Alt + A + T**to apply a filter on your data set.

**How to apply Excel advanced filter?**

- First
**Apply the filter**in your data set, - Click on the drop-down button of
**Amt. Rs.**Column, - Go to
**number filters**and then - Click on
**top 10**.

As soon as you click on Top 10, another **new box will appear** and be looking for your confirmation. You can choose the desired number from the box and then click ok. Here is my example, it is 10.

By Clicking on OK you will get the list of top 10 clients. I recommend that you should check out other variants too. The excel advanced filter techniques are handy for many challenges you face at work Now you want to check which is the Minimum Number. Here you cannot use the Min function because MIN function will select the entire range including the hidden rows and will give the result from your dataset and not from the filtered data.

### So the best approach is to apply the subtotal function:

- Just select the cell below where your
**filtered data**ends. - Then Press
**Alt + =**to apply a subtotal formula. - It will show you a subtotal excel formula.
- A lot of people go to
**AutoSum**(Under Home Tab) and**Choose Min**.

**You must be confused – Why is Alt **+** = showing Subtotal excel function?** We use **Alt **+** =** shortcut key for the sum. Excel is smart. It knows that the data is filtered and you will be working with your Filtered data and that’s why it is showing subtotal excel formula.

Our aim is to find out the minimum numbers from these 10 records. Now change the number **subtotal 9** to **subtotal 5**, because *subtotal 9 is used for Sum* and *subtotal 5 is used for Minimum*. And then Press enter. You will get the minimum number of your filtered data.

Now it’s dynamic. You go to your amount filter drop down. And this time in case of 10 chooses only Top 5. You will get the** minimum number** from the Top 5 Clients. Similarly, you can use different numbers for a different function. You can use number subtotal 9 for Sum, number **subtotal 4 for Max** etc. You don’t have to remember the list. It will come up automatically but you need to remember the logic.

## Different uses of the subtotal function:

They write **Subtotal excel formulas manually** and then put the numbers 5 or 9 indicating what they are looking for but they make the selection manually.

If you look very closely at the selection, it starts from 11th Row (actual data Starts from 4th row) and ends in 239th row (actual data ends in 258th Row). It does not take the extra date which is hidden between 239th and 258th row. That means in future if you extend the list, let’s say 30 clients and keeping the subtotal excel formulas as it is, you will notice it does not include the value which is outside 239th row.

So safe option is to press **Alt** + **=** Shortcut key to activate the subtotal excel formulas.

Now if you look very closely at the function box for subtotal excel formulas it shows like above. This is widely used as Excel advanced filter technique.

Hey,

very helpful blog !!

Is there any alternative of subtotal in Excel VBA?