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?

autofiltertop10filtered09

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.

autofiltertop10filtered01

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.

autofiltertop10filtered02

Add a Top 10 Filter

Next, add a filter on the Qty column, to keep only the Top 10 of those records.

autofiltertop10filtered03

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.

autofiltertop10filtered04

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

autofiltertop10filtered05

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.

autofiltertop10filtered06

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.

autofiltertop10filtered07

Now, filter the Day column for Sunday, and the amount in cell G4 changes to zero.

autofiltertop10filtered08

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:

=AGGREGATE(9,3,C6)

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.

_______________________

You may also like...

1 Response

  1. August 7, 2014

    […] 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 […]

Leave a Reply

Your email address will not be published. Required fields are marked *