Drop-down list is an interesting feature of Microsoft Excel. Using this tool, we can choose items from a given list, without typing any value. It restricts us from inputting unnecessary data in Excel. Normally the items on the drop-down list are predefined. But sometimes we may need to add a new item to the drop-down list. So in this article, we will discuss how to add items to the drop-down list in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Methods to Add Item to Drop-Down List in Excel
We will apply 5 methods to add items to the drop-down list in Excel. Adding items will depend on how a drop-down list is created. We will consider the following dataset for this purpose.
1. Add Item to Drop-Down List by Adding Item to Existing Data Range in Excel
We will show how to add items to Range based drop-down list. There are two options to add items to a range-based drop-down list.
1.1 Add Item within the Range Using Insert Feature
We can add items using the insert feature in the range-based drop-down list.
Step 1:
- Move to Cell E4.
- Select the Data Tools group from the Data tab.
- Now, choose the Data Validation option.
Step 2:
- Choose List from the Allow field.
- Now, choose the desired range in the Source field and then press OK.
Look at the dataset. The drop-down list is visible at Cell E4.
We will use the Insert feature to add items at any position of the selected range.
Step 3:
- Move the cursor to any position of the Fruit List column.
- Press the right button of the mouse.
- Choose Insert from the menu.
Step 4:
- Select Shift cells down from the Insert window.
Step 5:
- Now, press OK and look at the dataset.
A new cell is added to the range.
Step 6:
- Now, write a Mango on Cell B9 and click on the drop-down list of Cell E4.
Mango is added to the drop-down list.
1.2 Add Item at the Bottom of Range
We will add new items at the bottom of the data range by changing the source range.
Step 1:
- Add a new item at the bottom of the Fruit List column.
Step 2:
- Go to the Data Validation field by following the steps shown before.
- Now, modify the Source by selecting the range from the dataset.
- Mark the option Apply these changes to all other cells with the same settings and press OK.
Step 3:
- Now, move to Cell E4 and click on the down arrow to check the drop-down list.
The newly added item is shown on the list.
Read More: How to Create List from Range in Excel (3 Methods)
2. Add Item to Drop-Down List by Editing a Named Range
We can form a drop-down list using Named Range in Excel. New items will be added to the drop-down list by changing this Named Range.
Step 1:
- Select the cells of the Fruit List column.
- Select Define Name group from the Formulas tab.
- In the Refers to field select the range for Named Range. Then, press OK.
Step 2:
- Move the cursor to Cell E4.
- Go to the Data Tools group from the Data tab.
- Then, select Data Validation from the options.
Step 3:
- The Data Validation window will appear. Put the Named Range title on the Source box.
- Then click OK.
Look at the dataset now.
The drop-down list is visible at Cell E4.
We will add new items at the bottom of the dataset and modify the Named Range and that will reflect on the drop-down list.
Step 4:
- We added a new item at the bottom of the dataset.
- Go to Name Manager from the Formulas tab.
Step 5:
- In the Name Manager window, go to Refers to select the updated range with newly added data.
- Then click Close.
Step 6:
- A new dialog box will appear for permission. Choose Yes.
Again, go to Cell E4 and click on the down arrow sign.
The newly added item is shown on the list.
We can apply the Named Range in another simple way. Just select the cells, go to the name bar, and put your desired name.
Read More: How to Edit Drop-Down List in Excel (4 Basic Approaches)
3. Create a Table-Based Drop-Down List and Add New Item
We will form a drop-down list based on Excel Table and add new items from the bottom of the table.
Step 1:
- Select all the cells of the Fruit List Then, press Ctrl+T.
- The Create Table window will appear. The selected range will show here.
- Mark the box of My table has headers and then press OK.
Step 2:
- Move the cursor to Cell E4.
- Now, go to the Data Tools group from the Data tab.
- Click on the Data Validation option.
Step 3:
- Select the range on the Source field and press OK.
Step 4:
- Click on Cell E4 and press the down arrow sign.
We can see that the drop-down list is visible in the dataset.
Step 5:
- Now, go to the last cell of the Table. Add a new item and press the Enter button.
We can see that the new item is showing on the drop-down list.
Read More: Create Excel Drop Down List from Table (5 Examples)
Similar Readings
- Creating a Drop Down Filter to Extract Data Based on Selection in Excel
- How to Create Excel Drop Down List with Color (2 Ways)
- Create a Searchable Drop Down List in Excel (2 Methods)
- How to Create Drop Down List with Filter in Excel (7 Methods)
- Excel Drop Down List Not Working (8 Issues and Solutions)
4. Add Item Manually in an Excel Drop-Down List
We will manually input the items to form a drop-down list in Excel.
Step 1:
- Go to Cell E4.
- Go to the Data Tools group from the Data.
- Choose the Data Validation option.
Step 2:
- Put 5 items manually on the Source field of the Data Validation option.
- Then press OK.
Click on the down arrow of Cell E4.
We can see 5 items visible on the drop-down list.
Step 3:
- Again, go to the Data Validation option and add a new item to the Source box.
- Mark on Apply these changes to all other cells with the same settings option and then press OK.
Step 4:
- Go to Cell E4.
We can see that the new item is added to the drop-down list.
Read More: How to Make a Drop Down List in Excel (Independent and Dependent)
5. Add Item by Utilizing Dynamic Drop-Down List in Excel
We can utilize the Excel Dynamic Drop-Down list to add new items.
Step 1:
- Select Cell E4Â first.
- Choose Data Tools from the Data tab.
- Select the Data Validation option.
Step 2:
- Put the following formula on the Source option.
- Then, press OK.
=OFFSET($B$5,0,0,COUNTA($B:$B),1)
Now, go to Cell E4 of the dataset. The drop-down list is visible here.
Step 3:
- Now, add a new item on the bottom cell of the Fruit List column and press Enter.
Again, go to Cell E4 and see that the new item is visible here.
Read More: How to Create Dynamic Dependent Drop Down List in Excel
Conclusion
In this article, we described how to add new items to the drop-down list in Excel. Go through the methods and choose your desired method. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
Related Articles
- Excel Drop Down List Depending on Selection
- How to Make a Drop-Down List Based on Formula in Excel (4 Ways)
- Conditional Drop Down List in Excel (Create, Sort and Use)
- How to Link a Cell Value with a Drop Down List in Excel (5 Ways)
- Use IF Statement to Create a Drop-Down List in Excel
- How to Extract Data Based on a Drop Down List Selection in Excel