A drop down list in excel prevents a user from entering an invalid value in a cell. Did you know that you can disable the validation and still use the drop down list?

Entering a value that is not in the drop down list will not cause an error alert. My sheet allows you to type a new value and it is instantly included to the drop down list. The drop down list contains unique distinct values extracted from cells in the excel defined table.

The following picture shows what I am trying to explain.

Dynamic drop down list41

The great thing about drop down lists and excel defined tables is that you don't need to copy the drop down list to new cells in the table. The excel defined table does that for you. There is no vba in this post. How is this done?

Insert an excel defined table

Insert an excel defined table or convert an existing range. You can use the shortcut keys CTRL + T or

  1. Go to tab "Insert" on the ribbon
  2. Click "Table" button.

Dynamic drop down list - no error validation

Insert a new "helper" sheet

Insert a new sheet, it is going to be our "helper" sheet. I renamed it to "Sheet2".

Dynamic drop down list - no error validation1

The formula in cell A2 extracts unique distinct values from Table1[Name].

Enter this array formula in cell A2:

=IFERROR(INDEX(Table1[Name]&"", MATCH(0,COUNTIF('Sheet2'!$A$1:A1, Table1[Name]), 0)), "")

Make sure you enter it as an array formula. If you don't know how, follow these steps:

  1. Select cell A2
  2. Paste above formula to formula bar
  3. Press and hold CTRL + SHIFT simultaneously
  4. Press Enter
  5. Release all keys.

If you did this right the formula now begins with a curly bracket { and ends with a curly bracket }. Don't enter these characters yourself.

Copy cell A2 and paste it to cells below as far as needed.

Create a new named range

  1. Create a new named range by pressing CTRL + F3 or go to tab "Formulas" and click "Name Manager" button.
  2. Click "New..." button and use this formula:
    ='Sheet2'!$A$2:INDEX('Sheet2'!$A$2:$A$100, (1/IFERROR(1/SUM(IF('Sheet2'!$A$2:$A$100&"", 1, 0)), 1)))
  3. Name it Unique. Remember to change the sheet reference if you use a different sheet name.

Dynamic drop down list - no error validation2

Build a drop down list

  1. Go back to your excel defined table
  2. Create a drop down list in one of the cells on the first row
  3. Go to tab "Data" on the ribbon
  4. Click "Data Validation" button on the ribbon
  5. Select List and type =Unique in source field.

Dynamic drop down list - no error validation3

Disable data validation error alert

The "Error alert" ta in the data validation window allows you to remove the error alert that is shown after an invalid value is entered.

  1. Go to tab "Data" on the ribbon
  2. Click "Data valdation" button
  3. Go to tab "Error alert"
  4. Disable "Show error alert after invalid data is entered"

Dynamic drop down list - no error validation4

Disabling the error alert allows us to use the drop down list if we want or type a new value in the cell.

A sorted drop down list from A to Z

Dynamic drop down list - no error validation5

The drop down list in this sheet contains sorted text values, download the file below if you want to know how it is made.

The names are from Disney's Frozen, my three year old daughter loves that movie.

If you want to learn more about array formulas join Advanced excel course.

Download excel *.xlsx file

Dynamic drop down list - add new values automatically.xlsx