Excel Dependent Drop Down Lists - Block Changes

One of my favourite Excel features is a data validation drop down list. In just a couple of minutes, you can make a list of items, then make that list appear in a worksheet cell. It’s like magic!

The drop down lists make it much easier to enter data, and they help prevent typos, or invalid entries.

The steps for making a drop down list, using data validation, are on my Contextures site.

Dependent Drop Down Lists

After you’ve made a few drop down lists, you can take it a bit further – create a dependent dropdown list!

Choose an item from the first drop down list (fruit or vegetable)
Then, in the next column a dependent drop down list appears.
- The list is based on your first selection

For example, in the screen shot below

Vegetable is selected in cell B4
The dependent drop down in cell C4 shows a list with 3 vegetables.

Video: Make Dependent Drop Down Lists

In this short video, I show how to set up a main drop-down list, with a dependent drop-down list in the next column.

The written instructions are on the Dependent Drop-Down Lists page.

Dependent Drop Down Problem

There’s a potential problem with dependent drop down lists though.

After someone selects an item from a dependent drop down, they could go back to the main drop down, and select a different item.
That could result in mismatched items in that row.

For example, Fruit could be selected from the main drop down, and then Lemon in the dependent drop down.

If Fruit is changed to Vegetable later, the Lemon selection would be incorrect — it’s not a vegetable!

Video: Block Dependent Drop Down Changes

To avoid those mismatch problems, you can modify the data validation rule in the first drop down cells. For example:

Use the IF function to check for an empty dependent cell.
If the cell is not empty, don’t show the main drop down list

In this short video, I show how to block people from changing the first drop down list, if there’s a selection in the second drop down.

There are written steps, and a sample file on the Advanced Dependent Drop Down Lists page, on my Contextures site.

Get the Sample File

There are written steps for the block changes technique, and a sample file, on the Advanced Dependent Drop Down Lists page, on my Contextures site. The zipped Excel file is in xlsx format, and does not contain macros.
For a different way to prevent problems, there’s also a macro on that page that will clear the dependent cell, any time you select an item from the main drop down. The zipped sample file for that technique is in xlsm format, and does contain a macro.