How to create Drop down list in Excel

Excel drop-down list is used to enter data in a spreadsheet from a pre-defined list. The main purpose of using drop down lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and it will make automation task easy as number of input is predefined.

Below is the two ways to create simple drop down

Comma separated Drop Down list:

In Below example I am trying to add drop down list in column B only highlighted cells  .

 Please follow the below steps:

1.       Select all required range or non-contiguous cells. Then  Go to Data Tab .

2.       Click on Data Validation icon drop down. click on the Data Validation

3.       In the Data validation window on the setting tab:

Ø  In the Allow drop down select “List” and make sure “In-Cell dropdown”  check box should be checked

Ø  In the Source Text box give the values which you want to be in drop down as comma separated.

 

Ø  Click ok .That’s what you are looking for

 

Get The values from different Cells:

If you do not want to write the Values directly to Source  as we did above , we can get those from other cells like below .

1.       Write the expected values in any column in the same worksheet or different. In my example I want Column F values to be in dropdown list for Column B.

 

2.       Select all required range or non-contiguous cells. Then  Go to Data Tab .

3.       Click on Data Validation icon drop down. click on the Data Validation

4.       In the Data validation window on the setting tab:

Ø  In the Allow drop down select “List” and make sure “In-Cell dropdown”  check box should be checked

Ø  In the Source Text box  click on the button situated at right hand it will allow you to select the range as shown in below . Once data selected click on the button again it will bring you to data validation Window

 

Ø  Click ok .That’s what you are looking for

 

Read More