Create an Excel dropdown and test invalid entries

A dropdown standardizes entries such as “Open”, “In Progress” and “Done”. This guide uses Excel for Microsoft 365 on Windows and a fixed named source range. Data validation assists entry; it is not complete protection against incorrect data.

Prepare a working copy and source list

First save a copy of the workbook. Add a sheet named “Lists”, put “Status” in A1 and enter “Open”, “In Progress” and “Done” in A2 through A4, one per cell. Avoid blank rows, duplicates and unintended spaces.

Select A2:A4 only, excluding the heading. Choose Formulas → Define Name, enter StatusList with Workbook scope and check the reference =Lists!$A$2:$A$4. Do not inadvertently overwrite an existing name.

Set up the input-cell dropdown

  1. Select the intended input cells on the data sheet, for example B2:B20. Make sure they are not formula cells.
  2. Choose Data → Data Validation. Under Settings, set Allow to List.
  3. Enter =StatusList as Source and enable In-cell dropdown.
  4. Decide how to handle blanks. Ignore blank is not reliable mandatory-field enforcement against every deletion or paste.
  5. On Error Alert, enable the alert for invalid entries and select Stop. Add a clear message naming the three permitted values. Warning and Information allow a different entry after confirmation.
  6. Select OK and adjust the column width so the choices remain readable.

Test the rule with counterexamples

Pick “In Progress” in a test cell, then directly type a value absent from the list, such as “Unknown”. Stop must reject that direct entry. Also test blanks and several destination cells; a dropdown in B2 does not prove B20 has the same rule.

Applying the rule does not automatically clean up existing incorrect values. Use Data → Data Validation → Circle Invalid Data and review the marked entries substantively. Copying, pasting or filling can bypass ordinary input validation or overwrite rules. After imports, therefore check both the values and whether data validation still exists.

Maintain the source and save the result

This example uses a fixed named range. Adding an item in A5 does not automatically extend it: deliberately adjust the reference in Name Manager and test the selection again. Save the working copy, reopen it and retest the dropdown and Stop alert. If Data Validation is unavailable, finish editing the cell first and clarify possible sheet protection with its owner instead of bypassing protection.

Sources