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 Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
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.
- 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 the Excel advanced filter?
So, here are the steps to apply the 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 the 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.
Excel Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
So the best approach is to apply the subtotal function
1. Just select the cell below where your filtered data ends.
2. Then Press Alt + = to apply a subtotal formula.
3. It will show you a subtotal excel formula.
4. 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 dropdown. 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.
you use Subtotal excel function for something else, please add a comment below. Maybe, we all can learn something from your practical use cases.
15 Pivot Tables Tricks for Pros
15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.
Most Popular Tricks are #3, #7 & #12
Hey,
very helpful blog !!
Is there any alternative of subtotal in Excel VBA?