Excel is renowned for its robust data management capabilities. A pivotal feature that enhances Excel’s usability is the ability to create a drop down list in Excel. This tool simplifies data entry, reduces errors, and ensures consistency. In this post, we’ll guide you step-by-step on how to create a drop down list in Excel, using a practical example of managing staff allergies in a workplace.
Setting Up the Data
Before you create a drop down list in Excel, it’s crucial to have your data organised. To help understand the process we’ll use an example of creating a Staff Allergy List in Excel.
For background the Excel file contains two worksheets, ‘Staff’ and ‘References’. The ‘Staff’ worksheet contains a table with two columns: ‘Staff Name’ and ‘Known Allergies’:
Then we have a ‘References’ worksheet which lists the various allergies staff may have in cells A2 to A12:
Next we must determine what cells to apply the drop down list criteria against. In this example it is the Known Allergies column on the ‘Staff’ worksheet (cells C6 to C11). This is because the aim is to populate those cells with allergies from the list of allergies on the ‘References’ worksheet.
Apply Data Validation
The real magic to create a drop down list in Excel lies in data validation:
1. Select the Target Cells – Go to the ‘Staff’ worksheet. Highlight cells C6 to C11 where you want the drop down lists.
2. Open Data Validation Settings – Click on the ‘Data’ tab, and then on ‘Data Validation’ in the ribbon. In the Data Validation dialog box, under the ‘Settings’ tab, choose ‘List’ in the ‘Allow’ dropdown.
3. Set the Source for the Drop Down List – In the ‘Source’ box, enter =References!$A$2:$A$12. This links your drop down list to the allergies list you created. Click ‘OK’ to apply the data validation.
Test the Excel Drop Down List
Once you’ve set up data validation, test your drop down list:
- Click on any cell in C6 to C11.
- A drop down arrow should appear on the right side of the cell.
- Click the arrow, and you should see the list of allergies from your ‘References’ worksheet.
Summary
By following these steps, you’ve learned how to create a drop down list in Excel, a skill that streamlines data input and enhances accuracy. Drop down lists are invaluable for maintaining data integrity in any Excel-based project, from managing staff information to organizing inventory.
Keep Excelling,
Eager to master selecting and displaying multiple items in a single Excel cell using a dropdown list? Discover our step-by-step guide on How to Create a Dropdown List in Excel with Multiple Selections, and transform the way you manage data with ease!
