Excel 2013 Filter problem

[Dinukfonseka]

Hi all,

I am facing some problems with Excel filters. I have some data set with text values and currency values. First I need to filter the "Item Types" column to get only " Office Supplies" items. Then I need to apply a filter to the "Total profit" column to find the Top 5 values. But it will not be showing any values.

I think it suppose to filter only the top 5 values under Office Supplies item type.

Is it not the way the filters are working? I have attached the file.

Thank you

[#2 · Accepted solution]

@Dinukfonseka 

AutoFilter applies each filter separately. So the top 5 filter on the Total Profit column selects the 5 highest profits from ALL rows, not from rows filtered in other columns.

To do what you want, add a helper column:

First, remove the existing filter.

In H1, enter the text Helper (or Dummy, or whatever).

In H2, enter the formula

=SUBTOTAL(9,G2)

and fill down.

Now turn on the filter again.

Filter column A for Office Supplies, then filter the new column H for Top 5.

Based on: Top Ten Values in Filtered Rows 

See the attached version.

[#3]

@Hans Vogelaar  

Thank You very much for the solution. It is working

Thank you