You may have filled many online forms where you select a Country or State. You find different options for a country to choose one for yourself. The field of country name is a list and the pop-out option to select one of the countries is the pick list.
In this post, I will take you through all the essential aspects of a drop-down list (a pull-down menu) and provide examples that will assist in adding and managing a drop-down list quickly and easily for yourself.
While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.
The key topics which we will be covering here are:
What is Excel drop-down list
A drop-down list (a pull-down) gives a user the ability to select only one option from given predefined possibilities. A drop-down menu is a fantastic tool in Excel spreadsheet, by including a drop-down list we can simplify the process for an end user and make sure the data is accurate simultaneously.
Often we share our excel file with others and give absolute freedom to enter data. It happens very often that user makes misspellings or enter the right data in wrong places or vice versa.
Where is Excel drop-down list?
- Open an Excel workbook
- click Data tab
- Go to the Data Validation menu under the Data Tools group
- Click the adjacent Drop-Down
- select Data Validation
- Select Settings tab (Default) to input the list
- Go to Allow: and pick ‘List’ using from a drop-down list (Keep learning below)
Let’s dive down into Excel Drop-Down list by looking at various examples.
‘Yes’ and ‘No’ drop-down list in Excel
Suppose as an HR executive of an organization you are responsible for reviewing applicants resume. Typically there are three options to choose either you select a candidate for next round or reject or put them in the pending category for some good reason.
Let’s follow a simple example to understand ‘why we need a drop down list’.
Using the above scenario lets create a static drop-down list.
How to insert a drop-down list in Excel?
- Go to worksheet ‘Static’ of Excel working file (Image instructions below)
- Select a cell F5 to create a Drop-Down list
- Go to Data tab in the Excel ribbon.
- Click Data Validation, which pops out three options.
- Select the Settings tab (Default) to input the list
- Go to Allow: and pick ‘List’ using from a drop-down list
- Next is selecting Source: input box.
- Select cell H5 to H7 (=$H$5:$H$7) or manually enter “Yes,Pending,No”
- Press OK
- Copy C6 ( a drop down appears) and paste to F7:F10
When you click on cell F5, a list of three options will appear and we can pest the same in other cells of the list.
Try yourself to enter a different option other than the three you just enter in the dropdown list let’s say “Pass”, the Excel will return with a custom error message.
So the dropdown helps us to pick one of the options from a list to simplify our data entry process and reduces options of error through manual data entry.
A drop-down list makes sure that you are precise with ease.
- To make sure a drop down list is visible (a small pull down menu) ‘In-cell dropdown checkbox’ must be checked.
- Use keyboard shortcut Alt + DL for opening Data validation dialogue box directly.
- In drop-down list have option to select as many times as you wish but one at a time from the list.
Excel Drop-Down List with Dynamic List Range
By now you understand that to create a drop-down list and you need an actual list for reference. Following the above example will help in creating a list but a static one.
In the next Example, you will be working on creating Dynamic dropdown list.
What is Dynamic Drop-down list?
A dynamic drop-down automatically updates as the source list of drop-down changes. Dynamic Drop-down list does not just restrict invalid values but also provide greater freedom of managing list like addition and subtraction of an items list.
We will follow two methods to make sure that the Drop-Down list change and update automatically as the source reference changes by using an Excel table and name manager feature.
In the sheet “Dynamic” you will see a list under “Convert to Table”, a two-step process:
- Convert a list to table
- Apply Data Validation
To create ‘auto update’ drop-down list in Excel
First Step, a simple way is to convert the list into an Excel table by performing the following tasks:
- Go to worksheet ‘Dynamic’ of Excel working file (Image instructions below)
- Select a cell in range B5:B11 (Image instructions below)
- Press Ctrl+T to convert the list to a table
- Check “My table has headers” and press OK
- Click any cell of the table and then Table Tools Design tab
- Go to Table Name Box and write “Fruits” as a table name, press Enter
- Select B6:B12 without headings
- Now go to Formulas>Define Name
- Enter New Name as “Fruit” to name our list
- Press Ok
Check out further detailed analysis to understand the Excel Table and Excel Name Range
Now that we have set up our Table and List name, we can move towards the second step of the process to Integrate List Name with Drop Down box. The second step follows steps as mentioned in “How to create a drop-down list”.
Second Step, open the second worksheet “Dynamic” and then perform the following tasks:
- Select a cell D6 to create a Drop-Down list
- Select Data>Data Validation, which pop out a three tab dialogue box.
- Select the Settings tab to input the list (Default) manually or by selecting a cell range
- Go to Allow: and select ‘list’ using a pick from a drop-down list
- Next is selecting Source: input box
- Enter the “=fruit” list manually
- Press OK
Let’s apply changes in the list and make sure we have followed all steps successfully.
Add and Delete Drop-Down list
Get back to the fruit list of Drop-Down workbook to add another Fruit name in cell E12. I will call it “Pineapple”. If you go to the updated Drop-Down list, you will notice that Pineapple name appears as one of the options in the list.
The same can apply if you wish to add a Fruit name somewhere in the middle of the list. Same is true If we delete a Fruit name from the list.
- To convert List into Table does not require selecting the entire table range.
- You can view or edit the name of List and Table by going to Formulas>Name Manager.
Delete row to avoid the blank option in the list.
Keep Reading Similar Blog:
How Excel Names Range Work
Named Range- create, edit, delete and more about naming a range of cells and apply Name to new formula or existing formula quickly in Excel (Complete Guide).
Learn to Manage Data with Excel Table
Converting a list to an Excel table make data sets accessible to style, manage (add or delete row and column) and perform multiple data summary functions.
Dependent & Dynamic Drop-Down list
It is usual to see multiple picklists; one list is dependent upon the selection made in another list. In other words, a drop-down list depends upon the specific selection of a parent drop-down list.
Moreover, these dependent lists are also altered frequently to update the recent changes, so it becomes utmost important that the range of drop-down list is dynamic and adopt addition or subtractions in a list.
In the Example below, we have three product category (Mobile, Laptop and Camera) and related brands of each product category. The product category is the parent list, and the brand name is a secondary list.
So, If the user selects a product name from parent drop-down list simultaneously the brand names of the subordinate drop-down list must also change. 2nd Drop-down list is dependent upon the choice of 1st drop-down list.
To create a dependent Drop-Down list
The second drop-down list is dependent upon the choice of First drop-down list, the following are key steps to follow:
- Go to worksheet ‘Dependent’ of Excel working file (Image instructions below)
- Select a cell C5 to create a Drop-Down list
- Select Data>Data Validation>Settings>Source (Shortcut: Alt+DL)
- Enter =INDIRECT(B5), click OK, Ignore Error
- Select cell range (E4:G9)
- Go to Formulas tab> Create from Selection (Ctrl+Shift+F3)
- Check “Top Box” only (Create three list names)
- Select Mobile (E3 to E9) and Press Ctrl+T
- Apply same on Laptop and Camera
The INDIRECT function returns the contents of a cell by using its reference, For example, if cell B5 contains cell of the text value B8 (Or Name Range) and cell B8 contains the value 100, the formula =INDIRECT(B5) returns the value 100.
Now items in column C is guided by whatever is in column B, and the function called INDIRECT is the not so obvious choice.
We may find empty cells in a drop-down list as you see in ‘Camera’ to get rid of the blanks, perform the following tasks:
To Delete Empty Cells in Drop-Down list
- Go to worksheet ‘Dependent’ of Excel working file (Image instructions below)
- Select cell range (E3:G9)
- Go to Home tab
- Extreme Right button, Find & Select
- Go To Special > Choose Blank
- Press Ok
- Right-click on the highlighted blank cells
- Select ‘Delete’
- Check ‘Shift cells left.’
- Press Ok
Check the drop-down list, and the blank options or trailing options are not visible anymore.
Secondly, we can also create a formula to auto update the drop-down list by performing the following below steps:
Create Dynamic Drop-Down list with Formula
- Go to worksheet ‘Dynamic’ of Excel working file (Image instructions below)
- Select cell D6
- Open the Data Validation dialog box
- Click the Setting tab
- Select List from Allow list
- Type =OFFSET($B$6,0,0,COUNTA(B:B)-2) into the Source text box.
- Press OK
The formula to auto-update drop-down list in Excel is the following:
Here’s how the formula works,
- COUNTA function returns the total count of all values in Text or Number, “-2” separately used to reduce counts due to text’s (“Static list” – cell B3 and “Fruits” – B5) as they are not part of the list.
- OFFSET function returns a range of cells. B6 is starting, or reference point of the range and range dynamically change as the count in column B.
I consider creating a Table name and List Name as a preferred option because we can use a table or list name multiple time in an Excel workbook. Moreover, defining a name for List and Table keep all data organize apart from most importantly a freedom to add items in a drop-down list.
- Convert a list to Table only If you require a dynamic range
- The new column adjacent to a Table acquires the Data Validation rule even if it’s intended for a different purpose.
Wrap-up
Thank you for reading, along the way, I detailed you few examples to insert static and dynamic drop-down lists. Moreover, steps to create dependent drop-down lists. A drop-down list is an integral part of excel data modeling not just for making sure a secure data entry process but also to provide well-organized data presentation for yourself and your team. Also, to use each line item of a drop-down list in a significant manner.