Excel Dropdown Lists Tutorial

In Excel, a user has the ability to customize columns and rows of cells through unique dropdown menus. In dropdown lists, you can access many data choices for output within a cell. The Excel dropdown lists tutorial will show you how achieve this.

Download the tutorial and follow along.

In the example, you will learn how to easily assign an employee to a department using the creation of dropdown lists.

Creating a dropdown list in Excel

Choose the “List of Departments” in the example you downloaded and drag your selection to have A9 through A13 highlighted together. Right click and select “Define Name” from the list that appears.

Excel Dropdown Lists

A new box titled “New Name” will now show. Give the list title a descriptive name to identify it. We chose “Departments”.

Excel Dropdown Lists

Now you have made a list of the departments for the spreadsheet. All that is needed now is to tell Excel where to insert the list.

To do this, highlight cells C3 through C6.

Choose the “Data” tab in the top Ribbon bar and click “Data Validation”. From the dropdown list that appears, pick the “Data Validation” option.

Excel Dropdown Lists

In the Data Validation dialog box that comes up, make sure you’re on the “Settings” tab. In the dropdown under “Allow”, choose “List”. More options will then appear. Choose the text box located under “Source”:

Excel Dropdown Lists

Enter in “=Departments” (without quotations) or the name you made earlier for the list.

If you want to customize messages for the dropdown list, there are two tabs in the dialog called  “Input Message” and “Error Alert” for you to take advantage of.

When you’re done, press enter.

Under “Employee Information”, go to the empty “Department” cell next to an employee name and choose that empty cell. A dropdown arrow will show the options for each department that you can assign to that employee. This concludes the dropdown lists tutorial.

Related Templates:

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.