Excel Advanced filter technique using the Subtotal excel function

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

subtotal-excel-function-how-to-use

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:

  1. Firstly, Apply a filter to your data set.
    1. By selecting the entire data set pressing CTRL+ SHIFT+ Right Arrow and then CTRL+ SHIFT+ Down Arrow.
    2. If some extra rows arrows are selected, you can press CTRL+ SHIFT+ Up Arrow to adjust the same.
  2. 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.

Go-Data-Filter-Excel-Advanced-Filter

How to apply Excel advanced filter?

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

Apply-Excel-Advanced-filter

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.

Top-10-Autofilter-Excel-Advanced-filter

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:

  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.

Subtotal-at-end-data-Excel-Advanced-filter

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.

Select-Function-in-Subtotal-advanced-filter

Subtotal-Excel-Advanced-filter

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.

Subtotal-Formula-Manually-in-Advaned-filter

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.

Shortcut-subtotal-Excel-advanced-filter

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.Function-box-of-Subtotal-Advanced-filter you use Subtotal excel function for something else, please add a comment below. Maybe, we all can learn something from your practical use cases.

1 Response

Leave a Reply

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.