Discussion on:

How to add a dropdown list to an Excel cell

If you're using this tip, there are a few things to be aware of.

You don't need to setup the list of options on a worksheet, you can just type the items into the Source box on the Data Validation dialog. ie Enter Yes,No,Don't Know to get these options.

If you are using a list on a worksheet, but don't want to put it on the same worksheet as the validated cells, name the range your using (Insert/Name/Define) then use that name when you enter the Source. ie If you range is named "Choices", enter "=Choices".

The Validation dialog won't allow direct references to other sheets, but using the named range gets around the limitiation.

Hope these help.

The feature truly is very flexible. Thanks of the additional information. I had planned to follow up with an entry on how to work with lists not in the current sheet, but now I don't have to!

I have a spreadsheet I have to produce and update twice a week. I thought the first hint was really good, but with your information, I can take the cell and copy it from sheet to sheet! You just eliminated about 20 minutes a week of data entry for me.

Thanks for the note about naming the range of data on another sheet. It works.

Good one., !!

What about a drop down list of hyperlinks??

I'm sorry, but I don't know of anyway to maintain the link in a dropdown list. The control will display them, but they won't be live links.

I can do this/have done this. The drop down seems to stop at about 750 records. My list is longer (about 800) and properly refernced but the drop down willnot let me scroll to the real bottom? Ideas on how to get all the way?

This article is useful. But, i wanted to know that
'How to add additional data to existing dropdown list in excel'

For. Ex. here we have selected data from A1:A4, now i want to add 2 more values in A5 and A6.

Please let me know how this can be done to get the same list with 2 new values

I might not understand your question. If you add items to the sheet, you must recreate the list. There's probably a way to update the list via a macro, but unless you do it often, it's simpler just to recreate it.

Thanks a lot man!!! This helped me a lot.....Cheers !!

Thank you!

How do I make the drop down box visible even when it is not selected

Very Helpful...thanks

Is there a maximum list length. I cannot extend my cell list beyond 195 drop down entities

How to add color in dropdown list?

For example

cat is red

dog is blue

horse is green

and bird is on white

How do I remove a drop down list from a cell?

You can also just type in the criteria box (after you have selected the Validation option) just separate the option with a comma

How do i create dependant drop down lists. I have looked at using the Inderict() but i can not understand how to get it to work for me. I have a list of 46 catagories on one sheet with a key next to them. on another sheet i have all the sub catagories listed in two colums; one column listing a key to the main catagory with 5 descriptions listed in the second column for each catagory. From what I have tried the indirect does not work as it looks at subcatagories that are indiviual columns.

I just read that this was one of the Top Tutorials in 2008!

On my website, I have created 2 video lessons on this aspect of Data Validation. Here are the Links:
1) http://www.thecompanyrocks.com/excels/2008/08/3-ways-to-minimize-data-entry-errors-in-excel/

2) http://www.thecompanyrocks.com/excels/uncategorized/use-indirect-function-to-connect-2-pick-from-lists-in-excel/

I wish each of you a very happy new year in 2009
Danny Rocks

Many many thanks

You made an error. "1.Create the list in cells A1:A4. Similarly, you can enter the items in a single row, such as A1:D4." The range for your row example should be "A1:D1".

Thank you very much!!

If somebody wants to have a different list for different values in a given cell, this method could be extended further. The list will contain book names instead of animals and birds as in the screenshot.

I do not know if it was mentionned but you should sort the original list at some point. This way the items will appear in ascending order. Typing each item manually (separated by a comma) is hard to manage.

Definitely the best way to do this is by typing your list in a separate sheet then highlight it and type a name in the Name box (for example State).Finally sort the list in ascending order.

Once this is done in the Validation Source hit the F3 key to list all the range names and select the correct one.

If you need to add a new entry insert a cell within the original list (not at the end), type the new entry then sort it again. Everywhere where the list exist in the workbook will be automatically updated.

Cheers

I've been using drop downs in Excel for years, I didn't think it was such a secret. The irritating thing about data validation is that it does not support auto-fill (type-ahead). One would hope and expect that as you start to type in the cell it would jump to the most similar word on the list and offer that, as an alternative to clicking on the drop down arrow. Most times auto-fill would be much quicker, especially if you know most of the options on your list. Mine (Office XP) doesn't do that, so if anyone can tell me how, then that WOULD be a breakthrough.

Thank you very much. This is a time-saver for us !!

Great, very usefull. But:
How can the dropbox be widend so longer words can be fully displayed; and how can I enlarge the font?

Can you make the cell picking up the values keep the same formatting as in the original list, e.g if a word is underlined in the original list, can it keep that underlining in the list cell??

Can I have a concatenated drop down list consisting of 2 lists side by side. eg A/c code and its corresponding description. In addition if I selected from a list eg description, could I have the corresponding a/c code show? e.g Select Donation and A/c code 120 is displayed.

Is it possible to add dro down list in a worksheet and use that list to filter records in the worksheet.

This worked Great, to the point and I was able to Zoom through the process. It is great fro customers also. If choices do not change often, you can add as many as you need and change them quickly with out recreating the formula. Thank you.

Hello,

Thank you for this wonderful topic. I needed to know how to do this, but I also need to know if I can insert this into my website? I was told I could insert the drop-box feature, as html? Can anyone help me with this?

I would like to include a blank cell within the drop down list with the other selections offered. For example: black, white, red, (blank). Can anyone assist me on how to include a blank cell (empty cell).