How to add a drop-down menu for data validation in Excel 2016?


Capturing user input using drop down menus in your Excel spreadsheet can be leveraged as a very powerful data validation technique. In today’s tutorial we’ll take you through the process of creating the drop down menu, validating user input and showing an error message if a invalid data is entered. Read further for the procedure.

To create a drop down menu in your Excel worksheet:

  • Open your Excel spreadsheet, then select the cell where drop down menu has to be inserted.
  • Now, click on Data in the ribbon and select Data validation in the data tools menu.

2015-09-27 22_02_49-Book1 - Excel

  • Click on the Allow drop down menu and select List.

2015-09-27 22_03_42-Data Validation

  • In the source, enter the values which has to be displayed in the cell drop down menu.

2015-09-27 22_05_18-Data Validation



  • We can also enter the values for the source by click on the icon right to source and select the cells with different data’s.

2015-09-27 22_05_44-Data Validation

  • Click OK to insert a drop down menu.

Data Validation:

  • To insert a input message when the drop down menu cell is clicked, click on Input Messages in the data validation dialog box.
  • Make sure the “Show input message …” box is checked on.
  • Then, enter your user dialog title and the information in the space provided and select OK.

2015-09-27 22_07_06-Data Validation

Displaying Error Messages:


  • To show a error message when an invalid data is entered in the drop down menu cell, click on Error Message in the data validation dialog box.
  • Make sure the “Show error alert …” box is checked on.
  • Now enter the title and the error message respectively to be displayed if an invalid data has been entered.

2015-09-27 22_10_26-Data Validation

  • Also we can insert the style symbol with the error message like Stop, warning, information symbols.

2015-09-27 22_11_07-Data Validation

You are all set.  Thanks for reading :-)

Leave a Comment: