How to Add Dropdown List in Excel

Jun 17, 2020 ยท 3 minute read

In this page, we are going to explain, how to add, copy and update dropdown in excel and also how to handle errors and invalid inputs in excel.

How to Create Dropdown List in Excel;

For creating a dropdown list in excel we recommend two methods

If you have a few items in your list you can use this method.

First Method

1- Select the cells which you want to specify droplist.

2- Click on the Data tab.

3- Click on Data Validation.

4- In the Setting tab in the Allow part click on the arrow to open the list.

yes no

5- Choose the List item.

6- In the Source part write your items in the list,

Here our dropdown list has only two items and we input “Yes, No” in the Source part.

7- Now you can click on the arrow in your selected cells and choose your desired item.

yes no 1

Second Method

In this method, you have your list in our excel file and you want to create a dropdown list from your list.

For that you should follow these steps;

In this example, we have three columns and we want to create a dropdown list for the first column.

1- Select the cells which you want to specify a dropdown list for it.

2- Click on the Data tab.

3- Click on Data Validation.

4- In the Setting tab Click on Allow dropdown list and choose List item.

5- In the Source tab click on Arrow.

data validation

6- Go and select the items of your list for this column.

7- Click on Ok.

8- Now you can see the droplist with your desired items were created.

copy dropdownlist

How to Copy Dropdown List in Excel;

There are two ways to copy and paste a dropdown list.

First Method

1- Select the cells which you have a dropdown list on them.

2- Press Ctrl+C to make a copy from them.

3- Select the cell which you want to paste the cells there and Press Ctrl+V.

copy paste

4- Now you can see the dropdown list was transferred to those cells too.

choose item

How to Handle Errors and Invalid Input;

If you want to enter new data which there is not in your dropdown list, the program will warn you.

you can modify this warning, here we are going to explain it.

erorr

1- Click on the dropdown list.

2- Go to Data tab and click on Data Validation.

3- Click on the Error Alert tab.

4- In the Style part you have three options;

  • Stop; the program will not allow you to continue.

stop alert

  • If you choose “Warning” the program will only warn you,

you can specify the warning sentences here too, For instance, we choose for title “Error” and for the message “This item do not exist in the list, please add to the list”.

warning alert

  • If you choose the information you can write your item in the cell, but you can specify the message like the warning part here.

information alert

How to Update Dropdown List with New Data;

Sometimes you want to update the list, for that, you can follow these steps.

1- Before you start to modify the list go to the list on your excel file and update it.

2- Select all the cells which you want to update the dropdown list.

3- Click on the Data tab and select Data validation.

4- In the Source part click on the arrow.

select cells

5- Select the new list.

6- Tick the box “Apply these changes to all other cells with same settings”.

7- Click on Ok.

8- You can see the dropdown list was updated now.

updatelist

excel dropdown