Adding data to a drop down list

Copper Contributor

I have attempted to add data to a previously set up drop down list associated with a cell, I am unable to add another item? 

2 Replies

When adding a data for you drop down list, you also need to update your SOURCE on Data Validation Criteria. See the image below



If you do not want to edit your Data Validation Source, I have 2 suggestions method for ya'. 

  1. Using OFFSET and COUNTA Functions
    1. Select the cell where you want to put the drop-down list.
    2. Go to the Data tab >> Data Validation group >> Data Validation.
    3. In the Data Validation dialog box, select List from different options under Allow.
    4. Type the following formula in the Source box: =OFFSET($A$2,0,0,COUNTA(A:A)-1).
    5. Here, $A$2 is the starting cell of the range, the following 2 zeros are indicating that the cell reference will not move by any row or column number. Finally, COUNTA(A:A)-1 is indicating the height number of the range which will be the number of rows having texts or numbers.
  2. Defining Name for a Range and Creating Table
    1. Select the source range.
    2. Click the tab “Formula” in the ribbon.
    3. Click the button “Define Name” in the toolbar.
    4. Input a name into the “Name” Text Box.
    5. Input the range into the “Refers to” text box.
    6. Click a cell within the source range.
    7. Click the tab “Insert” in the ribbon.
    8. Click the button “Table” in the toolbar

Here's the source/reference: 2 Methods to Auto Refresh the Drop-Down List in Your Excel Worksheet




To add additional items to a drop-down list in Excel, you need to modify the source of the list.

Here is a step-by-step guide on how to add new items:

  1. Select the cell with the drop-down list: Click on the cell where you have the drop-down list.
  2. Go to the "Data" tab: In the Excel ribbon, navigate to the "Data" tab.
  3. Click on "Data Validation": In the "Data Tools" group, you will find "Data Validation." Click on it.
  4. Go to the "Settings" tab: In the "Settings" tab of the "Data Validation" dialog box, you will find the "Allow" drop-down list. Choose "List."
  5. Modify the source: In the "Source" field, you will see the range of cells that currently make up your drop-down list. Edit this range to include the new item you want to add.

For example, if your current range is Sheet1!$A$1:$A$5, and you want to add a new item in cell A6, change the range to Sheet1!$A$1:$A$6.

6. Click "OK":

After modifying the source, click "OK" to apply the changes.

Now, your drop-down list should include the newly added item.


If you are using a named range for your drop-down list, you can also modify the named range to include the new item. To do this, go to the "Formulas" tab, click on "Name Manager," find and edit the named range associated with your drop-down list.


Remember, if your drop-down list is based on a table or a dynamic range, you might need to adjust the table or range definition to include the new item.

After making these changes, you should be able to see and select the newly added item in your drop-down list.


My answers are voluntary and without guarantee!


Hope this will help you.


Was the answer useful? Mark as best response and like it!

This will help all forum participants.