Top Ten Values in Filtered Rows
If I apply an AutoFilter to see the Top 10 Sunday sales in a list, why does Excel just show me the Top 2?
In the screen shot below, you can see the first few rows of the table. The values are sorted highest to lowest, and the Sunday values are colored, so they're easy to spot.
In the top 10 rows, there are 2 Sunday rows, and there are another 22 Sundays in the remaining rows. The rows are numbered in column D, to show each value’s overall position.
Filter By Day
To see only the Sunday records, I’ll filter the Day column. At the bottom of the screen, you can see that 24 of the records are for Sundays.
Add a Top 10 Filter
Next, add a filter on the Qty column, to keep only the Top 10 of those records.
Uh-oh! Instead of keeping the highest 10 Sundays, there are only 2 records left after the filter. It only kept the Sundays that are in the overall Top 10.
Add a New Column
I don’t want just the Sundays that are in the overall Top 10 – I want the Top 10 Sundays.
So, the built-in filter doesn’t do what I need, but I can add another column, to do the job.
- First, clear all the filters, so all the rows are visible.
- Then, add another column to the table, with a SUBTOTAL formula. Here’s the formula in cell E6:
- = - - SUBTOTAL(9,C6)
- NOTE: There are 2 minus signs after the equal sign. I included spaces, so they’re a little easier to see. The formula will work with or without the spaces.
- Copy the formula down to the last row of data
Apply the Filters
Now that the new column has been created, try the filter again.
- First, apply the Sunday filter in the Day column, to see the 24 Sunday records.
- Next, instead of filtering the Qty column, apply the Top 10 filter on the Test column.
Success! All 10 of the top Sunday results are shown in the filtered results.
How It Works
To see how this works, I’ll remove the filters from the list, and add a link to cell E6, where the SUBTOTAL formula with highest quantity is stored.
Row 6 is visible, and cell G4 shows the top amount of $1000.
Now, filter the Day column for Sunday, and the amount in cell G4 changes to zero.
The SUBTOTAL function only calculates a sum for visible rows, and Row 6 is no longer visible, so the current result is zero.
Only the Sunday rows will have a TEST value that is above zero, so when the Top 10 filter is applied on the TEST column, the Sunday rows have the highest values.
Of those Sunday values, only the Top 10 are visible, after the filter is applies.
Use the AGGREGATE Function
If you’re using Excel 2010 or later, you can use an AGGREGATE formula, instead of SUBTOTAL. The functions are similar, but AGGREGATE has many more options than there are in SUBTOTAL.
Here is the formula that you could use in cell E6:
You can read more about the AGGREGATE function here.
Download the Sample File
To see the Top 10 filters with SUBTOTAL and AGGREGATE, you can download the sample file from my website. On the Sample Files page, go to the Functions section, and look for FN0033 – Show Top 10 For Filtered Items.
The zipped file is in xlsx format, and does not contain macros.