Using Dropdown Lists in Excel

Using Dropdown Lists in Excel

Excel has the ability to customize rows and columns through dropdown menus. With Dropdown lists, you have access to many different data choices for output within a cell.

Download the Dropdown List example page to follow along.

In our example, we will show how to easily assign an employee to a department using dropdown lists.

How to create a dropdown list in Excel

Select the “List of Departments” in the example and drag to have A9 through A13 highlighted together. Then right click and choose “Define Name” from the list that appears.

Using Dropdown Lists in Excel

A new box called “New Name” will appear, give the list title a descriptive name like Departments.

Using Dropdown Lists in Excel

Press Enter. Now you have created a list of the departments in Excel. From here, all that is needed is to tell Excel where to insert the list in the rest of the spreadsheet.

Start by highlighting cells C3 through C6.

Click the “Data” tab in the top Ribbon bar and select “Data Validation”. From the dropdown list, pick the “Data Validation” option.

Using Dropdown Lists in Excel

In the Data Validation dialog box, make sure you’re on the “Settings” tab. In the dropdown under “Allow”, select List. More options will then appear in the dialog box. Click the text box located under “Source”.

Using Dropdown Lists in Excel

Type in “=Departments” (without quotations) or the name you came up with earlier for the list.

If you wish to customize messages for the dropdown, there are two tabs in the dialog called  “Input Message” and “Error Alert” for you to use.

When you’re finished, press enter.

Under “Employee Information”, go to the empty “Department” cell next to an employee’s name and click the empty cell. A dropdown arrow will appear and show the options for each department to assign to that employee. You’re finished!


Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.