In the vast world of Microsoft Excel functionalities, one feature stands out for its ability to guide user input seamlessly: Data Validation. This tool isn’t just about ensuring accurate data entry; it’s about facilitating a user-friendly experience where choices are laid out clearly, ensuring minimal room for error. While Excel boasts a plethora of Active X controls, including Command buttons, Spin buttons, and more available on the Developer tab, there’s often a need to simplify choices for the user. Enter the drop-down list—a concise way to present multiple options without overwhelming the user or taking up excess spreadsheet real estate. And guess what? Crafting this handy tool is made possible using Data Validation. If you’re looking to enhance your spreadsheet with a drop-down list, you’re in the right place. This risewindows guide is poised to walk you through the step-by-step process of leveraging Data Validation for this very purpose.
How to Make a Drop-down List in Excel through Data Validation?
Follow the steps to create a drop-down list using Data Validation in Excel:-
Step 1. First, open a Microsoft Excel Worksheet.
Step 2. Now, enter data into the spreadsheet.
Step 3. Then, select the cells where you want the drop-down list to appear.
Step 4. After that, click on the Data tab in the menu bar, and in the Data Tools group, click on the Data Validation button.
Step 5. Next, a Data Validation dialog box will appear on the screen.
Step 6. Then, switch to the Settings tab.
Step 7. Now, use the drop-down under Allow and choose the List option.
Step 8. After that, click the Source button.
Step 9. 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. Next, click the down arrow button under “Data Validation” to expand it.
Step 11. Now, click the OK button.
Step 12. Finally, in the cell(s) you selected in Step 3, you will find a drop-down menu. Click on the cell, and a down arrow will appear. Clicking on that, you can select the desired value you have entered.
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. Do the following steps:-
Step 1. Firstly, select the cell(s) where you want to input a drop-down list.
Step 2. Now, click on the Data tab in the menu bar, and in the Data Tools group, click on the Data Validation button.
Step 3. Then, a Data Validation dialog box will appear on the screen.
Step 4. After that, switch to the Settings tab.
Step 5. Next, under Allow drop-down menu, select the List option.
Step 6. Then, enter the items you want to include in the drop-down list in the Source field, followed by a comma.
Step 7. Lastly, 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?
You may want to make it more accessible by adding an input message after you’ve created a drop-down list. For that, perform these steps:-
Step 1. First, select the cell(s) where the drop-down list is located.
Step 2. Now, click on the Data tab in the menu bar, and in the Data Tools group, click on the Data Validation button.
Step 3. Then, a Data Validation dialog box will appear on the screen.
Step 4. After that, switch to the Input Message tab.
Step 5. Next, check the “Show input message when the cell is selected.”
Step 6. Then, enter a suitable Title and up to 225 characters for the “Input message” to appear in the drop-down list when clicked.
Step 7. Lastly, click the OK button.
The next time you click the drop-down cell(s), the above message will appear.
How to Show an Error Message in the Drop-down Cell(s)?
To display a custom message, similarly, you can display an error alert when the wrong data is entered in the cell that’s not found in the list.
Step 1. As usual, select the cell(s) where the drop-down list is located.
Step 2. Now, click on the Data tab in the menu bar, and in the Data Tools group, click on the Data Validation button.
Step 3. Then, a Data Validation dialog box will appear on the screen.
Step 4. Switch to the Error Alert tab.
Step 5. After that, check the box “Show error alert after invalid data is entered.”
Step 6. Next, select a Style of error message from Stop, Warning, or Information.
Step 7. Then, enter the relevant Title and Error message in the provided box.
Step 8. At last, 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
Microsoft Excel’s Data Validation feature is a potent tool that provides spreadsheet creators the power to ensure data accuracy, enhance user experience, and control the type of input entered into cells. Through the simple steps outlined above, users can create drop-down lists, manually enter data, display custom messages, and even show error messages for invalid entries. With this functionality, Excel offers a user-friendly and efficient way to manage data entry, minimizing errors and ensuring consistency.
