What is limit of the number of entries in Excel drop-down lists?

Question

Hi,

I have Office 365 subscription so I am using the latest MS Office software. I have an Excel spreadsheet and I created a drop-down list but it's not working correctly.

1. I have 100,000 names listed in the drop-down list column. So there are 100,000 possible choices but when I try to select a name from the list in the column with the list selector, I can only scroll down to about the first 32,000 names. The rest of the list is not visible and I can't scroll anymore to see more entries. Is this problem caused by a 32,000 name maximum in Excel? Or is there a better way to create a list that you can select from in other cells so that you can have hundreds of thousands of potential choices?
2. The other problem I have with this Excel spreadsheet is that I can only assign this drop-down list to one column in one sheet. I tried to create a second column in another sheet of the same Excel workbook but it's not working. So my question is: Is there a one column limit to the drop-down lists? Or is this problem also caused by the 100,000 list of names being beyond the maximum for Excel?

Thanks for any advice.

Answer 1

There is a limit.  See info below from Debra Dalgleish and the link to the sample file.

Item Limit in Drop Down List

There are limits to the number of items that will show in a data validation drop down list:

• The list can show up to show 32,767 items from a list on the worksheet.
• If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.

If you need more items than that, you could create a dependent drop down list, broken down by category. There is a sample file here: Dependent Drop Down from Sorted List

Gord