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:
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to AutoFill Using a Custom List
Excel: How to Highlight Cell if Value Exists in List
Excel: Search for Value in List and Return Yes or No
