Maximum number of rows an auto filter can handle

%3CLINGO-SUB%20id%3D%22lingo-sub-1422848%22%20slang%3D%22en-US%22%3EMaximum%20number%20of%20rows%20an%20auto%20filter%20can%20handle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1422848%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20using%20Excel%20for%20many%20years%20and%20I've%20come%20across%20this%20issue%20but%20never%20asked%20anyone%20about%20it.%26nbsp%3B%20I%20use%20the%20filter%20often.%26nbsp%3B%20I%20have%20noticed%20that%20if%20my%20worksheet%20has%20many%20rows%2C%20the%20filter%20doesn't%20filter%20the%20whole%20worksheet%2C%20i.e.%20doesn't%20filter%20all%20the%20way%20down%20to%20the%20last%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20the%20maximum%20number%20of%20rows%20the%20filter%20can%20handle%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOther%20than%20using%20the%20Advanced%20Filter%2C%20which%20I've%20never%20learned%2C%20is%20there%20anything%20I%20can%20do%20to%20get%20the%20filter%20to%20filter%20the%20whole%20worksheet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1422848%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424809%22%20slang%3D%22en-US%22%3ERe%3A%20Maximum%20number%20of%20rows%20an%20auto%20filter%20can%20handle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424809%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F680631%22%20target%3D%22_blank%22%3E%40spbocgirl%3C%2FA%3E%26nbsp%3B%2C%20i%20hope%20you%20are%20selecting%20the%20complete%20table%20and%20then%20applying%20the%20auto%20filter.%20If%20you%20select%20just%20the%20top%20row%2C%20then%20Excel%20may%20not%20apply%20auto%20filter%20on%20complete%20table.%26nbsp%3B%3C%2FP%3E%3CP%3E(Easy%20method%20of%20selecting%20the%20complete%20table%20is%20%3A%20select%20top-left%20cell%20and%20press%20ctrl%2Bshift%2Bend)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425361%22%20slang%3D%22en-US%22%3ERe%3A%20Maximum%20number%20of%20rows%20an%20auto%20filter%20can%20handle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425361%22%20slang%3D%22en-US%22%3E%3CP%3E%2F%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636579%22%20target%3D%22_blank%22%3E%40amit_bhola%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BThank%20you.%26nbsp%3B%20I%20didn't%20know%20this%20would%20be%20necessary%2C%20since%20normally%20all%20you%20have%20to%20do%20go%20up%20to%20the%26nbsp%3B%3CEM%3Ehome%3C%2FEM%3E%20cell%20(CNTL-Home)%20and%20have%20it%20automatically%20filter%20the%20spreadsheet%20based%20on%20your%20column%20criteria.%26nbsp%3B%20Why%20doesn't%20go%20all%20the%20way%20to%20the%20bottom%3F%20Why%20this%20extra%20step%3F%26nbsp%3B%20(Questions%20for%20MS%2C%20I%20guess.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20your%20answer%2C%20though.%26nbsp%3B%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425376%22%20slang%3D%22en-US%22%3ERe%3A%20Maximum%20number%20of%20rows%20an%20auto%20filter%20can%20handle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425376%22%20slang%3D%22en-US%22%3EI%20don't%20have%20a%20complete%20or%20definitive%20answer%20but%20it%20can%20'stop'%20recognition%20if%20there%20are%20blank%20cells%20or%20if%20rows%20get%20cleared%20vs%20deleted.%20I'm%20sure%20there%20are%20other%20reasons%20too%20and%20probably%20a%20more%20detailed%20list%20of%20criteria.%20Best%20to%20select%20the%20entire%20table%20and%20format%20as%20table%20so%20the%20entire%20range%20is%20recognized%20as%20a%20table%20and%20then%20table%20filters%20should%20work.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425549%22%20slang%3D%22en-US%22%3ERe%3A%20Maximum%20number%20of%20rows%20an%20auto%20filter%20can%20handle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425549%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BYes%2C%20I%20discovered%20the%20filter%20can't%20hop%20over%20blank%20rows%20awhile%20back.%26nbsp%3B%20And%20it's%20never%20easy%20to%20answer%20the%20%22why%22%20questions...%20unless%20you%20are%20the%20one%20who%20designed%20the%20program.%26nbsp%3B%20Thank%20you.%26nbsp%3B%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425560%22%20slang%3D%22en-US%22%3ERe%3A%20Maximum%20number%20of%20rows%20an%20auto%20filter%20can%20handle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425560%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%26nbsp%3B%26nbsp%3BYes%2C%20I%20discovered%20a%20while%20back%20that%20the%20filter%20can't%20hop%20over%20blank%20rows.%26nbsp%3B%20And%20it's%20never%20easy%20to%20answer%20the%20%22why%22%20questions...%20unless%20you%20are%20the%20one%20who%20designed%20the%20program.%26nbsp%3B%20Thanks%20for%20replying.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I've been using Excel for many years and I've come across this issue but never asked anyone about it.  I use the filter often.  I have noticed that if my worksheet has many rows, the filter doesn't filter the whole worksheet, i.e. doesn't filter all the way down to the last row. 

 

What is the maximum number of rows the filter can handle?

 

Other than using the Advanced Filter, which I've never learned, is there anything I can do to get the filter to filter the whole worksheet?

4 Replies
Highlighted

@spbocgirl , i hope you are selecting the complete table and then applying the auto filter. If you select just the top row, then Excel may not apply auto filter on complete table. 

(Easy method of selecting the complete table is : select top-left cell and press ctrl+shift+end)

Highlighted

/@amit_bhola   Thank you.  I didn't know this would be necessary, since normally all you have to do go up to the home cell (CNTL-Home) and have it automatically filter the spreadsheet based on your column criteria.  Why doesn't go all the way to the bottom? Why this extra step?  (Questions for MS, I guess.)

 

I appreciate your answer, though.  Thanks.

I don't have a complete or definitive answer but it can 'stop' recognition if there are blank cells or if rows get cleared vs deleted. I'm sure there are other reasons too and probably a more detailed list of criteria. Best to select the entire table and format as table so the entire range is recognized as a table and then table filters should work.
Highlighted

@mtarler   Yes, I discovered a while back that the filter can't hop over blank rows.  And it's never easy to answer the "why" questions... unless you are the one who designed the program.  Thanks for replying.