Create Excel Data Validation Drop Down List

How to Create Appealing Excel Data Validation Drop Down List

If you haven’t used excel data validation drop down list in your reports then you are mostly missing something “really big”.

The combination of well-structured drop down list and formulas like Indirect, Sumifs & Vlookup are the deadly combinations for Excel dashboards.

So, in this step by step tutorial, I’m going to cover how to create drop down list in excel.

Create Excel Data Validation Drop Down List

We will also cover some of the advanced techniques to create data validation list multiple selections using Indirect formula.

Let’s get started.

 

What is Data Validation in Excel?

In simple terms, data validation is a set of pre-defined rules to have correct data in correct place.

“Data validation is the process of ensuring that a program operates on clean, correct and useful data – Wikipedia 

In excel we use data validation for numerous reasons.

Say, for example, to enter repetitive text in a column, or to build financial models with various criteria’s

(like Monthly, Quarterly Half yearly, Region, Country etc..)

 

How to create a drop down list in excel

Step1: Select a cell or a range where you want to add data validation list

Step2: Go to “Data” tab & then click on”Data Validation” option.

How to add Data Validation in excel

Once you click on Data validation you will have prompt with various options to select.

Step3: In Settings tab from the first dropdown select “List”

Step4: Next enter your data for drop-down separated by coma & click “ok”

[I’ve added Months for my example, where user can select only from the dropdown]

 Data validation options window with complete steps

That’s all…

You just created a basic dropdown list.

Also Read: 51 Best excel tricks 

How to add Input Messages & Error alerts to your list:

Input Message:

On the same data validation window, select → Input Message Tab → make sure to enable first check box like below.

Enter your title [short descriptive text]

Enter detailed message [should be able to describe the purpose]

input message in data excel data validation

Error Alerts:

On the same window, select Error Alert tab & make sure the first checkbox is selected.

You can choose alert icon → enter title & short description

Then click ok.

 

How to add data validation list from a range/ named range

Of course, it’s not always feasible to add your dropdown data manually in the source box. In future, if you decided to change/update something in your list, it will be a headache.

So instead, you can link to a cell range so that it will be very easy to update.

Step1: Select a cell where you want to insert data validation

Step2: Enter all your list values in a separate column

Step3: Click on Data Validation → Settings → Select “List”

Step4: On the source, → click on range selection button & select your list range & click ok

create data validation list from range

 

Using named range:

Sometimes it’s easy to use named ranges. They are easy to recognize and easy to manipulate.

To use named ranges in your excel data validation list you can follow below simple steps:

Step1: Select your lookup values cell range

Step2: Head over to formulas → Click on “Define Name”

Step3: Under name box  → enter your desired name [in my case I’ve added “lookup_range”]

Step4: Scope: Workbook → Comment: Descriptive text for easy recognization → Click ok

Data validation using named range

Step5: Go back to Data validation window → under Source enter your range name like below

=lookup_range

Also read: 10 reasons for Excel formulas not working 

How to create Conditional Drop down list in excel

So far you have learned how to create basic excel data validation drop down list, but in this section let’s understand how to create a dynamic list using Indirect formula.

In our Example, we have two main categories.

  1. Electronics
  2. Books

When the user selects Electronics in our first drop down the second drop down should show only subcategories related electronics like Mobile Phones, Laptops etc..

Similarly when the user selects Books then the second one should show the only subcategory like Fiction, Romance, non-fiction etc..

Arrange Lookup tables:

Step1: Arrange lookup data like below. Your subcategory headings should be same as your main category.

Dynamic lookup table for data validation

Seep2: Select both the cells in Shop Catagory and name it as “Shop_Category”

Step3: Similarly select all the items under Electronics and name it as “Electronics” & same goes with Books.

Step4: Select cell B1 and click on Data validation → List → just provide the first category named range [Shop_Category] and click ok

You just added first drop down, based on that we need to add the second one

Step5: Select B2 → Click on Data validation → List → Under Source → you need to link first validation cell i.e. B1 using Indirect formula like below

How to use indirect formula for data validation

That’s it, now if you select Electronics from the first category, you will see only sub-items related to electronics as defined in our lookup table.

Same goes with Books as well.

 

Things to remember:

The method I mentioned above has some limitations:

  • Your named range name should not have any blank space. For example, you can’t define your range as “Mobile Phones” instead you should name it as “Mobile_Phones”
  • The other limitation is, you can’t define name as FY15, FY16, Q116, Q216 etc.. because Excel will consider these as a normal range

 

How to find Data Validation cells in Excel

There is an easy way to find all the data validation cells in excel.

Under Home tab → Click on Find & Select → Select Data Validation.

Once you do that, you will be able to see all the validation cells selected.

 

How to remove Data Validation in excel

This is awesome..!

But, if you have to remove Data Validation from a cell/range how do you do that?

It’s pretty simple.

Just select the cells [range] where you want to remove data validation.

Click on → Data Validation → Under settings tab → under Allow drop down → Select “Any Value”

how to remove data validation in excel

The other simple way is by copying and pasting a normal blank cell in a drop down list range. If you do so, Data Validation formats will be overwritten by blank cell formatting.

 

How to create Excel Data Validation Drop Down List using Form control Combo Box

Other than data validation, you can also use Form control Combo box for a professional look.

But you need to enable developer tab to use form controls, you can refer this tutorial on how to enable developer tab.

Step 1:

Once you have it head over to → Developer tab →Insert → from Form controls click on Combo Box → and insert using a mouse.

Step 2:

Add your lookup data in a row.

Step 3:

Right click ok Combo Box that you just added and select “Format Control”

Step 4:

Under Control tab → Select input range & Select Cell link → click Ok

how to add combo box drop dow lint in excel

How to use Combo box data:

Unlike excel data validation drop down list, the combo box is not a part of any cell so you cannot directly link your formulas to refer Combo box result.

It’s a separate form so we have to do some workaround to use it for our requirements.

Step1: once you have Combo box setup, select a value from dropdown list.

Step2: You will see a serial number in a cell link that you had provided while sitting up.

Step3: Go back to your lookup data cell and add serial numbers to your lookup items

Step4: Somewhere close to your form just do a vlookup using the serial numbers

Step5: Use the V lookup cell in your formulas

Combo box drop down in excel

I know this method is not direct, but sometimes it’s better to use Combo box form in professional-looking dashboards instead of data validation.

Conclusion

Creating excel data validation drop down list in excel is very easy and effective. Especially if you are working on dashboards then it’s a must have skill.

Have you used date validations lists before? if so how effective they are with multiple criteria’s.

Join our Newsletter