• Home  / 
  • Excel
  •  /  Subtotal Excel Formula – Excel Advanced Filter Trick

Subtotal Excel Formula – Excel Advanced Filter Trick

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 formula

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

Excel Advanced filter technique using the subtotal excel formula Raw data

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 from 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 dataset 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.

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

  1. Firstly, Apply a filter on 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.

After Selecting your data go to Data tab then go to Filter on your data setHow 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.

First apply filter in your data set then click on dropdown and select Numbers Filter

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

You can also see the Top 10 items will appear also you can choose desired numbers - 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 subtotal formula

  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 formulas.
  4. A lot of people go to AutoSum (Under Home Tab) and Choose Min.

Just select below cell where your filtered data ends then press shortcut key Alt = it will show subtotal of excel advanced filter data

You must be confused – Why is Alt + = showing Subtotal excel formula?

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.

Suggested video: 5 little-known Excel Shortcut keys

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 from your filtered data.

Our aim is find minimum from these 10 records now change the number subtotal 9 to Subtotal 5 - Advanced filter

Subtotal of Excel Advanced filter 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 subtotal formula

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

They write Subtotal excel formulas manually and the put the numbers 5 or 9 indicating what they are looking for but they make the selection manually. - Excel 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.

So the Safe option is to press Alt = Shortcut key to activate the 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 techniqueFunction box for subtotal excel Formulas is widely used advanced filter technique

If you use Subtotal excel formula for something else, please add a comment below. Maybe, we all can learn something from your practical use cases.

Learn online Excel course for free – Beginner must watch it

Share it and help your friends who work with large datasets at the office.

About the author

Rishabh Pugalia and Niladri Sekhar Biswas

Author CA. Rishabh Pugalia has 10+ yrs. of work experience in Audit, Interest Rates Trading & I-Banking & Author Niladri Sekhar Biswas has 9 years of experience working in Microsoft Excel, as Excel and VBA corporate trainer

  • Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword….wait there’s even more Now what if i told you there was a simple WordPress plugin that does all the On-Page SEO, and automatically for you? That’s right AUTOMATICALLY, just watch this 4minute video for more information at. Seo Plugin

  • Very good article, i think you can make yodalearning.com go viral easily using one tricky method.
    Just search in google:
    Skelap’s Method To Go Viral

  • Junior

    Hi there! This post could not be written any better! Reading
    through this post reminds me of my good old room mate!
    He always kept talking about this. I will forward this article to him.

    Pretty sure he will have a good read. Thanks for sharing!

  • I am genuinely thankful to the holder of this site who has shared this
    great paragraph at at this time.

    • Niladri Sekhar Biswas

      Thanks a lot.

  • maaz

    very nice yar, thank u

  • Stelios Tserkezis

    I think you dont have to select the list to apply the table feature. But even if you want to select the list ctrl+a is a better aproach. The shortcut to convert a list to table is ctrl+t. Beside this if you have a table you can choose a total row from table tools to apply the automatic subtotal formula easily, and a dropdown list to select the function of subtotal.

    • Niladri Sekhar Biswas

      Thanks Stelios. You can do also the same.

  • Anonymous

    what is of IFERROR function in excel give instruction through video

    • Niladri Sekhar Biswas

      We will discuss this function (IFERROR) me very soon.

    • Niladri Sekhar Biswas

      Thanks a lot. We will cover that part also.

  • Debarchan

    The way the process is explained is really good. Keep on adding these kind of tutorials and help was educate ourselves on Excel – the super tool.

    Thank You

    • Niladri Sekhar Biswas

      Thanks a lot.

  • Partha Giri

    Excellent Sir. It’s really helpful share. Thank you.

    • Niladri Sekhar Biswas

      Thanks Partha

  • Sanchita Hore

    Its wonderful and really very helpful.

    • Niladri Sekhar Biswas

      Thanks a lot.

  • Smita

    The formula was very self explanatory

    • Niladri Sekhar Biswas

      Thanks Smita

  • Sahana Dutta

    So well explained. WTG!

    • Niladri Sekhar Biswas

      Thanks Sahana

  • Rajat Subhra Sen

    This is great! Must read for all who deals with vast quantities of data. Niladri, you continue to thrill again.

    • Niladri Sekhar Biswas

      Thanks Rajat

  • Amy

    Helpful! Expecting to see more such useful blogs from you for the amateur like us ….. in future 🙂

    • Niladri Sekhar Biswas

      Thanks. Please go through this blog. We are trying to cover all the advance features of Excel.

    • Niladri Sekhar Biswas

      Thanks

  • Somidh Das

    Good one! Keep going…

    • Niladri Sekhar Biswas

      Thanks Somidh

  • Somoshree

    Very helpful tutorial. We need more knowledge on Excel from you.

    • Niladri Sekhar Biswas

      Thanks Somoshree.

  • Sayed Riyaz ALi

    Excellent job you do Niladri Sir. I just hope I’ll more to catch excel knowledge from you. Thank You Niladri Sir………

  • Excellent !!

  • Dipanjan Ghosh

    Keep up the good work Niladri, we look forward for more similar and easy articles from you in the near future.

  • Hi Niladri Da,

    Great tutorial.

    Wish you all the best for your blogging journey.

    Thanks,
    Lahaul

  • Moumit Mondal

    Assume sir … That’s a good article in excel …..