Microsoft Excel · Data preparation · Beginner

Create a Data Validation Drop-Down List in Excel

Build input dropdowns and validation checks in Excel with a complete practice example, expected results and recovery checks.

Official documentation reviewed; example logic independently checked; native Excel execution not performed · Editorial standards

Quick answer: Use Data → Data Validation with Allow set to List and an explicit source range. Add a Stop error alert if unlisted direct entries should be rejected.

Version and platform: Microsoft 365 Excel for Windows desktop; Mac, Excel for the web and Excel 2013 interfaces not covered. Use a disposable workbook for the exercise.

In a blank worksheet, enter Status choices in D1, then New, In progress, Blocked and Done in D2:D5. Put Task in A1 and Status in B1; enter Draft, Review and Publish in A2:A4. Leave B2:B4 empty for the dropdowns. This ordinary fixed range is intentionally simpler than a table-based source.

Create the drop-down

Select B2:B4, the cells that should contain the status.
Choose Data → Data Validation.
On Settings, set Allow to List.
Set Source to =$D$2:$D$5. Exclude D1 or the heading becomes an unwanted choice.
Keep In-cell dropdown enabled and confirm the rule.
Open the cell’s drop-down and choose each allowed value.

Using a source in an Excel Table can make maintenance easier because Microsoft documents that drop-downs based on a table can update as items are added or removed.

Input and error messages

The Data Validation dialog can show an input message when the cell is selected and an error alert when an invalid value is entered. Test an unlisted value such as Waiting so you know whether the chosen settings block it or merely warn.

Troubleshooting

Data Validation may be unavailable on a protected sheet or in some shared-workbook situations.
A manually typed source must use the appropriate list separator for the environment.
Do not hide the source range and then forget where it lives; document the source in a maintenance note or named range.

Reproducible maintenance test

The fixed address is easy to inspect later: select a target cell and reopen Data Validation to see which source cells supply its choices.

On Error Alert, enable the alert and choose Stop. Use a clear message such as “Choose a listed status.” Select New in B2 and Done in B3. Type Waiting directly in B4; the Stop alert should prevent acceptance of that unlisted entry. Cancel the invalid entry, then select Blocked instead. A Warning or Information alert is not equivalent to Stop because it permits an override.

Now change D4 from Blocked to Waiting. Reopen B4’s dropdown: Waiting should be available, but the old text already in B4 is not automatically rewritten. Choose Waiting explicitly. This separates maintenance of allowed choices from cleanup of existing records. Save as XLSX and reopen; inspect the source address and test a fresh direct entry. Treat validation as an entry aid, not a security or permission boundary, and review data after bulk pasting.

Official references

Create a drop-down list

Documentation snapshots retrieved 20 October 2026. These instructions and example checks are documentation-based; no native Excel run or application screenshot is claimed.