Office Settings & Configuration

Refresh the Displayed Selection in an Excel Dependent Drop-Down

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

The user wants a dependent drop-down to display the first item in its refreshed source list when the parent selection changes.

Refresh the Displayed Selection in an Excel Dependent Drop-Down
Product
Microsoft Excel
Device & OS
not provided
Scenario
A data-validation list depends on another selection, and the user wants a valid default displayed after its source changes.
Observed behavior
The drop-down list reflects the updated source when opened, but the previously selected cell value remains visible.
Before you start

Identify the dependent drop-down cell and the range that supplies its current list. Check that the source range updates when you change the parent selection and that its first cell contains the desired default.

Solution 1Recommended

Display a valid selection or default in a helper cell

Use a formula-driven display cell when you need a valid result without VBA and can keep it separate from the editable drop-down.

Data validation does not overwrite an existing selection when the source list changes. The helper formula displays a result but does not change the stored value in the drop-down cell.

1
Identify the cells

For this example, use B2 as the dependent drop-down and D2:D10 as its current source range. Make sure D2 contains the first available item.

2
Enter the helper formula

In an empty cell such as C2, enter =IF(AND(B2<>"",COUNTIF(D2:D10,B2)>0),B2,IF(D2="","",D2)). Adjust the references to match your worksheet.

3
Use the helper result

Refer to C2 in downstream formulas or display it as the effective selection. It keeps the selection from B2 if it is still in the source range; otherwise, it displays D2.

4
Test the change

Change the parent selection, then check that D2:D10 updates and C2 shows either the valid existing selection or the new first item.

Display a valid selection or default in a helper cell
The drop-down cell remains unchanged: B2 may still contain the old value. If the drop-down cell itself must be replaced automatically, that generally requires VBA.
Work with dependent lists in WPS

Display a valid dependent-list selection in WPS Spreadsheet

Use a data-validation list and a separate helper formula in WPS Spreadsheet to display the existing selection when valid or the first source item when it is not.

  1. 1. Open the workbook: Open your workbook in WPS Spreadsheet and identify the dependent drop-down cell and its updating source range.
  2. 2. Check the list: Select the dependent cell and open Data > Data Validation to check that it uses the intended list source.
  3. 3. Add a helper formula: In an empty cell, enter =IF(AND(B2<>"",COUNTIF(D2:D10,B2)>0),B2,IF(D2="","",D2)), replacing B2 and D2:D10 with your drop-down cell and source range.
  4. 4. Test the display: Change the parent selection and check the helper cell. Use that cell in subsequent calculations; the original drop-down value is not automatically overwritten.
Display a valid dependent-list result without a macroOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for formulas and data validationWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel keep the old value after a dependent list changes?

Data validation controls the choices offered in the drop-down, but it does not automatically replace a value already stored in the cell when the source changes.

Can a formula replace the value in the drop-down cell itself?

A separate helper cell can calculate a valid display value, but it cannot overwrite the editable drop-down cell. Automatically replacing that cell generally requires VBA.

How can I always display the first source item?

If D2 is the first cell of the current source range, enter =IF(D2="","",D2) in a separate display cell. Update the reference to match your source.

What should I check if the helper shows the wrong default?

Check that the formula references the current source range and that its first cell contains the intended default. Change the parent selection and verify that the source range recalculates.