A few days ago I was working on an Excel spreadsheet and was looking for a value in the filter list. To my surprise I wasn’t able to find the entry which I knew must be there in the sheet. After locating the entry to prove I wasn’t dreaming, my usual curiosity kicked in.
A quick search revealed that it was a fairly well documented limitation. Now I would like to demonstrate a way to reproduce the scenario.
This can be achieved quite efficiently using a few short cuts.
a) Select cell A2.
b) Press Ctrl + Shift + Down Arrow (to select the entire range)
c) Type 1.
d) Press Ctrl + Enter (to fill the entire range)
2. Replace the ones with sequential numbers.
a) Type the number 2 in cell A3. This establishes the pattern of a sequence.
b) Select range A2:A3.
c) Double click the little black square at the bottom left corner of the selection box (to fill the entire range with sequential numbers)
4. Confirm the filter limit.
a) Click on the down arrow to open the filter window.
b) Scroll down to the bottom.
You should only see 10000 down the bottom.
For more info, please refer to the following links:
Not all list items are displayed in the AutoFilter list in Excel