When you add a drop-down list into a spreadsheet in Excel, you can either type the list in or use an existing list. You can also specify whether you want users to only be able to choose from the drop-down list, or whether users can override it.

Add a drop-down list n Excel (typing it in)

Select the cell or cells where you want to add the drop down list
Click on the Data tab
Click on Data Validation in the Data Tools Group
A dialogue box will appear, with three tabs – Settings, Input Message and Error Alert
(Make sure you are on Settings)
Choose List from the Validation Criteria Allow: drop-down box
In the Source box, type in the options you want in your drop-down list (making sure they are separated by a comma)
Make sure the In-cell drop down is ticked
Click on the Input Message tab, and type in a title and message (optional)
This will display as the user clicks into the cell or hovers over it
Click on the Error Alert tab (optional)
Choose the Stop style if you don’t want the user to enter incorrect data, then type an error title and message
Finally click on OK
You will then have a drop-down list in your selected cells

Showing the Input Message when your mouse is hovered over the cells

Error Message — Showing the Input Message when your mouse is hovered over the cells

Add a Drop-Down List Video Tutorial

Use an Existing List for a Drop-Down

Note: Before you add validation, you must have the list (that you want as your drop-down options) in a worksheet. In the example below the list of departments is already typed in Column G.

Select the cell or cells where you want your drop down list
Click on the Data tab
Click on Data Validation in the Data Tools Group
A dialogue box will appear, with three tabs – Settings, Input Message and Error Alert
Make sure you are on the Settings tab

Choose List from the Allow: drop-down box

In the Source box click on the up arrow

Select your data source – the cell references will be shown in the data source box

Click on the down arrow icon on the right-hand side of the source box to maximise the dialogue box again

Make sure the In-cell dropdown is ticked
Click on the Input Message tab, and type in a title and message (optional). This will display as the user clicks into the cell or hovers over it
Click on the Error Alert tab (optional). Choose the Stop style (as you don’t want the user to enter incorrect data), then type an error title and message
Finally click on OK
You will have an in-cell drop-down list being fed by your original list.

Editing your Drop-Down List

If you want to add to your drop-down list, you can add it to the middle of your original list so that Excel automatically picks it up or you can edit your data validation to include you new data. However, if you add it to the bottom of your list, it won’t be automatically included in your drop-down list.

Edit list — In this screenshot, I’ve added “Operations” to the end of my list of departments, but Excel hasn’t automatically added it to my drop-down list.

Adding a new option to your list

Click on any cell in your list
Right-click and choose Insert
Select Shift Cells Down
Click OK
A blank cell will appear
Type in a new option
When you use the drop-down, your new option will be displayed.

Edit your data validation

Select the cell or cells where you have your drop-down list
Click on the Data tab and choose Data Validation in the Data Tools Group
Click on the up arrow in the Source box
Select the data to feed your drop-down list (including your new data)
Click on the down arrow to return to the dialogue box
Make sure the box labelled Apply these changes to other cells with the same settings is ticked, as this will update any identical validation that has been applied to other cells (when it matches the (original) validation of the cell(s) being edited)
Click OK

The drop-down list will have updated correspondingly.

Deleting a Drop Down List

Select the cell or cells where you want to get rid of your drop down list (alternatively see step 4 below)
Click on the Data tab
Click on Data Validation in the Data Tools Group
If you want to delete the drop-down list from all of the cells that contain that list, make sure the box labelled Apply these changes to other cells with the same settings is ticked (if you haven’t highlight all the cells containing the drop-down list originally – this tick box will do if for you!)
Click on Clear All (the data validation will show Any Value in the Allow: drop-down box)
Click on OK

I hope you enjoyed this tutorial about how to remove duplicates in Excel. For more Excel tutorials, please click here. To view our YouTube channel just click here.