Forum Discussion
Excel 2013 Filter problem
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
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.
2 Replies
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.
