How To Set Up Dropdowns In Excel

A drop-downwardly list is an fantabulous way to requite the user an selection to select from a pre-defined listing.

It tin be used while getting a user to fill up a class, or while creating interactive Excel dashboards.

Driblet-downwardly lists are quite common on websites/apps and are very intuitive for the user.

Sentinel Video – Creating a Drib Downwardly List in Excel

In this tutorial, you'll larn how to create a drop down listing in Excel (it takes only a few seconds to do this) along with all the awesome stuff you can practice with information technology.

How to Create a Drop Down List in Excel
- #1 Using Data from Cells
- #2 By Entering Data Manually
- #3 Using Excel Formulas
Creating a Dynamic Drop Downward Listing in Excel (Using OFFSET)
Copy Pasting Driblet-Down Lists in Excel
Caution while Working with Excel Driblet Down List
How to Select All Cells that have a Drop Downwards List in it
Creating a Dependent / Conditional Excel Drop Downwardly List

How to Create a Driblet Down Listing in Excel

In this department, you volition learn the exacts steps to create an Excel drop-down list:

Using Information from Cells.
Inbound Information Manually.
Using the Kickoff formula.

#one Using Information from Cells

Allow's say yous accept a list of items as shown below:

Hither are the steps to create an Excel Drop Down List:

Select a jail cell where you desire to create the drop downward list.
Go to Data –> Data Tools –> Data Validation.
In the Data Validation dialogue box, within the Settings tab, select List every bit the Validation criteria.
- As soon as you select Listing, the source field appears.
In the source field, enter=$A$2:$A$6, or but click in the Source field and select the cells using the mouse and click OK. This volition insert a drop down list in cell C2.
- Make sure that the In-jail cell dropdown selection is checked (which is checked by default). If this selection in unchecked, the cell does not bear witness a drop downwardly, yet, you tin can manually enter the values in the listing.

Note: If you want to create drop down lists in multiple cells at one go, select all the cells where y'all want to create it so follow the above steps. Make sure that the prison cell references are absolute (such equally $A$ii) and not relative (such as A2, or A$2, or $A2).

#2 Past Entering Data Manually

In the above instance, cell references are used in the Source field. You lot can also add items straight by entering it manually in the source field.

For example, let's say yous want to show ii options, Yes and No, in the driblet down in a prison cell. Here is how you tin can directly enter it in the data validation source field:

This will create a drop-down listing in the selected prison cell. All the items listed in the source field, separated past a comma, are listed in different lines in the drop down card.

All the items entered in the source field, separated by a comma, are displayed in different lines in the drop down list.

Annotation: If y'all want to create drop downwardly lists in multiple cells at one go, select all the cells where y'all want to create it and and so follow the to a higher place steps.

#iii Using Excel Formulas

Apart from selecting from cells and entering data manually, y'all tin can as well use a formula in the source field to create an Excel drop down listing.

Whatever formula that returns a listing of values can exist used to create a drop-downwardly list in Excel.

For example, suppose y'all have the data fix as shown beneath:

Here are the steps to create an Excel driblet down list using the OFFSET function:

This volition create a drop-down list that lists all the fruit names (as shown beneath).

Note: If yous want to create a drop-down list in multiple cells at one get, select all the cells where you lot desire to create information technology and then follow the above steps. Brand sure that the cell references are accented (such every bit $A$2) and not relative (such as A2, or A$two, or $A2).

How this formula Works??

In the above case, we used an Commencement function to create the drib down list. Information technology returns a list of items from the ra

It returns a listing of items from the range A2:A6.

Hither is the syntax of the Get-go function: =OFFSET(reference, rows, cols, [meridian], [width])

Information technology takes v arguments, where we specified the reference as A2 (the starting point of the list). Rows/Cols are specified as 0 equally we don't want to offset the reference jail cell. Height is specified as 5 equally there are five elements in the list.

Now, when y'all use this formula, it returns an array that has the list of the 5 fruits in A2:A6. Note that if you lot enter the formula in a cell, select information technology and press F9, you would see that it returns an array of the fruit names.

Creating a Dynamic Drop Down List in Excel (Using Kickoff)

The higher up technique of using a formula to create a drib down listing can be extended to create a dynamic drib down list as well. If you lot use the OFFSET function, as shown higher up, even if you add together more items to the list, the drop down would not update automatically. You will take to manually update it each time you change the listing.

Here is a way to make it dynamic (and information technology's aught merely a minor tweak in the formula):

Select a jail cell where you lot want to create the drop down list (cell C2 in this example).
Go to Data –> Information Tools –> Information Validation.
In the Data Validation dialogue box, within the Settings tab, select Listing as the Validation criteria. As soon as y'all select List, the source field appears.
In the source field, enter the following formula: =OFFSET($A$2,0,0,COUNTIF($A$ii:$A$100,"<>"))
Brand sure that the In-cell driblet down option is checked.
Click OK.

In this formula, I have replaced the argument 5 with COUNTIF($A$ii:$A$100,"<>").

The COUNTIF function counts the non-blank cells in the range A2:A100. Hence, the OFFSET function adjusts itself to include all the non-blank cells.

Note:

For this to work, there must Not be any blank cells in between the cells that are filled.
If you desire to create a driblet-down listing in multiple cells at one become, select all the cells where you want to create it then follow the above steps. Make sure that the cell references are accented (such equally $A$2) and non relative (such as A2, or A$2, or $A2).

Copy Pasting Drop-Down Lists in Excel

You can copy paste the cells with information validation to other cells, and it will copy the data validation also.

For example, if you have a drib-down list in cell C2, and you desire to apply it to C3:C6 likewise, simply re-create the cell C2 and paste it in C3:C6. This will copy the driblet-down list and make it available in C3:C6 (forth with the drop down, it will also copy the formatting).

If you but want to re-create the drop down and not the formatting, here are the steps:

This will just re-create the drop downward and not the formatting of the copied cell.

Caution while Working with Excel Drop Down List

You demand to to be conscientious when yous are working with drop downward lists in Excel.

When you copy a cell (that does not contain a drop downward list) over a cell that contains a drop down list, the drop down list is lost.

The worst part of this is that Excel will not show any alarm or prompt to let the user know that a drop down will exist overwritten.

How to Select All Cells that have a Drib Down List in it

Sometimes, it 'southward hard to know which cells contain the driblet downwardly list.

Hence, information technology makes sense to mark these cells by either giving it a distinct edge or a background color.

Instead of manually checking all the cells, there is a quick way to select all the cells that have drop-down lists (or any data validation rule) in it.

This would instantly select all the cells that have a data validation rule applied to information technology (this includes driblet down lists as well).

Now you can just format the cells (give a border or a background color) so that visually visible and you don't accidentally copy some other prison cell on information technology.

Here is another technique by Jon Acampora y'all tin employ to always keep the drop down pointer icon visible. You can as well encounter some means to do this in this video by Mr. Excel.

Creating a Dependent / Conditional Excel Drop Down Listing

Here is a video on how to create a dependent driblet-down listing in Excel.

If y'all prefer reading over watching a video, go along reading.

Sometimes, you may have more than than one driblet-downwardly listing and yous desire the items displayed in the second drop down to exist dependent on what the user selected in the first drib-down.

These are chosen dependent or provisional drop downwards lists.

Below is an case of a conditional/dependent driblet down list:

In the above example, when the items listed in 'Drop Down 2' are dependent on the option made in 'Drib Down 1'.

Now allow's encounter how to create this.

Here are the steps to create a dependent / conditional driblet downward list in Excel:

Now, when you lot make the selection in Drop Down 1, the options listed in Drop Down List 2 would automatically update.

Download the Example File

How does this work? – The conditional drib down list (in prison cell E3) refers to =INDIRECT(D3). This means that when you select 'Fruits' in cell D3, the drop down list in E3 refers to the named range 'Fruits' (through the INDIRECT function) and hence lists all the items in that category.

Important Notation While Working with Conditional Drop Downwards Lists in Excel:

When y'all accept made the option, and and so you change the parent drop downward, the dependent driblet downwardly would not alter and would, therefore, be a wrong entry. For example, if you select the US as the country and so select Florida as the land, and then become back and alter the state to India, the state would remain as Florida. Here is a swell tutorial by Debra on immigration dependent (conditional) driblet down lists in Excel when the selection is changed.
If the main category is more than one word (for example, 'Seasonal Fruits' instead of 'Fruits'), then y'all demand to use the formula =INDIRECT(SUBSTITUTE(D3," ","_")), instead of the simple INDIRECT part shown above. The reason for this is that Excel does not let spaces in named ranges. Then when you create a named range using more than one word, Excel automatically inserts an underscore in between words. And so 'Seasonal Fruits' named range would be 'Seasonal_Fruits'. Using the SUBSTITUTE function inside the INDIRECT function makes sure that spacesare converted into underscores.

Y'all May Also Like the Following Excel Tutorials:

Extract Information from Driblet Down List Option in Excel.
Select Multiple Items from a Drop Downwardly List in Excel.
Creating a Dynamic Excel Filter Search Box.
Display Main and Subcategory in Driblet Down List in Excel.
How to Insert Checkbox in Excel.
Using a Radio Button (Option Button) in Excel.