Author: Oscar Cronquist Article last updated on January 21, 2023

Add new items to a drop down list automatically

A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that is not in the drop-down list will not cause an error alert.

My worksheet 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 animated 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?

1. 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. Press with left mouse button on the "Table" button. A dialog box appears, see the image below.
  3. Make sure your data is selected and that the check box is enabled if you have table headers.
    Dynamic drop down list - no error validation
  4. Press with left mouse button on the OK button.

Add new items to a drop down list automatically Excel table 1

The table is now formatted differently,this makes it easy to identify Excel Tables on a worksheet.

Back to top

2. Insert a new "helper" sheet

Insert a new sheet, it is going to be our "helper" sheet. I renamed it "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 the 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.

Update! Excel 365 dynamic array formula:

=UNIQUE(Table1[Name])

A dynamic array formula is not entered as an array formula. It works like a regular formula, press Enter only.

Read more about the UNIQUE function.

2.1 Explaining array formula in cell A2

Step 1 - Count cells based on a condition

The COUNTIF function counts cells that meet a condition.

COUNTIF('Sheet2'!$A$1:A1, Table1[Name])

becomes

COUNTIF("Unique values",{"Elsa";"Ellen";"Ella";"Ellie";"Anna";"Ellen"})

and returns {0; 0; 0; 0; 0; 0}.

Step 2 - Find relative position of the frist 0 (zero) in the array

The MATCH function returns the position of a given value in a cell range or array.

MATCH(lookup_value, lookup_array, [match_type])

MATCH(0,COUNTIF('Sheet2'!$A$1:A1, Table1[Name]), 0)

becomes

MATCH(0,{0; 0; 0; 0; 0; 0}, 0)

and returns 1.

Step 3 - Get value based on the relative position

The INDEX function returns a value from a cell range or array based on a row and column number.

INDEX(array, [row_num], [column_num])

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

becomes

INDEX(Table1[Name]&"", 1)

becomes

INDEX({"Elsa"; "Ellen"; "Ella"; "Ellie"; "Anna"; "Ellen"}&"", 1)

and returns "Elsa" in cell A2.

Step 4 - Return a blank if no value is found and an error is returned

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

becomes

IFERROR("Elsa", "")

and returns "Elsa".

Back to top

3. Create a new Excel Named Range

  1. Create a new named range by pressing CTRL + F3 or go to tab "Formulas" and press with left mouse button on the "Name Manager" button.
  2. Press with left mouse button on the "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

Back to top

4. 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. Press with left mouse button on the "Data Validation" button on the ribbon
  5. Select List and type =Unique in the source field.

Dynamic drop down list - no error validation3

Back to top

5. 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. Press with left mouse button on the "Data validation" 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.

Back to top

6. 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, get the file below if you want to know how it is made.

Back to top

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

Get the Excel file


Dynamic-drop-down-list-add-new-values-automaticallyv2.xlsx

Back to top