Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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. At the beginning of the tutorial, let’s see how to create drop-down lists in a worksheet with a demo.

We also provide detailed steps of creating drop-down lists in Excel as follows.


Create drop-down list in Excel cells

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.

More drop-down list operations:


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    KAMBALE JEAN DE DIEU · 4 months ago
    Highly appreciate the page. Great!
  • To post as a guest, your comment is unpublished.
    Tom · 3 years ago
    Perfect! Simple screen shots made building the drop down easy.
  • To post as a guest, your comment is unpublished.
    Jules · 4 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Sunil · 4 years ago
    That's good example.... :P
  • To post as a guest, your comment is unpublished.
    Accountant · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Laura Lee · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Swanlake · 5 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Stark · 5 years ago
    Thank you :)it helped a lot
  • To post as a guest, your comment is unpublished.
    danny · 5 years ago
    I am most grateful, thanks
  • To post as a guest, your comment is unpublished.
    Abegga · 5 years ago
    Generous!!! Thanks a lot!
  • To post as a guest, your comment is unpublished.
    Kakaire Charles · 5 years ago
    Just too wonderful-as simple as it is. I am excited!!! Thanx a lot.
  • To post as a guest, your comment is unpublished.
    Amy · 5 years ago
    Any 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?

    Thanks,
    Amy
  • To post as a guest, your comment is unpublished.
    Amy · 5 years ago
    When 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.
    Christine M · 5 years ago
    Thanks! This was what I needed, as well. Steps were very simple.
  • To post as a guest, your comment is unpublished.
    Web Guy · 6 years ago
    Thanks for this informative post, just what I was looking for :lol: