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.
How to Add Item to Drop-Down List in Excel: 5 Methods
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 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.
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
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
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.
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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 and give your suggestions in the comment box.
Related Articles
- How to Create a Drop Down List from Another Sheet in Excel
- How to Remove Drop Down List in Excel
- How to Link a Cell Value with a Drop Down List in Excel
- How to Auto Update Drop-Down List in Excel
- How to Create Excel Drop Down List with Color
- How to Create Drop Down List with Filter in Excel
- How to Create a Drop Down List with Unique Values in Excel
- How to Copy Filter Drop-Down List in Excel
- Excel Drop Down List Not Working
