How to create a drop-down list in Excel using Data Validation

In Microsoft Excel, users can include various Active X controls in their spreadsheets, such as the Command button, Spin button, Scroll button, etc, which is available on the Developer tab, but what if you want to create a drop-down list within your spreadsheet? To create a drop-down list in Excel, you must use the Data Validation feature. 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.

How to create a drop-down list in Excel using Data Validation

Follow the steps below to create a drop-down list using data validation in Excel.

1. Launch Microsoft Excel.
2. Add data to the spreadsheet.
3. Click the Data tab and select Data Validation.
4. Under Allow, click the drop-down arrow and click List from the drop-down list.
5. Click the Source button.
6. Click where you want to copy the source from to make the drop-down list
7. Click the arrow. Then click Ok.
8. Now we have a drop-down list created by Data Validation.

Launch Excel.

Add data into the spreadsheet. In this tutorial, we have added data to worksheets one and two.

Select where you want the drop-down list to appear.

Click the Data tab and select Data Validation in the Data Tools group.

A Data Validation dialog box will appear.

Under Allow, click the drop-down arrow and select List from the drop-down list.

Now click the Source button.

Highlight  where you want to copy the source to make the drop-down list.

Click the arrow. Then click Ok for the dialog box.

Now we have a drop-down list created by Data Validation.

If you want to add a message pop up, you can click the Input Message tab and check the checkbox for “Show input message when cell is selected.”

Add a Title and input a message in the Input Message box.

Then click OK.

If you want a message to pop up when someone tries to enter something that is not in your drop-down list, click the Error Alert tab.

Ensure that the checkbox for the “Show error alert after invalid data is entered.”

Pick an option from the style box.

Give the message a title and type in the message in the Error message box.

Then click OK.

We hope you understand how to create a drop-down list using Data Validation in Excel.

How does Data Validation work in Excel?

Data Validation in Microsoft Excel allows users to control the type of data entered into the worksheet. It picks from a list of rules to limit the type of data that can be entered into a cell. For example, you can provide a list of values like 1, 2, and 3, or only allows number allow numbers greater than 1000 as valid entries.

What are the 3 styles of data validation?

The Data Validation feature allows users to control what a user can type into a cell.

1. Stop
2. Warning
3. Information.

READ: How to add Bullets to Text Box in Excel

Why do we use data validation?

Data Validation is used to provide accuracy and eliminates errors from our data. It helps users input values such as list, date, time, etc into our spreadsheets. Data Validation also inputs messages and Error Alerts as a part of the values you have entered.

READ: Cannot create List in Excel: The file does not exist.