Make a Cell Drop Down List in Excel for Easy Data Entry

Make a Cell Drop Down List in Excel for Easy Data Entry



in this workbook we have a list where we're going to assign staff members to each week and 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 and 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 so 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 so 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 in this defined names group I'll click define name and I'm going to call this e MP list the short version of employee list the scope will be the workbook I'm going 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 and for the refers to I want it to refer to this table so I'm going to click an equal sign and click this button to go to the worksheet and 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 so this is Table one and the employee's column click the button to go back into the dialog box so 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 so I'm going back to the other sheet and these are the cells where we want the drop-down list so 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 and in this source box I'm going to type an equal sign and on your keyboard press the f3 key and that opens up a list with names so 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 any one 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 wwlp.com

32 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *