How to Make a Cell Drop Down List in Excel 2010

• Report rights infringement
• published: 03 Aug 2012
• views: 494196

http://www.contextures.com/xlDataVal01.html Visit this link for sample workbooks and written instructions. To make data entry easier, create a drop down list in an Excel cell. First, create a list of items, then make a named Excel table. Then, use Excel data validation to make the drop down list in worksheet cells '---------------------- Transcript for How to Make a Cell Drop Down List in Excel 2010 In this workbook, we have a list where we're going to assign staff members to each week. I'd like a drop down list in this cell so it's easy to select a name. On another sheet in the file, I've created a list of names with the title Employees at the top. The first step will be to create a Named Table, and to do that, I'll go to the ribbon, and click the Insert tab, then click Table. Here's the Create Table window, and it's already selected the range where my list is. I'm going to say yes there are headers because this cell is a header, so check that box and click OK. We now have a table and if I click somewhere in that table, I can see its name up here in the Table Name box. I'm going to use these employee names in my drop down list. The next step will be to give an Excel name to the range of cells where the employee names are. I'm going up to the ribbon, and on the Formula tab and in this Defined Names group, I'll click Define Name. I'm going to call this EmpList, the short version of Employee List. The scope will be the workbook, I want to be able to use this name anywhere in the workbook. I won't add a comment, and right now it's just showing the cell I have selected on the worksheet. I'm going to delete that. For the Refers To, I want it to refer to this table. I'm going to click an =, and click this button to go to the worksheet. If I move my pointer just to the top of the column, and click there, it picks up that table and shows the name of the column. This is Table 1 and the Employees column. Click the button to go back into the Dialog box. This name is going to be equal to whatever is in that column and as the column grows or shrinks, it's going to keep track of it for us. Click OK and we now have a name in our workbook that we can use in a drop down list. I'm going back to the other sheet. These are the cells where we want the drop down list. I'll select all of those cells, and on the ribbon, go to the Data tab, and click Data Validation. Instead of just any value, we want to allow a list. In this source box, I'm going to type an =. On your keyboard, press the F3 key, and that opens up a list with names. There's the name I created. I'll select that and click OK, and it puts it in for us. When I click OK now, I've got data validation. So each cell that I click on, you'll see the drop down arrow. I can select anyone of the names, go down, and assign an employee to each week without trying to remember all the names. It creates that list so the data entry is easy for us. To download the sample file for this video, or to see more Excel tips and tutorials, please visit my website at www.contextures.com..