Skip to main content

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.


Create data validation drop down list with build-in in Excel

1. Firstly, you need to create a list with the data you will display in the drop-down list.

Tip: In many cases, you may need to add new data or delete data from the list. For automatically updating the drop-down list with the fresh data, you need to convert the source list to table.
  • 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;

Tips:

  • 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:
    =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A),1)
    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.

Notes:

1. The drop-down arrow is visible only when the cell is selected.
2. The error alert will pop up with specific title and error message when invalid data is entered into the drop-down list cell.

Quickly create a simple drop down list with an amazing tool

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.

  If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.


More drop-down list operations:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (15)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Highly appreciate the page. Great!
This comment was minimized by the moderator on the site
Perfect! Simple screen shots made building the drop down easy.
This comment was minimized by the moderator on the site
I 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?
This comment was minimized by the moderator on the site
That's good example.... :P
This comment was minimized by the moderator on the site
How 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 Thanks
This comment was minimized by the moderator on the site
After 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.
This comment was minimized by the moderator on the site
Great 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?
This comment was minimized by the moderator on the site
Thank you :)it helped a lot
This comment was minimized by the moderator on the site
I am most grateful, thanks
This comment was minimized by the moderator on the site
Generous!!! Thanks a lot!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations