SOLVED

Excel 2013 Filter problem

%3CLINGO-SUB%20id%3D%22lingo-sub-1870825%22%20slang%3D%22en-US%22%3EExcel%202013%20Filter%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1870825%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20am%20facing%20some%20problems%20with%20Excel%20filters.%20I%20have%20some%20data%20set%20with%20text%20values%20and%20currency%20values.%20First%20I%20need%20to%20filter%20the%20%22Item%20Types%22%20column%20to%20get%20only%20%22%20Office%20Supplies%22%20items.%20Then%20I%20need%20to%20apply%20a%20filter%20to%20the%20%22Total%20profit%22%20column%20to%20find%20the%20Top%205%20values.%20But%20it%20will%20not%20be%20showing%20any%20values.%3C%2FP%3E%3CP%3EI%20think%20it%20suppose%20to%20filter%20only%20the%20top%205%20values%20under%20Office%20Supplies%20item%20type.%3C%2FP%3E%3CP%3EIs%20it%20not%20the%20way%20the%20filters%20are%20working%3F%20I%20have%20attached%20the%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(66).png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232533i150DF21951146E84%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%20(66).png%22%20alt%3D%22Screenshot%20(66).png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(67).png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232534i3C258398FF5C0BEA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%20(67).png%22%20alt%3D%22Screenshot%20(67).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1870825%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1871012%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202013%20Filter%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1871012%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F621125%22%20target%3D%22_blank%22%3E%40Dinukfonseka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAutoFilter%20applies%20each%20filter%20separately.%20So%20the%20top%205%20filter%20on%20the%20Total%20Profit%20column%20selects%20the%205%20highest%20profits%20from%20ALL%20rows%2C%20not%20from%20rows%20filtered%20in%20other%20columns.%3C%2FP%3E%0A%3CP%3ETo%20do%20what%20you%20want%2C%20add%20a%20helper%20column%3A%3C%2FP%3E%0A%3CP%3EFirst%2C%20remove%20the%20existing%20filter.%3C%2FP%3E%0A%3CP%3EIn%20H1%2C%20enter%20the%20text%20Helper%20(or%20Dummy%2C%20or%20whatever).%3C%2FP%3E%0A%3CP%3EIn%20H2%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUBTOTAL(9%2CG2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20fill%20down.%3C%2FP%3E%0A%3CP%3ENow%20turn%20on%20the%20filter%20again.%3C%2FP%3E%0A%3CP%3EFilter%20column%20A%20for%20Office%20Supplies%2C%20then%20filter%20the%20new%20column%20H%20for%20Top%205.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBased%20on%3A%20%3CA%20href%3D%22https%3A%2F%2Fcontexturesblog.com%2Farchives%2F2014%2F03%2F11%2Ftop-ten-values-in-filtered-rows%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3ETop%20Ten%20Values%20in%20Filtered%20Rows%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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.

 

Screenshot (66).pngScreenshot (67).png

 

Thank you

2 Replies
Highlighted
Best Response confirmed by Dinukfonseka (New Contributor)
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.

 

 

 

Highlighted

@Hans Vogelaar  

Thank You very much for the solution. It is working

 

Thank you