Add All Employee Names to an Excel Data Validation Drop-Down List
Question details
The user needs monthly absence schedule drop-down lists to show all employee names instead of only the first five names.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Employee names are stored on an Employees worksheet and need to be available in absence schedule data validation lists.
- Observed behavior
- Only the first five employee names appear in the drop-down lists, although 20 names exist on the Employees sheet. The Source field is not visible until the validation type is set to List.
Locate the full employee-name range on the Employees sheet, such as cells A2:A21. Check whether the schedule template uses a named range or an Excel table, because its source may need to be expanded instead of edited directly.
Update the Excel Data Validation source to include every employee
Use this method when the absence schedule cells use a standard Data Validation list with an incomplete source range.
The Source box appears only after you select List in the Allow menu. Set it to the complete range containing employee names so each schedule drop-down can display all available employees.
Open the monthly absence schedule and select the cells that should contain employee names.
On the Excel ribbon, select Data > Data Validation.
In the Settings tab, choose List from the Allow drop-down menu. The Source field will then appear.
In Source, enter or select the full employee-name range, such as =Employees!$A$2:$A$21, then select OK.
Select a schedule cell and open its drop-down arrow to confirm that all employee names are available.
Expand the named range or table used by the employee drop-down
Use this method when the template source is a named range or Excel table rather than a fixed cell reference.
Create an Employee Drop-Down List in WPS Spreadsheets
WPS Spreadsheets lets you build and maintain employee selection lists for attendance and absence schedules using Data Validation. Select the schedule cells, choose a list source from the Employees sheet, and extend the source range whenever new employees are added.
- 1. Prepare the employee list: In WPS Spreadsheets, place employee names in one column on an Employees worksheet, for example A2:A21.
- 2. Select schedule cells: Open the absence schedule and select the cells where users should choose an employee.
- 3. Open Data Validation: Go to Data > Data Validation and choose List as the validation type.
- 4. Set the list source: Use the source selector to choose the complete employee-name range on the Employees sheet, then confirm the rule.
- 5. Check the drop-down entries: Click a validated schedule cell and open its drop-down arrow to make sure all employee names are shown.
Frequently Asked Questions
Why does my Excel employee drop-down show only five names?
Its Data Validation Source likely points to only the first five cells. Select a drop-down cell, go to Data > Data Validation, choose List under Allow, and extend the Source range to include every employee.
Where is the Source box in Excel Data Validation?
Open Data > Data Validation and select List from the Allow menu in the Settings tab. Excel displays the Source box only for list-based validation.
How do I add a new employee to every absence schedule drop-down?
Add the employee to the Employees sheet, then expand the range used in Data > Data Validation. If the source is a named range, use Formulas > Name Manager; if it is a table, use Table Design > Resize Table.
Can an Excel drop-down list use a named range for employee names?
Yes. Create or update a named range through Formulas > Name Manager, then use that name in the Data Validation Source field, such as =EmployeeNames.
