Home

About excel filter function

%3CLINGO-SUB%20id%3D%22lingo-sub-1189257%22%20slang%3D%22zh-CN%22%3EAbout%20excel%20filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189257%22%20slang%3D%22zh-CN%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThere%20is%20a%20big%20big%20cause%20lyme%20me%2C%20hope%20someone%20help!%3C%2FP%3E%3CP%3Ei%20want%20to%20filter%20my%20data%20in%20two%20step%2C%3C%2FP%3E%3CP%3Estep1%20%3A%3C%2FP%3E%3CP%3Efilter%20line%20'2019'%2C%20i%20get%20top%2010%20biggest%20value%2C%20it's%20ok%2C%20i%20did%20it%20.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22broog2045_2-1582448375946.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172815i0EC4DF5E6D0192A2%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22broog2045_2-1582448375946.png%22%20alt%3D%22broog2045_2-1582448375946.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Estep2%3A%3C%2FP%3E%3CP%3Efilter%20line%20'2020'%2C%20i%20want%20to%20get%20top%205%20value%20put%20end%20step%201%2C%20but%20finally%20there%20is%20not%20out%2C%20what's's%20wrong%20with%20my%20excel%20excel%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22broog2045_1-1582448320258.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172814iB3EF429829213F36%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22broog2045_1-1582448320258.png%22%20alt%3D%22broog2045_1-1582448320258.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1189257%22%20slang%3D%22zh-CN%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189274%22%20slang%3D%22en-US%22%3ERe%3A%20About%20excel%20filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189274%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565958%22%20target%3D%22_blank%22%3E%40broog2045%3C%2FA%3E%26nbsp%3BThat's%20because%20none%20of%20your%20values%20are%20both%20in%20the%20top%2010%20for%20column%20A%20%3CSTRONG%3E%3CU%3Eand%3C%2FU%3E%3C%2FSTRONG%3E%20in%20the%20top%205%20for%20B.%20Try%2C%20by%20typing%20100%20in%20B5%20and%20then%20apply%20your%20filters%20again.%20Only%20row%205%20will%20be%20displayed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189333%22%20slang%3D%22zh-CN%22%3ERe%3A%20About%20Excel%20filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189333%22%20slang%3D%22zh-CN%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%20thanks%20a%20lot%2C%20but%20i%20want%20filter%20%3CSPAN%3Ecolumn%20b%3C%2FSPAN%3E%20line%20a%20was%20filtered%2C%20so%20i%20need%20the%20be%20s%20be%20sings%20be%20displayed%20in%20column%20B%20%3CSPAN%3Ewas%20'0.8718%2C%200.1254%2C%200.3166%2C%201.1799%2C%200.0365'%20after%20filtertop%20top%205%20in%20column%20B%2C%20how%20to%20Eve%3F%20%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22WechatIMG87.jpeg%22%20style%3D%22width%3A%20938px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172820i446C7A721CDD4108%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22WechatIMG87.jpeg%22%20alt%3D%22WechatIMG87.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189364%22%20slang%3D%22en-US%22%3ERe%3A%20About%20excel%20filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189364%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565958%22%20target%3D%22_blank%22%3E%40broog2045%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20Excel%20with%20the%20latest%20FILTER%20function%2C%20the%20solution%20in%20the%20attached%20sheet%2C%20column%20E%2C%20might%20work%20for%20you.%20Otherwise%2C%20the%20one%20in%20column%20F.%3C%2FP%3E%3CP%3ESince%20you%20column%20A%20is%20already%20sorted%20you%20can%20simply%20filter%20the%20largest%205%20from%20range%20B2%3AB11.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

hi

there is a big problem puzzled me, hope someone help!

i want to filter my data in two step,

step1 :

filter line'2019',i get top 10 biggest value,it's ok,i did it .

broog2045_2-1582448375946.png

 

step2:

filter line'2020',i want to get top 5 value based step 1 ,but finally there is nothing output,what‘s wrong with my excel

broog2045_1-1582448320258.png

 

 

 

3 Replies
Highlighted

@broog2045 That's because none of your values are both in the top 10 for column A and in the top 5 for B. Try, by typing 100 in B5 and then apply your filters again. Only row 5 will be displayed.

Highlighted

@Riny_van_Eekelen thanks a lot, but i want filter column B based line A was filtered,so i need the result that be displayed in column B was '0.8718、0.1254、0.3166、1.1799、0.0365' after filtered top 5 in column B ,how to achieve ?WechatIMG87.jpeg

Highlighted

@broog2045 

If you have Excel with the latest FILTER function, the solution in the attached sheet, column E, might work for you. Otherwise, the one in column F.

Since you column A is already sorted you can simply filter the largest 5 from range B2:B11.

 

Related Conversations
Excel_CommandButtons_Hidden
Chrisovalantis_Mitelos in Excel on
0 Replies
Excel Ideas search field
Fenjar in Excel on
0 Replies
Trouble shoot: File formatting is lost when shared with others
W_005 in Excel on
0 Replies
Which formula to use
Ramon Haagen in Excel on
3 Replies