Step-by-step tutorial for creating a drop-down list with data valuation

Validation Data feature effectively improves data entry in Excel and reduces typos and errors. You can use the validation criterion to restrict the input data or even define certain rules for entering information in the software yourself so that you can prevent the entry of unwanted data.

Validation Data If invalid data is entered, the program displays a message and prevents the user from entering this data. To better understand this feature, let’s take an example:

In column A we have a list of our products and we want to prepare a drop-down list of all products.

C: \ Users \ PC \ Desktop \ image-117.png

To do this, follow these steps:

  • Select any cell in the worksheet you want.
  • To create a drop-down list, go to Data > Data Tools > Then click on Data Validation .

C: \ Users \ PC \ Desktop \ image-210.png

  • Or can the shortcut ALT + D + L used.
  • The Data Validation dialog box appears.

C: \ Users \ PC \ Desktop \ image-310.png

  • In the Allow list , select “List” .

C: \ Users \ PC \ Desktop \ image-47.png

  • In the Source tab , select the range of your product list.

C: \ Users \ PC \ Desktop \ image-57.png

Note: Before clicking OK , you will notice that the “Ignore blank” and “In-cell Dropdown” boxes are checked by default . The first box is “Ignore blank” to allow or not to allow empty cells to enter as input. If you uncheck it, Excel will not allow empty cells to enter, and if an empty cell is entered, an error message will appear. 

“In-cell dropdown” is used to display the arrow next to the cell (drop-down list flash). Therefore, if you uncheck this option, the slider will disappear.

  • Now go to “Input Message” and there in the title box , write ” Select Product Name ” and in the input message box , write “Select the product name from the list” .

C: \ Users \ PC \ Desktop \ image-65.png

Then we go to the “Error Alert” section . You will see that the style is set to ” STOP ” by default . Therefore, in the title box , write “Invalid Product Name” and then in the error message box , “Please select the product name from the list” . And now we click on OK .

C: \ Users \ PC \ Desktop \ image-75.png

By clicking OK , we can slide the arrow to see the incoming message in a yellow box.

C: \ Users \ PC \ Desktop \ image-85.png

  • After clicking on the drop-down menu, we can see all the products we selected in the drop-down list. You can select and use any of these instead of typing or remembering them.

C: \ Users \ PC \ Desktop \ image-95.png

Note: To delete text in a cell that has validation, you can go to the cell and click on it and then press the Delete key . Content will be removed from the cell but will remain in the drop-down list.

Also, the drop-down list does not open when you click on normal (invalid) cells. In other words, only if you click on cells that have validation, a drop-down list will open.

C: \ Users \ PC \ Desktop \ image-105.png

If you want to remove the data validation completely from the cell or domain, just go to the data validation window . There you will see the “Clear All” option.

Click on it and then click OK . When you click enter, the data validation is removed from the cell.

C: \ Users \ PC \ Desktop \ image-118.png


There is another way to create a drop-down list. We can create a drop-down list by defining the domain name. To better understand this method, follow these steps:

  • Well, we have the same list as before, sorted from A9 to A15 .
  • Select the range and now we can go to the name box to define a name for this range or we can use the Define Name field to name it.
  • Go to the Formula > Defined Names tab and then click on Define Name .

C: \ Users \ PC \ Desktop \ image-125.png

  • Now write “Product_Name” in the name box . We see the range we have already selected here. Then click OK .

C: \ Users \ PC \ Desktop \ image-134.png

  • Go to the Data Validation window again and select “List” in the validation criteria section . This time, instead of selecting the range, we see the name we entered in the defined name field in the source box .

C: \ Users \ PC \ Desktop \ image-144.png

Note: If you remember the name you gave to this list, you can type it here. To do this, first press the “=” sign or the F3 key until the “PASTE NAME” window appears. 

You can find all the names in the workbook in this window.

  • We name the range “Product_Name” , click on it and then click OK .
  • We click OK to create a drop-down list for this cell.

C: \ Users \ PC \ Desktop \ image-153.png

Well, this way you can create a drop-down list using data validation in Excel.

Leave a Reply

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