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.
A new box titled “New Name” will now show. Give the list title a descriptive name to identify it. We chose “Departments”.
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.
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”:
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:
- Macros
- Preventing Duplicates in Excel
- Excel Autocorrect Guide
- Highlight Rows in Excel
- How to Use the COUNTIF Function in Excel
View this offer while you wait!