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

Chances are that you might want to validate and standardize user input in Excel using drop down menus. In today’s tutorial we’ll take you through the process of creating the drop down, validating user input as well as showing an error message if a invalid data is entered. Read further for the entire step by step procedure.

Create a drop down menu Excel worksheet

  • Open your M Microsoft Excel spreadsheet.
  • Now select the specific cell where your drop down menu has to be inserted.
  • Next, go ahead and click on Data in the Ribbon and select Data validation in the Data Tools menu section.
2015-09-27 22_02_49-Book1 - Excel
  • Next, we are going to define a list of values that will be allowed for usage in your combo box.
  • In the Settings tab, hit on the Allow drop down menu and select List.
2015-09-27 22_03_42-Data Validation
  • In the Source filed, enter the values which has to be displayed in the cell drop down menu.
2015-09-27 22_05_18-Data Validation
  • Important: A more robust method is to specify the list of values (LOV) in a different worksheet. Then associating the LOV with the Source field by clicking on the icon at the right hand side and pointing to the cells range that has list of values.
2015-09-27 22_05_44-Data Validation
  • Click OK to insert a drop down menu.

Data Validation

Next is to validate the user selection and show input messages in case that user input do not conform to the list of allowed values.

  • 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