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.
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.
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]
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]
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
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
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.
- Electronics
- 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.
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
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”
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 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
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.