Often you may want to create a dropdown list in an Excel sheet using values from another sheet.

The following step-by-step example shows how to do so.

Step 1: Create List of Values

Suppose we would like a user to be able to enter data for various basketball teams in Sheet1 in Excel:

To ensure that the user only enters specific team names, we’ll specify the list of valid team names in Sheet2:

Step 2: Create Name for Dropdown List

We’ll then highlight the cell range A1:A7 in Sheet2 and right click.

Click Define Name from the dropdown menu, then type teams in the Name box. Then click OK:

Step 3: Use Data Validation to Create Dropdown List

Suppose we would like a user to be able to enter one of the team names from the list in Sheet2 within the range A2:A10 in Sheet1.

We can highlight the range A2:A10 in Sheet1, then click the Data Validation icon within the Data Tools group in the Data tab along the top ribbon:

In the new window that appears, click the dropdown arrow next to Allow and choose List.

Then type =teams in the Source box:

Lastly, click OK.

Now, when you click on any of the cells in the range A2:A10, a dropdown arrow will appear where the user can choose from one of the team names in the list from Sheet2:

This ensures that only the seven team names in Sheet2 can be entered into column A.

If a user attempts to manually type in a different team name that is not in the list, Excel will produce an error message:

The following tutorials explain how to perform other common tasks in Excel: