Allow Other Entries With Excel Drop Down List

You can make data entry easier by adding a drop down list on an Excel worksheet.

dropdownlistblank08 

In most cases, you want people to select an item from the list, to prevent typos and invalid entries. If they try to type something that’s not in the list, they’ll see an error message, and will have to try again.

Allow Non-List Entries

In some workbooks though, you might want to allow other entries in those cells, in addition to the items in the drop down list.

To allow other entries, you can turn off the Error Alerts in the data validation cells.

To turn off Error Alerts:
  1. Select all the data validation cells
  2. On the Ribbon, click the Data tab, and click Data Validation
  3. Click the Error Alert tab
  4. Remove the check mark from "Show error alert after invalid data is entered"
  5. Click OK to close the Data Validation box.

datavalerroroff

With error alerts turned off, you will be able to edit the cell, after selecting an item in the drop down list, or enter any value in the cell.

Data Validation Tips

For more data validation tips, please visit  my Contextures website, where I’ve recently updated the Data Validation Intro page, for Excel 2010. It includes the steps for inserting a named table, and using that dynamic list as the source for the drop down list items.

tablenamedrange

______________

You may also like...

17 Responses

  1. AlexJ says:

    That [F3] tip on the website to insert a named range is a great reminder.

  2. Jason M says:

    Hi Debra,

    If a user is allowed to disregard the list and enter their own item, then sometimes the next logical step is to add that user’s entry to the original data validation list. I assume you have encountered this before and developed a solution but I couldn’t find it on your website. For my own amusement I created a solution with formulas only…it was easier than I thought. A VBA solution may be better suited.

  3. Thanks Jason, your formula solution sounds interesting. I’ve got a VBA solution on my Contextures site:
    .
    http://www.contextures.com/excel-data-validation-add.html

    It automatically adds new items, and sorts the source list

  4. Jason M says:

    Very nice! Yes, the one drawback of the formula solution is that the list does not-resort. It simply appends the new entry to the list. If users add lots of new entries, then you have an partially sorted list and this can slow the user down.

  5. Birch says:

    Can a drop-down list be extended to DISPLAY more than 8
    records from the source data without scrolling down ? This is very inconvenient for (say) the 12 months of the year or
    any longer list of data.

  6. Julie says:

    Is there a way to edit a drop down menu to include more cells after it has been created? I can’t seem to add to it, or delete it.

  7. Mike says:

    Can we have a couple of dynamic dependent drop down lists which we can add items too like your standalone sample?

  8. Alex says:

    I’m having the trouble of trying to enter free text into the cell after an item from the drop-down menu is selected. the result is the drop-down menu item repeats itself. For example, if my drop-item is “Other” and I select that, and then attempt to free text immediately after, when I hit enter it repeats the word other. Any clue as to why this is happening and how I can fix it? Thanks much!

  9. Marcelo says:

    I’m having the trouble of trying to enter free text into the cell after an item from the drop-down menu is selected. the result is the drop-down menu item repeats itself. For example, if my drop-item is “Other” and I select that, and then attempt to free text immediately after, when I hit enter it repeats the word other. Any clue as to why this is happening and how I can fix it? Thanks much!

    http://metodoexcel.com.br/pincel-de-formatacao/

  10. Chris says:

    I’m having the opposite issue where I cannot restrict free text in a dropdown list. I have checked the error message box is selected and it is in ‘Stop’. I have 4 dropdowns in my report, all of them restrict free entry apart from 1. I have tried copying the first cell from another list to the first cell of the problem list and then relinking it to the correct list, but this reverts back to allowing free text. Can’t understand why! It is the longest list, with 41 possibilities (is there a maximum?)

  11. Arpan says:

    is it possible to set a condition such that if a definite value appears in a specified cell, then a dropdown is enabled for the current cell, but if any other value is present, then text is acceptable?

    Example:
    Cell A2 can have values [Manual, Test, Dummy]
    I want to set B2 such that if A2 has value Dummy, then B2 is a dropdown with values like [DummyA, DummyB], else user can enter any value in B2

    • @Arpan, create a list of items for the Dummy selection, e.g. DummyList. Then, select a blank cell, and name it as NoList.
      In cell B2, use this formula for the data validation:
      =IF(A2=”Dummy”,DummyList,NoList)
      Because the NoList range contains a blank cell, any entry will be allowed in cell B2 (it will show a blank item in the drop down list)

  12. kavindu says:

    Thanks. This helped a lot

  13. Thomas says:

    As soon as you make a product selection, a price comes along in colum F. How do you do that? A have a product and price list in a diffrent sheet, but i can’t figure out how to make this happen.

    So for example; select a product from the dropdownlist in A1, and it show the price in B1. And if you select a diffrent product in A1, a diffrent price in B1 appears.

    Can you help me please?

Leave a Reply to AlexJ Cancel reply

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