Excel Drop-Down List Not Updating? Check the Source Range and Validation Rule

Set up separate entry and source areas

Use desktop Excel for Windows and a new worksheet. Put the four sample tickets in A1:B5. In H1 type allowed_status, then put Open, In progress, and Closed in H2:H4. Keep those source cells separate from the entry area. The header must not become a selectable status.

Select B2:B20 as the intended input range. Empty future ticket rows are allowed in this demonstration. The controlled vocabulary is deliberately short so every choice can be inspected; it is not a dependent or multi-select dropdown.

Create a validation rule you can inspect

Open Data > Data Validation. On Settings select List and use =$H$2:$H$4 as Source. Enable In-cell dropdown. On Error Alert enable the alert and select Stop for invalid typed entries. Confirm the dialog and select B2 to see its choices.

The dollar signs keep the source addresses fixed. If the arrow is absent, select a cell inside the configured range and recheck In-cell dropdown. Protected sheets or other workbook restrictions may prevent rule editing; use an authorized editable copy rather than disabling controls blindly.

Include the new choice deliberately

Enter On hold in H5. Select the affected input range B2:B20 and extend Source to =$H$2:$H$5. Test B6 as well as B2 so you verify coverage of future entry rows. Checking only the first cell can miss a partly applied rule.

For a regularly changing vocabulary, an Excel Table can help maintain the source list. Confirm the actual validation source and test an appended option in your edition before relying on automatic expansion. The reproducible path here uses a fixed range and an explicit update.

Audit existing entries separately

Applying validation does not rewrite previously entered data. Put status_check in C1 and the helper formula in C2, then copy through C5. Waiting is not one of the four permitted values, so its row should say Review. Do not automatically replace it with On hold: the person responsible for that ticket must decide whether they mean the same thing.

COUNTIF is case-insensitive and interprets wildcard characters. This controlled list uses simple words without asterisks, question marks, or tildes. For identifiers with those characters, use an exact comparison method rather than treating this helper as a universal validator.

=IF(B2="","Blank",IF(COUNTIF($H$2:$H$5,B2)>0,"Allowed","Review"))

Test three kinds of input

Choose On hold from the dropdown in B6 and confirm it is retained. In B7 type an unrelated value such as Escalated; the Stop alert should reject that direct edit. Then inspect the original Waiting value in B5: it remains until a deliberate correction is made.

Copy and paste workflows can bypass or replace validation rules, so test the way your colleagues actually enter data. Recheck the rule after pasting and use an audit column before exporting. A dropdown is a data-entry aid, not an access-control or data-integrity boundary.

Save rules in a workbook

Keep the working template as XLSX. Export a CSV only when a receiving tool needs values; it will not carry dropdown controls. Check that the resulting status values still match the agreed vocabulary.

The downloadable XLSX has Before and After worksheets with the same four original records. Before applies $H$2:$H$4 to B2:B20; After expands that source to $H$2:$H$5. H5 contains On hold in both sheets, so the source boundary is the only rule difference. Existing Waiting remains in B5 and needs separate review.

Native desktop Excel 16.0 build 5569 opened both worksheets with the expected list source and Stop error-alert settings. Its validation engine accepted On hold on After, rejected it on Before, and flagged Escalated and the existing Waiting as invalid. Those are native rule evaluations after programmatic writes; the dropdown popup and typed-input Stop dialog were not exercised. Automated Range.Copy failed on this environment, so ordinary paste, paste-values and copied-validation behavior remain unverified. Test your actual entry method in a copy before relying on the rule.