Data Validation is a feature in Excel that allows users to pick from a list of rules to limit the type of data that can be entered into a cell. You need to use the Data Validation feature to create a drop-down list in Microsoft Excel.
In Microsoft Excel, users can include various Active X controls in their spreadsheets, such as the Command button, Spin button, Scroll button, etc., available on the Developer tab, but what if you want to create a drop-down list within your spreadsheet? You must use the Data Validation feature to create a drop-down list in Excel.
This risewindows article will show you how to create a drop-down list using Data Validation.
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
Step 5. Next, a Data Validation dialog box will appear on the screen.
Step 6. Then, switch to the
Step 7. Now, use the drop-down under Allow and choose the
Step 8. After that, click the
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
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 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
Step 3. Then, a Data Validation dialog box will appear on the screen.
Step 4. After that, switch to the
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
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
Step 3. Then, a Data Validation dialog box will appear on the screen.
Step 4. After that, switch to the
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
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
Step 3. Then, a Data Validation dialog box will appear on the screen.
Step 4. Switch to the
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
Once you have set the error message, it will appear the next time you enter invalid data in the drop-down cell.
That’s it for the article.
I hope you understand how to create a drop-down list using Data Validation in Excel.
Good luck & stay healthy, guys.
