Create a dropdown list in Excel with data validation
Compatible with Microsoft 365, Excel 2024/2021/2019/2016 Windows/Mac and web versions. Prepare a table of candidates, set the cell input rule to [List], and try selective input.
Published · Updated · FaultNote editorial policy
Who this guide is for and what to prepare
- People who want to unify the notation of status and person in charge
What you need
- Enter “Not started,” “Working on,” and “Completed” in A2:A4 on a separate sheet.
Prepare a list of candidates
Set A1 on the "candidate" sheet as status, set A2:A4 as unstarted/in progress/completed, select A1:A4, and select Ctrl+T. Arrange them in the order you want them displayed without any spaces.
- Enter suggestions in one column
- Create a table including headings
Specify a list with validation rules
Select the destination range B2:B20 and open [Data] > [Data Validation]. On [Settings], set [Allow] to [List], set [Source] to the candidate-data range, select [In-cell dropdown], and choose [OK].
- Select input destination
- Select list by input rules
- Specify candidate range excluding headings
Experiment with selection and error display
Select "In progress" from the arrow in B2 and confirm that only those characters are included. If the [Error Message] input rule is set to [Stop], if you directly enter "Hold", it will be rejected. If necessary, set a unique message such as "Please select from suggestions."
- Select one candidate
- Confirm rejection by entering a non-suggested word
change or delete
If you correct the value in the candidate table, it will be reflected in the reference destination. To remove only the validation rules, select the target cell and use [Data] > [Data Validation Rules] > [Clear All] > [OK]. Delete the cell contents separately if necessary.
- Modify the candidate table
- Clear all to remove input rules
Limitations and requirements
- Validation rules cannot be changed on protected sheets or in some sharing states
Frequently asked questions
I don't see any arrows.
Check that the input rule "Select from drop-down list" is selected and that the cell is editable.
Even if you add a candidate, it will not be reflected.
It may refer to a fixed range. Add suggestions to the next row in the table or expand the range of original values.
Official sources and verification date
Sources checked: . Check the official sources below for changes to supported systems, plans and menus.