Create a dropdown list in Excel with data validation

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.

Separate status entries from their source list

For a progress sheet with status in column D, place three choices on a separate source sheet. Configure D2 first. Exclude headers and unused cells from the source. If direct cross-sheet entry is unsuitable for the dialog, consider a named source range.

Once D2 works, apply the same validation to subsequent rows. When using paste operations, choose validation-only where appropriate to avoid replacing borders and colors. Inspect the final row and a newly added row, not just the first cell.

Renaming Complete to Resolved requires reviewing existing entries separately from editing the source list. Otherwise summaries can split old and new labels. Filter for the old value, confirm the affected count and limit replacement to the status column. Decide who maintains shared source choices.

Give choices distinct meanings and document when to use each. Put free-form explanations in a separate notes column rather than allowing ambiguous status labels.

For workflows involving pasted data, review existing values as well as validation settings. Filter for unlisted values and blanks, deciding whether blank status is permitted.

1. Prepare a source range without the heading
2. Test the first destination cell
3. Apply validation to the required range
4. Check new rows and retired-choice counts

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.

1. Enter suggestions in one column
2. 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].

1. Select input destination
2. Select list by input rules
3. 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."

1. Select one candidate
2. 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.

1. Modify the candidate table
2. Clear all to remove input rules

Test a new choice and existing entries

For a list such as Not started, In progress and Complete, add On hold on a copy and check the dropdown. Extend a fixed source range if necessary. An Excel table makes maintaining the source choices easier.

Removing In progress from the source should not be treated as a migration of existing entries. Filter the destination column for that old value and decide whether to update those records or restore the choice.

1. Add one choice and inspect the destination cell
2. Check the end of the source range
3. Review rows containing retired choices

Choose the error alert deliberately

Choose a Stop error alert when typing an unlisted value should be rejected. Warning and Information alerts allow different behavior. Test by typing a value that is absent from the list.

If the arrow is missing, check that the affected cell has List validation and that the in-cell dropdown is enabled. After applying validation to the full range, inspect its beginning, middle and end.

1. Test the alert with an unlisted typed value
2. Inspect validation on one cell
3. Open the dropdown at three points in the range

Choose the next action from the result

Limitations and requirements

• Validation rules cannot be changed on protected sheets or in some sharing states

Frequently asked questions

Sources and verification date

Sources checked: 2026-10-13. These sources support the specifications, procedures or prices discussed here. Check each source for applicable conditions and current information.

• Create a drop-down list | Microsoft Support ↗
• More on data validation | Microsoft Support ↗

Related practical guides

• Export One Excel Sheet to PDF with All Columns on One Page Width
• Convert an Excel data range to a table
• Delete duplicate rows based on specified columns in Excel
• Freeze header row and leftmost column at the same time in Excel
• Import UTF-8 CSV in Excel without garbled characters
• The recovery drive to make on day one: what it holds, what it does not, and which USB stick to use

Microsoft Excel troubleshooting

• Excel file locked for editing: check the owner, location and app

How-to guides: browse all guides →