Create a Cell Drop-Down List in Excel with Data Validation

by Gregory on February 7, 2011

Adding a drop-down list to a cell or range using Data Validation is a simple matter. Data Validation is used to define restrictions on what data can or should be entered into a cell. Here we’ll use a List to restrict what values can be entered into a cell.

Create a Drop-Down List for a Cell or Range

Select the cell or range you want to use for a drop-down list, then

  • Choose Data Validation from the Data Tools group on the Data tab
  • Select the Settings tab
  • In the Allow box, select List
  • Click the Source box
  • Type in a list of values separated by a comma
  • Make sure the In-cell dropdown box is checked
  • Click OK

Data Validation dialog box Settings

The list I created was for cell A1, which is shown below.

Cell Drop-Down Manual List

List Data Sources

Manually entering the source data for the list is probably the least desirable method. A better way is to put the list in a range, then refer to the range.

Data Validation list source range

The same list data was put into the range J1:J6, then I changed the source reference to these cells. This is a better method than manually entering the values, but older versions of Excel require the list to be on the same worksheet. One way around this, and a better solution, is to give the List range a Name.

Data Validation list source Named rangeYou can give the List range a Name then use it for the Source. For example, I selected the range J1:J6 then typed TheNames into the Name box, thereby creating a Named Range. On the Data Validation dialog box I typed in =TheNames into the Source box.

Now let’s assume that we have to add a couple more names to the list. Instead of changing every cell that references this Data Validation list, we just change the reference to the Named Range. (Choose Formulas > Name Manager, select the Named Range, change the reference in the Refers to box, then click the green arrow to make the change and click Close.)

But if the list will grow over time, changing the reference should be done automatically with a dynamic Named Range formula. We’ll do this by using the OFFSET formula.

  • Choose Formulas > Name Manager
  • Select New
  • Type a Name in the Name box (I’ll use myNamesList)
  • In the Refers to box type =OFFSET(Sheet1!$J$1,0,0,COUNTA(Sheet1!$J:$J),1)
  • Click OK

Now select the cell or range with Data Validation and,

  • Choose Data > Data Validation
  • Select the Settings tab
  • In the Source box type in =myNamesList (or the Name you created)
  • Click OK

This Named Range formula is dynamic, which means the source list will expand when names are added to the list. If the list contains more than 8 values the drop-down list will have a scroll bar.

Data Validation list source range expanded

Hopefully you can now create a drop-down list that will meet your needs.

Related Posts Plugin for WordPress, Blogger...
kapil February 8, 2011 at 9:43 pm

Hi Sir,
Data validation description is very good. Now I am learning how to make it as dynamically Using offset function along with countA .

Thanks

Gregory February 8, 2011 at 11:57 pm

Your words are encouraging to me and I appreciate them very much.

Adam August 15, 2011 at 11:31 am

The instructions to set up a drop down menu on excel were clear, easy to follow and helpful. Thank you.

CabbageBnB January 22, 2012 at 4:20 pm

Problem–How to display a default selection from DDLs?

Great tool Gregory… Thanks.
Great posts for very basic Excel user.

Problem defined–
Using your Drop Down List for Suites, Discounts, Specials, and other options in B&B booking system database (Generally selecting various costs via LOOKUP)
–On selection of new booking record–
How to display a default selection from DDLs?
i.e. Discounts=”None”, Special Packages=”None”

Tools–
iMac-3.06 GHz Intel Core i3,
OS X 10.6.8 Snow Leopard plan to upgrade to OS X 10.7x Lion
Working–
Upgrading small (<40 bookings/year) B&B booking database from Appleworks 6.0 to Excel for Mac 2011, version 14.1.4.

Gregory January 22, 2012 at 5:42 pm

If your looking for a drop down list (DDLs) that is dependent upon a particular selection in another drop-down list, the you should check out my article on Dynamic Dependent Drop Down Lists. This should give you the general method and data structure to created drop-down list that are dependent.

CabbageBnB January 22, 2012 at 6:31 pm

Thanks Gregory…
Been looking at that technique also.

Main question is…
How does one set a ‘default’ DDL selection when using Excel B&B booking database system to create a new Booking record?

Gregory January 22, 2012 at 6:43 pm

It sounds like you need to import the default data list from your database, by using the Data import tools in Excel 2011. The problem you’ve been describing is only giving me a fuzzy idea of what needs to be done.

Is the default selection a single value or a list you pick from? And were is the data coming from to populate the default value or list: Excel or Appleworks?

Larry Anderson July 1, 2012 at 10:21 pm

I see all kinds of dependent drop down lists where you select an item and then go to the next column and select another item. In my case I would like to create a dependent drop down list where you select a Hallway (example: Hallway 1) in say cell A1, after selecting Hallway 1, I would then click on cell a1 drop down list again and have it show me a list of all the Room Numbers in Hallway 1 being the final result. So, Click on drop down arrow, Click on Hallway 1, Click on drop down arrow, Click on Room # 125. Cell A1 should now show 125. I am doing this for a High School for Substitute Teachers. Any Help would be greatly appreciated. Thanks Larry

Gregory July 3, 2012 at 6:08 pm

At first I dismissed this as not possible, but the more I thought about it the more likely it’s solution seemed within reach. It will require some VBA code, but I believe it can be done. Please let me know what version of Excel you are using. Thanks for the challenge, Larry.

Larry Anderson July 3, 2012 at 8:08 pm

Hi Gregory,

I did find a spreadsheet that will do exactly what I want without VBA (I think). I am having trouble trying to figure out the formulation in the spreadsheet. In this spreadsheet you select the A-Names and when that comes up, you click on the drop down arrow again and then a list of Names beginning with A appears and you select the name. This is the name that will appear in the same drop down list after clicking on the drop down list twice. HOWEVER, when I try to modify this sheet to show Hallways and Room #s I run in error problems. (For some reason which I do not know it does not like numbers???). I tried to stick a letter in front of the number and it works. But, the room #s do not have letters in front of them. I even tried to make the letter white but when it allows you to select it the letter appears in front of the room # which will not be acceptable.
I would be most willing to send you this small spreadsheet example for you to look at if you wish.

Thanks

Larry

Larry Anderson July 3, 2012 at 8:09 pm

BTW – I am using Excel 2010

Werner Lechner December 24, 2012 at 3:57 pm

Just to add, for German Sys Prefs. you have to use ; instead of ,

Liz Taylor February 11, 2013 at 9:16 am

Before I was upgraded to Excel 2010 I typed numbers in my drop down box because it’s faster than stopping and looking down the list. Now if I do that I get a message telling me “The value you entered is not valid. A user has restricted values that can be entered into this cell.” The sheet isn’t protected. Can anyone help?

Gregory February 14, 2013 at 8:29 pm

Not sure exactly, but is sounds like some data validation was invoked when you upgraded. I know there are some serious problems with upgrading from Excel 2003 to Excel 2010. Sorry I couldn’t be of more help.

Graham Crooks May 4, 2013 at 6:27 am

I’ve a lot of upgrading from Excel 2003 to Excel 2007/2010 to do, so this was a great find – thanks. One question…
I’ve sorted my lookup list; let’s say it’s colours in a LUPallette list: Red, Green, Blue, Purple.
I’ve applied that to the right cell in the row in my table – all good.
However, folks can type in directly, so they may enter “red” not “Red” – that presents some presentational differences which are a bit fussy I know but annoying none-the-less.
Is there a way to force the entry to appear as Capitalized without the user having to hit the shift key?

Gregory May 4, 2013 at 6:38 am

I don’t know any method to generate proper case unless you use VBA, which gets a bit involved.

Comments on this entry are closed.

Previous post:

Next post: