Excel dropdown are important and easily used by excel beginners and is used if you do not want the user to type something in the cell and to be sure that they select value from the dropdown. There are various type of dropdown and this would be the 1st of three series of dropdown learning .

Dropdown in excel are prepared by using the formulas in excel

STEP-1 Create Values

Enter the values in any column (I have used column A). Here Fruits is the header and rest is the value which will be shown in the dropdown.

basic excel

STEP-2 Create Formula For Excel

Select the values from the column (leave header i.e., fruits) and go the formula tab and click define name (you can also right click over the select values and click the define name ).

Small modal will open, give it a name (I have given fruit )and click ok and close the window.

excel skills

STEP-3 Checking Excel Formula

Now we need to check whether the formula that we have created , has been created successfully ?
So now open the name manager by either clicking CTRL+F3 or go to formulas tab and click NAME MANAGER ,
if the name that you had specified earlier exists, that means you are able to create excel dropdown formula.

Name manager,ms excel

STEP-4 Create Data Validation

Once you are done with your step-3 then you need to select the cell in which you will show the values as dropdown(I have selected column E cell 1).
Go to the data tab of excel and click DATA VALIDATION . Modal will appear now select the list from the dropdown and click F3 for listing all the formulas. select the formula that you want to use for cell(in our case it is fruit).

Data Validation,use ms excel


Go to the cell 1 of column E you will see the down arrow . once clicked will list all the values.

excel dropdown

You can also watch the excel video below to better understand this excel basic dropdown.

i hope you have understood the concept and learnt to make the dropdown in excel . In next post we will learn about dynamic dropdown

Don’t forget to comment .


  1. Very nicely explained.

Leave a Reply

Your email address will not be published. Required fields are marked *