How do you add rows to a drop down list automatically Dynamic Data Validation lists?

How to automatically add new items to a drop down list

Author: Oscar Cronquist Article last updated on April 21, 2021

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

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.

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

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.

What's on this webpage

  1. Insert an Excel defined table
  2. Insert a new "helper" worksheet
  3. Create a new Excel Named Range
  4. Build a Drop-Down List
  5. How to disable Data Validation error alert
  6. Drop-Down List containing values sorted from A to Z
  7. Get Excel file

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.
    How do you add rows to a drop down list automatically Dynamic Data Validation lists?
  4. Press with left mouse button on the OK button.

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

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".

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

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 belowas 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 tochange the sheet reference if you use a different sheet name.

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

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.

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

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"

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

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

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

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

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

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

Back to top


Weekly Blog EMAIL

Email
How do you add rows to a drop down list automatically Dynamic Data Validation lists?
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.


How do you add rows to a drop down list automatically Dynamic Data Validation lists?

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We []

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

Populate drop down list with unique distinct values sorted from A to Z

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using []

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

Apply dependent combo box selections to a filter

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. []

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are []

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

Dependent drop-down lists in multiple rows

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on []

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

Change chart data range using a Drop Down List [VBA]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above []

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

Run a Macro from a Drop Down list [VBA]

This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two []

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

Change PivotTable data source using a drop-down list

In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down []

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

Create a drop down list containing alphabetically sorted values

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list []

Comments (15)

15 Responses to How to automatically add new items to a drop down list

  1. Ola.S says:
    March 19, 2015 at 5:55 pm

    Place the curser in cell A8 (your attached spreadsheet).
    Press with right mouse button and select from the menu: "Pick_From Drop-Down List..." - works only for text.
    Rarely used, even by the pro's.
    //Ola.S

    https://www.pcreview.co.uk/threads/pick-from-drop-down-list-gives-empty-or-erroneous-result.4026822/

    Reply
    • Oscar says:
      March 19, 2015 at 7:04 pm

      Ola.S

      I am not sure I understand.

      Press with right mouse button and select from the menu: "Pick_From Drop-Down List..." - works only for text.
      To be honest, I didn't know you could. That drop down list is not the same thing as a data validation rule - drop down list. Try it yourself, numbers work fine in my first example.

      The drop down list is automatically copied to the next cell below when the table grows. Example, select the last cell in the table, cell C7 and press TAB key. A new row is inserted and cell A8 has a drop down list.

      There are other ways to insert new table rows. Press with right mouse button on a cell and hover over "Insert". Press with left mouse button on "Insert Table rows above" or "Insert Table rows below"

      Thank you for commenting.

  2. mma173 says:
    March 22, 2015 at 1:00 pm

    Thanks' Oscar for sharing this trick.

    I wonder what are the advantages of using tables?

    Reply
    • Oscar says:
      March 23, 2015 at 2:24 pm

      mma173,

      - Sort and filter
      - Sum by adding a row for total
      - By entering a formula in one cell in a table column, you can create a calculated column in which that formula is instantly applied to all other cells in that table column.
      - Structured references
      - Easy to reference a table or table column
      - Formatting
      - Easy to insert or delete table columns or rows

  3. Oscar says:
    March 24, 2015 at 7:45 pm

    I got a question by email:

    Is it possible to make a drop down list autocomplete if you have hundreds names instead of scrolling down into drop down list?

    Yes, almost. It works only if the values in the drop down list are sorted.

    Example

    1. Type E in the cell
    2. Press with left mouse button on the "Drop down list" arrow
    3. You are now on letter E in the drop down list
    4. Select a value

    How do you add rows to a drop down list automatically Dynamic Data Validation lists?

    Reply
  4. gemma harlot says:
    June 18, 2016 at 3:45 am

    Helpful comments - Incidentally , if anyone have been needing to merge PDF or PNG files , I merged a tool here https://goo.gl/elSZt2.

    Reply
  5. MF Jones says:
    August 31, 2017 at 5:59 pm

    Hey Oscar, thanks a lot for the method you outlined above! It helped me accomplish exactly what I'd set out to do. There is one issue I ran into using your instructions above, the formula you specify the reader's to use in step 2 of "Create a New Named Range" is:

    ='Sheet2'!$A$2:INDEX('Sheet2'!$A$2:$A$100,(1/IFERROR(1/SUM(IF('Sheet2'!$A$2:$A$100&"", 1, 0)), 1)))

    However, this formula would only list the value of A2 from "Sheet2" in my tables drop down list. After messing with it for a while I finally opened your example to find out where I went wrong & discovered that the formula you specified didn't match the formula you used in the example workbook! The formula in step 2 needs to be modified to include the less-than / greater-than () portion of the formula.

    Corrected formula should be:
    ='Sheet2'!$A$2:INDEX('Sheet2'!$A$2:$A$100,(1/IFERROR(1/SUM(IF('Sheet2'!$A$2:$A$100"",1,0)),1)))

    Thanks again Oscar!

    -Jones

    Reply
    • Nikita says:
      May 18, 2018 at 4:09 pm

      Hey Jones,

      Thanks for the tip, I was having the same issue and couldn't figure out why.
      Oscar, thank you for this review, it's saved me.

  6. MF Jones says:
    August 31, 2017 at 6:02 pm

    Ahh, it is automatically removing the less than / greater than symbols () in the code! :o/

    Reply
    • Oscar says:
      September 1, 2017 at 6:46 am

      MF Jones,

      thank you for commenting.
      Yes, wordpress removes html characters unfortunately.

  7. Michael says:
    January 30, 2018 at 6:24 pm

    Hey Oscar!

    I am trying to make an automatically updating drop down menu. I am close to figuring it out. What I want to do is similar to what you have, except if a name gets deleted, I don't want it to go away on the list. Is this possible?

    For instance, if the chart was one row and I typed in 3 name, all 3 names would show up on the named range, ultimately on the drop down list.

    Reply
    • Oscar says:
      February 2, 2018 at 2:20 pm

      Michael,

      I am trying to make an automatically updating drop down menu. I am close to figuring it out. What I want to do is similar to what you have, except if a name gets deleted, I don't want it to go away on the list. Is this possible?
      Not with formulas, as far as I know.

      For instance, if the chart was one row and I typed in 3 name, all 3 names would show up on the named range, ultimately on the drop down list.
      There is no chart in this article?

  8. Shane says:
    June 23, 2019 at 11:51 pm

    Hello Oscar, great post and very helpful. If starting a new workbook, with an existing list of 20-30 items of data i want available for the drop down, but with the table only having 3-4 of these so far, can the other items be included in the drop-down list? At the moment, they only show if included in the table.
    Thanks again.

    Reply
  9. 65Snake says:
    July 10, 2019 at 4:29 pm

    Oscar, Thanks for the method to automatically add and sort drop-down list items. I've been trying to resolve one issue, which I can't seem to figure out (ref. your Excel spreadsheet). What Ive found is that if theres a name in the first row of the "Name" column of the "Table2" ("Add and sort values" sheet), everything works as expected. That's even true if there are rows below the first row of the "Name" column of the "Table2" that do not have a name in them, i.e., blank cells between cells with names. However, the issue I've come across is that if there's not a name in the first row of the "Name" column of the "Table2", any names in any of the rows below the first row are ignored. I can't seem to resolve why the array formula in the column "A" cells on the "Named range1" sheet require a name in the first row of "Table2". I was hoping you could provide some insight as to why this is happening and a possible fix to allow the first row to be blank and have non-blank rows below. I've provided a photo of with and without a name on the first row--see link embedded.

    https://imgur.com/nNvHvbW

    Reply
  10. Jason Brandle says:
    September 7, 2021 at 11:52 pm

    Can this be done in Google Sheets?

    Reply

Leave a Reply

Click here to cancel reply.

Name (required)

Mail (will not be published) (required)

Website

Δ

How to comment

How to add a formula to your comment
Insert your formula here.

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

How do you add rows to a drop down list automatically Dynamic Data Validation lists?

You can contact me through this contact form