When you try to use the Top 10 filter, on a list that already has some filters applied, the results probably won’t be what you want. The Top 10 feature ignores the filters on other columns, and just returns values that are in the overall Top 10.
Recently, I showed a workaround for that problem in this blog post: Top Ten Values in Filtered Rows. In that example, I added a new column, and used the SUBTOTAL function to show the value, then filtered that new column. Hidden rows would have a value of zero, thanks to the SUBTOTAL function, so they wouldn’t be included in the ranking.
Top 5 for Advanced Filter Results
Last week, someone asked how to do something similar with an Advanced Filter – for products sold after a specific date, which orders had the top 5 amounts?
An Advance Filter is a little harder to use than an AutoFilter, but it has some advantages:
- You can filter in place, or send the results to a different location – even to a different sheet
- Instead of adding a new column, with extra formulas that might slow down the workbook’s calculation, you just need to add a few formulas at the top of the sheet
Unlike AutoFilter, the Advanced Filter doesn’t have a built-in Top 10 feature, but you can use the LARGE function in the criteria range, to find rows with the highest values. So, if we just wanted to find the top 5 orders overall, we would use this formula in the criteria range, if the first order amount is in cell D2:
The formula checks to see if each Total is greater than or equal to the fifth largest number in the list of orders.
Largest Amount After a Specific Date
In this filter, we don’t want to find the 5th largest amount overall – we want to find the fifth largest amount after a specific date. So, instead of a simple LARGE formula, we need a LARGE IF, similar to the MAX IF formula that we’ve created before.
To keep the criteria row visible, I moved the data down one row, so the headings are in row 2. Then, I set up some formulas, that will be used in the Advanced Filter criteria.
H2: The date to be used as the starting date for the filter results
I2: The number of top records to return
K2: A formula that calculates the number of records after the start date
=COUNTIF($A$3:$A$29,">=" & $H$2)
M2: LARGE IF formula calculates 5th largest Total for orders on or after the start date
- The LARGE function returns the fifth largest value in the range D3:D29, for dates on or after the start date.
- This formula is arrayed entered (Ctrl+Shift+Enter)
- If there are fewer records than the Top # requested, the lower number is used -- MIN($K$2,$I$2)
Create the Criteria Range
After the supporting formulas are built, you can create the criteria range for the Advanced Filter.
The criteria cell will contain a formula, so the heading cell is left blank.
F1: Criteria Heading -- Leave blank
F2: Criteria: =AND(A3>=$H$2,D3>=$M$2)
In cell F2, the criteria formula checks two things:
- Is the date in cell A3 greater than or equal to the starting date in cell H3?
- Is the amount in cell D3 greater than or equal to the minimum total that is calculated in cell M2 ?
Run the Advanced Filter
When you run the Advanced Filter, the result is a list of the 5 orders sold on or after February 3rd, with the 5 largest totals. I filtered the list in place, but you could send the results to a different location.
Download the Sample File
To see how this filter works, you can download the Advanced Filter sample file from my website. The file also has an example for finding the highest totals within a specific date range – between a specific start and end date.
Video: Filter Data to a Different Sheet
One of the many useful things that an Advanced Filter can do is copy data to a different sheet, without using a macro. You can see the steps in this video.
Or watch on YouTube: Filter Excel Data to a Different Sheet