How to Create a Drop-down List in Excel using Data Validation?

If you wish to create a drop-down list in Microsoft Excel, you need to use the Data Validation feature, which allows users to select from a list of rules to limit the type of data that can be entered in a cell.

Excel drop-down lists (also called drop-down boxes or combo boxes) allow users to enter data from a list of predefined items into a spreadsheet. In Excel, drop-down lists are used to limit the number of options available. Besides that, a drop-down prevents spelling mistakes and speeds up data entry.

How to Make a Drop-down List in Excel through Data Validation?

Use the steps to create a drop-down list using Data Validation in Excel:-

Step 1. Open a Microsoft Excel Worksheet.

Step 2. Enter data into the spreadsheet.

Step 3. Then, select the cells where you want the drop-down list to appear.

Step 4. Next, click on the Data tab in the menu bar, and in the Data Tools group, click on the Data Validation button.

Step 5. When you’re done, a Data Validation dialog box will appear on the screen.

Step 6. Here, switch to the Settings tab.

Step 7. Use the drop-down under Allow and choose the List option in the list.

Step 8. Next, click the Source button.

Step 9. Now, select the cell(s) from where you want to show the data in the drop-down list. For example, we have put the data on Sheet 2.

Step 10. After that, click the down arrow button under “Data Validation” to expand it.

Step 11. Then, click the OK button.

Step 12. In the cell(s) you selected in Step 3, you will now find a drop-down menu. For that, click on the cell, and a down arrow button will appear. Clicking on that, you can select the desired value that you have entered before.

How to Create a Drop-down List by Manually Entering Data?

Microsoft Excel also allows you to create a drop-down list by manually entering the data. Here are the steps to do so.

Step 1. First, select the cell(s) where you want to input a drop-down list.

Step 2. Next, click on the Data tab in the menu bar, and in the Data Tools group, click on the Data Validation button.

Step 3. When you’re done, a Data Validation dialog box will appear on the screen.

Step 4. Here, switch to the Settings tab.

Step 5. Under Allow drop-down menu, select the List option.

Step 6. Enter the items you want to include in the drop-down list in the Source field, followed by a comma.

Step 7. Finally, click the OK button to appear drop-down in cell(s).

How to Display a Custom Message when the Drop-down Cell(s) is Selected?

After you’ve created a drop-down list, you may want to make it more accessible by adding an input message. To do so, use these steps:-

Step 1. Select the cell(s) where the drop-down list is located.

Step 2. Next, click on the Data tab in the menu bar, and in the Data Tools group, click on the Data Validation button.

Step 3. When you’re done, a Data Validation dialog box will appear on the screen.

Step 4. Here, switch to the Input Message tab.

Step 5. Check the checkbox “Show input message when cell is selected.”

Step 6. Enter a suitable Title and up to 225 characters for “Input message” to appear in the drop-down list when it is clicked.

Step 7. Finally, click the OK button.

The next time you’ll click the drop-down cell(s), the above message will appear.

How to Show an Error Message in the Drop-down Cell(s)?

You can display an error alert when the wrong data is entered in the cell that’s not found in the list, similarly to displaying a custom message.

Step 1. Select the cell(s) where the drop-down list is located.

Step 2. Next, click on the Data tab in the menu bar, and in the Data Tools group, click on the Data Validation button.

Step 3. When you’re done, a Data Validation dialog box will appear on the screen.

Step 4. Here, switch to the Error Alert tab.

Step 5. Check the box “Show error alert after invalid data is entered.”

Step 6. Select a Style of error message from Stop, Warning, or Information.

Step 7. Enter the relevant Title and Error message in the provided box.

Step 8. Finally, click the OK button.

Once you have set the error message, it will appear the next time you enter invalid data in the drop-down cell.

Conclusion

In conclusion, creating drop-down lists in Microsoft Excel is a straightforward process using the Data Validation feature. It not only limits the number of options available but also prevents spelling mistakes and speeds up data entry. By following the steps outlined in this guide, you can create a drop-down list by selecting the data source manually or by entering the data into the source field. Additionally, you can customize your drop-down list by adding input messages or error messages to make it more accessible and user-friendly. Overall, Excel’s drop-down lists are an excellent tool for anyone who wants to manage data efficiently and accurately.

Leave a Reply