How to create or insert drop down list in cells in Excel?
You can help yourself or others to work more efficiently in worksheets for data entry by using drop down lists. With drop down list, you can quickly pick an item from the list instead of typing the own value manually.
1. Firstly, you need to create a list with the data you will display in the drop-down list.
- Please select the whole list and press Ctrl + T keys, and then click OK in the Create Table dialog.
2. Select where you will place the drop-down lists.
3. Click Data > Data Validation. See screenshot:
4. In the popping up Data Validation dialog box, please to do as follows:
- Under the Settings tab, select List from the Allow drop down list;
- Click in the Source box, and then go to select the data you have created in step 1;
- If the data list haven't been converted to table, and you still want to update the drop-down list with the fresh data when adding or deleting data from the list, please directly type the below formula into the Source box:
In my case, Sheet2!$A$2 representing the first cell (exclude the header cell) of the data list, and Sheet2!$A:$A means that the data list locating in column A. You can change them based on the location of your data. You can click to know more about the OFFSET function.
- You can also type the items manually into the Source box and separate them by commas. See screenshot:
5. Go to the Input Message tab, fill in the Title box and the Input message box if you want to display an input message when selecting a drop-down list cell.
6. Go ahead to click the Error Alert tab, fill in the Title box and the Error message box.
7. Click OK to finish the whole settings. Now the drop-down lists are created.
Here highly recommend the Create simple drop-down list utility of Kutools for Excel. With this tool, you can easily create a simple drop down list with several clicks only.
Before applying Kutools for Excel, please download and install it firstly.
1. Select the range of cells to output the drop down list, and then click Kutools > Drop-down list > Create simple drop-down list.
2. In the Create simple drop down list dialog box, please configure as follows.
- The range you have selected in step 1 is displayed in the Apply to box. You can change the range as you need;
- In the Source section, if you want to create drop down lists based on data of a cell range or you just need to enter values manually, please select the Enter a value or reference a cell value option;
- In the text box, select the cell range or type in values (separate by commas) you will create the drop-down list based on;
- Click the OK button. See screenshot:
Note: If you want to create a drop-down list based on custom list, please select the Custom Lists option in the Source section, choose a custom list in the Custom Lists box, and then click the OK button.
Now the simple drop down list is created as the below demo shown.
More drop-down list operations:
Drop down list - Multi-selection
By default, a drop-down list cell only allows selecting an item per time. In many cases, you may need to select multiple items from a drop-down list, here we provide a VBA method in details for you to easily create drop-down list with multiple selections in Excel. Click to know more...
Drop down list - Auto complete
Normally, you can only select an item from the drop-down list or typing the entire data into the drop-down list cell. If there is a long drop-down list, it's not easy for you to find the accurate item in the list. Do you want to complete the value automatically when typing the first letter only? Click to know more...
Create Dynamic Drop-down List
Supposing there are two drop-down lists A and B in your worksheet, when you choose a value in drop-down list A, the values in drop-down list B will be updated automatically based on the selection in A. Here we provide detailed steps to help you easily create dynamic drop-down list with the Data Validation feature and the INDIRECT function. Click to know more...
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 years agoHighly appreciate the page. Great!
- To post as a guest, your comment is unpublished.· 5 years agoPerfect! Simple screen shots made building the drop down easy.
- To post as a guest, your comment is unpublished.· 6 years agoI was able to follow these steps and create a drop down list, however, when I save and exit, the next time I open the spreadsheet the drop down list is no longer there. How do I get it to save?
- To post as a guest, your comment is unpublished.· 6 years agoThat's good example.... :P
- To post as a guest, your comment is unpublished.· 6 years agoHow do I create a drop downlist with a Description that is different than my returned value?
For Example: Description in List is - "ABG Interests"
But I only want to return - "ABG" to the Cell
- To post as a guest, your comment is unpublished.· 7 years agoAfter I have created my drop down box with a description of different types of equipment, can I have in another column (a rate for each piece of equipment) automatically generate. If so can you please let me know how to do this.
- To post as a guest, your comment is unpublished.· 7 years agoGreat thanks! Now I'm looking to add a macro (linked to button)depending on the selection of the item on the menu...does anyone know what the syntax is for the drop down menu items?
- To post as a guest, your comment is unpublished.· 7 years agoThank you :)it helped a lot
- To post as a guest, your comment is unpublished.· 7 years agoI am most grateful, thanks
- To post as a guest, your comment is unpublished.· 7 years agoGenerous!!! Thanks a lot!
- To post as a guest, your comment is unpublished.· 7 years agoJust too wonderful-as simple as it is. I am excited!!! Thanx a lot.
- To post as a guest, your comment is unpublished.· 7 years agoAny way to make this work on both 2013 and 2007?
I created an excel file on my 2013 laptop and need to use the file on my 2007 desktop at work. How do I set it up so it works seamlessly on both?
- To post as a guest, your comment is unpublished.· 7 years agoWhen I did this in my 2013 version (the one on my laptop) it worked, but when I brought it into the office (office computer using 2007), it did not work. Any help on how to make it work on both seamlessly?
- To post as a guest, your comment is unpublished.· 7 years agoThanks! This was what I needed, as well. Steps were very simple.
- To post as a guest, your comment is unpublished.· 8 years agoThanks for this informative post, just what I was looking for :lol: