How to create drop-down list in Excel like a Pro?

The drop-down list is the great way to organize and sort the information in your office.

The excel drop-down list is used to enter the data in the cells from the pre-defined list.
​
The main function of the drop-down list is to limit the number of inputs and prevent the user from making mistakes and enter the data fast.
In this tutorial, I will discuss the number of ways of making the drop-down list:

Creating drop-down list with comma separated entries

​This is one of the easiest and the fastest ways to create the drop-down list. It is generally used when you want to create a questionnaire for your clients or the customers or for the staff.

You can create the drop-down list using this method and can make it attractive and easy to use for them.

Step:1 Select a cell or range for the drop-down list:
​
Just write the questions which you want to be answered by them in the following format as shown in the screenshot below:

Selecting range of cells for the drop-down list.

​After that select every cell next to the questions in order to make a drop-down list.

Step 2: Use data validation to create drop-down list:

The next step after selecting the cells is to go to the data validation and click on it as follows:

• Data> Data validation

Step 3: Select the options and type the entries:

• On clicking the data validation, the new window will pop up asking you to select the validation criteria:
• Under “Allow” select the List tick “Ignore blank” and “In-cell drop-down”
• In the “Source” field, you can type your options like:Daily, alternate days, weekly, once in 15 days

All the procedure is shown in the screenshot below:

Step 4: ​Your drop-down list is ready in no time:

Now when you click the OK tab after entering the values, the drop-down list is created in your selected cells.
​
The users can select the options of their choice from the drop-down box.

Creating drop-down list based on named range

Creating this type of drop-down list may take time but once created it is very easy to implement it anywhere in the excel worksheet.

Let us know to create this type of list.
​
Step 1: Type the entries of the drop-down list like I have typed in the screenshot shown below:

The next step would be to give the name to the list.

It can be done as follows:

• Select the list
• Right click them and then choose the Define name from the context menu or alternatively, you can press Ctrl + F3.
• The following window will open, asking you to define the name of the selected list. Give the name to the list like I have given it “Fruits” in the Name field mentioned below. 

{Please make sure that your name range doesn’t include space or hyphens, you can use underscore (_) instead}

• Also don’t forget to mention the correct range in the Refers to: field
• After that press OK

The next step would be to select the cell for the drop-down list 

You can select the range of cells or the entire column to create the drop-down list.
I have selected the single cell in the following screenshot:

After that apply data validation to the selected cell:

On the Excel ribbon look for Data tab and then click the Data validation. The process is shown in the following screenshot:

Write the name and configure the options:

In the data validation window under the settings tab, choose the following options:

• Under Allow field choose List
• Tick Ignore blank and In-cell dropdown
• Under Source field, type the name of your list starting with = sign, for example =Fruits
• Finally, click the OK button to create your dropdown list.

Your list is ready:

Thank you :)