Conditional Dropdown List in Excel – Learn How to Create!

[soniya55531]

Learn how to create a conditional drop-down list in Excel, step by step, quickly and easily!

If you prefer this content in video lesson format, watch the video below or access our YouTube channel !


To receive the file(s) used in the class by email, fill in :

Your best email
To send
Excel Icon
Excel
Impressive
Everything you need to know, from Basic to Advanced, to master the most important tool in the Job Market. Learn the main Excel tools, functions and resources to make any spreadsheet more eye-catching and efficient.

Start now
Right arrow
Excel icon used as backgroundThree images of Excel course classes

Conditional Dropdown List in Excel – Learn how to create it!
Do you know how to create a conditional drop-down list line data in Excel? In this lesson, I will show you how to do this process quickly and easily.

In today's lesson, I'll show you what a drop-down list and a conditional drop-down list are and how to create each of them using Excel. To do this, we'll cover the use of the Name Manager tool.

So, come with me, download the available material and learn how to create a conditional drop-down list in Excel.

Dropdown List in Excel – Data Validation
If you've never created a drop-down list in Excel, it's pretty easy. Just select the cell where you want to create the drop-down list , go to the Data tab , and click on Data Validation .

Data Validation
In the window that opens, choose the List option within the Allow box . Then, in the Source box , pass the range containing the states or type the names manually , separating them with semicolons.

Range with states
Name of the states
Either of these two approaches will create our dropdown list. Just click OK and it's ready to go.

Dropdown list
Note that even if there are repeated values ​​in the range of cells in the State column , when you create the drop-down list, these values ​​will only appear once. However, this functionality is only available in the latest versions of Excel .

In older versions, Excel does not remove duplicate values, so it is more advisable to use the option to manually write the values ​​within the Data Validation Source .

Conditional Dropdown List in Excel
A conditional drop-down list in Excel is simply a drop-down list that changes dynamically based on some specific condition. In this case, we will create a drop-down list for cities, conditional on the State list .

That is, the Cities list will display only the cities corresponding to the state selected in the State list .

To do this, we will use Excel's Name Manager . Simply select the set of cities for each state and, on the Formulas tab , click Name Manager .

Let's start by doing this for the cities related to Rio de Janeiro first.

cities related to Rio de Janeiro
In the window that opens, click on New and set the name for this selected range as RJ.

Name Manager
Creating Name Manager
Once this is done, we will repeat the same procedure for SP , MG and ES .

Name manager created for each state
With the named ranges according to each state, we can create our conditional drop-down list. Let's select the cell next to City and create a data validation of the List type, passing the formula INDIRECT as the source for it .

This formula will receive as an argument the cell where our drop-down list of states is located.