Reset a Dependent Drop-Down List in Excel When the Parent Changes
Question details
Automatically clear a second-level drop-down selection when the first drop-down changes.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses a parent data-validation drop-down and a dependent drop-down whose available choices vary with the parent selection.
- Observed behavior
- The dependent cell should be cleared when the parent selection changes so the user can choose a value from the updated list.
Identify the parent and dependent cells and confirm that the dependent cell already has a data-validation list linked to the parent. Use desktop Excel with macros enabled; Excel for the web cannot run VBA macros.
Clear the Dependent Cell With a Worksheet Change Macro
Use a worksheet event to clear the old selection as soon as the parent cell is edited.
This example uses B2 as the parent drop-down and C2 as the dependent drop-down. Change both cell references to match your worksheet. Clearing the contents leaves C2's data-validation rule in place.
The event responds to edits to B2, including selections and pasted values. It does not fire when a formula in B2 changes only because of recalculation.
In desktop Excel, right-click the sheet tab containing the drop-downs and select View Code.
Paste this code into the worksheet code window: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B2")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False Me.Range("C2").ClearContents CleanUp: Application.EnableEvents = True End Sub
Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Reopen the workbook and enable macros if prompted and you trust the file.
Choose a value in B2, select an option in C2, and then change B2. Confirm that C2 clears and that its drop-down offers the options for the new parent value.

Work With Spreadsheet Drop-Downs in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working with Excel-format spreadsheets. Open your existing files in WPS Spreadsheets for an easy transition, but verify VBA and macro support for your WPS edition before relying on the Excel event code.

Frequently Asked Questions
Will clearing the dependent cell remove its drop-down?
No. The macro uses ClearContents, which removes the selected value but leaves the data-validation rule in place. Check the rule under Data > Data Validation.
Can a formula clear the old dependent selection automatically?
A formula can change the options available in a dependent list, but it does not automatically erase an existing value in the input cell. A VBA event is a typical way to clear that value after a parent edit.
Why does the dependent list still show the wrong options?
Select the dependent cell and open Data > Data Validation. Check that its list source refers to the correct parent cell and that any named ranges or source formulas match the parent values.
Why does the macro not run in Excel for the web?
Excel for the web cannot run VBA macros. Open the workbook in desktop Excel, save it as an XLSM file, and enable macros if you trust the workbook.
Will the macro run when the parent cell changes because of a formula?
No. Worksheet_Change responds to edits, not changes caused only by formula recalculation. A different approach, such as a carefully designed Worksheet_Calculate event, is needed for a formula-driven parent cell.




