Welcome To Excel Avon
Drop Down List in Excel
You can download excel file from here>>
In this post we are Talking about Drop Down Lists, Drop Down List is mainly used in Data Entry organization like medical transcription & data dashboard. By the way, there are three types of drop down lists and today we will explain all three, so let’s understand through example.
For using Drop Down Lists open excel sheet and
Data>Data Validation
Type of Drop Down list
There are three type of drop down
1. Comma Separated Dropdown List
2. Fixed Range Dropdown List
3. Dynamic Range Dropdown List
1. Comma Separated Dropdown List
The comma-separated drop-down list is one that you manually defined using commas to separate the list variables under. You can see the image below for better generalization. As you can see, we have made some examples in which the formula will be applied.
By using this formula, the work will become a little easier, you will not need to write any value again and again. First of all we will go to data validation by selecting the cell and then setting>validation criteria> change for allow and then write your source.
press ok and you will start getting options in the cell, you just click twice, you will be able to choose that number, yes, there can be any name on the bar or yes/no options for any question yes/no options for any question
.
2. Fixed Range Dropdown List
Fixed range Drop down list is used for fixed range say the month of the year or the dates of any month. It remains in fixed range only, to use it, a fixed range is written in the data validation source.
Source =$A$3:$A$14
Fixed range can be used in the exam of multiple questions, multiple answers will be made fixed range
3.Dynamic Range Dropdown List in Excel
A drop down list is a powerful tool. It can help you to make your data entry simple and quick. You can use a drop down list with your dashboards, charts or even with normal data. In dynamic drop down we will use two formula offset and counta.
So now we will use the formula
=OFFSET($A$3,,,COUNTA($A:$A)-2,1)
- COUNTA(A.A) is getting lenth of range.
- ‘2’ is subtracting (NO. of header row)
after Applying formula press ‘okay’. Now you can use Dynamic Range Dropdown
You can download used excel file from here>>
If you add more data in to column (A), No changes will required to the formula. After adding data in column(A), you can see.
More Info about offset formula
So I hope you have understood this formula and for more information you can follow us on Twitter, Instagram, LinkedIn and YouTube as well.
You can also see well explained video here
