How To Make A Drop Down On Excel

A drop-down listing is an first-class way to give the user an option to select from a pre-defined list.

Information technology tin be used while getting a user to fill a form, or while creating interactive Excel dashboards.

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

Watch Video – Creating a Drop Down List in Excel

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

• How to Create a Drop Downwardly List in Excel
  • #1 Using Information from Cells
  • #two By Entering Data Manually
  • #3 Using Excel Formulas
• Creating a Dynamic Drib Downward List in Excel (Using Kickoff)
• Re-create Pasting Drop-Down Lists in Excel
• Caution while Working with Excel Drop Down List
• How to Select All Cells that take a Drop Downwards List in it
• Creating a Dependent / Conditional Excel Drib Down List

How to Create a Drop Down List in Excel

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

1. Using Data from Cells.
2. Inbound Data Manually.
3. Using the First formula.

#1 Using Data from Cells

Permit'south say you have a list of items equally shown below:

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

1. Select a jail cell where you want to create the driblet down list.
2. Go to Data –> Data Tools –> Data Validation.
3. In the Data Validation dialogue box, within the Settings tab, select List every bit the Validation criteria.
   • As shortly equally you select List, the source field appears.
4. In the source field, enter=$A$2:$A$6, or simply click in the Source field and select the cells using the mouse and click OK. This volition insert a drop downwards listing in jail cell C2.
   • Brand sure that the In-prison cell dropdown option is checked (which is checked by default). If this pick in unchecked, the cell does not show a drop down, however, yous can manually enter the values in the list.

Note: If you lot desire to create driblet down lists in multiple cells at one go, select all the cells where y'all want to create it and and then follow the higher up steps. Make sure that the prison cell references are absolute (such as $A$two) and not relative (such as A2, or A$2, or $A2).

#two By Entering Data Manually

In the above example, cell references are used in the Source field. You can also add items directly by entering information technology manually in the source field.

For case, let's say you lot want to show two options, Yes and No, in the driblet down in a cell. Here is how you lot tin straight enter it in the data validation source field:

This volition create a driblet-downward list in the selected cell. All the items listed in the source field, separated past a comma, are listed in different lines in the drib downwards menu.

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

Note: If you want to create drib down lists in multiple cells at one go, select all the cells where you want to create information technology and so follow the higher up steps.

#3 Using Excel Formulas

Autonomously from selecting from cells and entering data manually, yous can besides use a formula in the source field to create an Excel drop downwards list.

Any formula that returns a list of values can be used to create a driblet-downwardly listing in Excel.

For case, suppose you accept the information fix every bit shown below:

Hither are the steps to create an Excel drop down list using the Outset function:

This will create a driblet-down list that lists all the fruit names (equally shown below).

Note: If you lot desire to create a drop-down list in multiple cells at one go, select all the cells where you want to create it and so follow the above steps. Make sure that the cell references are accented (such as $A$2) and not relative (such equally A2, or A$2, or $A2).

How this formula Works??

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

Information technology returns a list of items from the range A2:A6.

Here is the syntax of the Showtime function: =OFFSET(reference, rows, cols, [height], [width])

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

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

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

The above technique of using a formula to create a drop downward list can exist extended to create a dynamic drop downward listing as well. If you use the Start function, as shown in a higher place, fifty-fifty if you add more items to the listing, the driblet downwards would non update automatically. Yous will have to manually update it each time y'all modify the list.

Here is a way to make it dynamic (and it'southward nothing only a minor tweak in the formula):

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

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

The COUNTIF part counts the not-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, at that place must NOT be whatsoever blank cells in between the cells that are filled.
• If you lot want to create a drop-downwards list in multiple cells at ane go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are absolute (such as $A$ii) and not relative (such as A2, or A$two, or $A2).

Copy Pasting Drop-Downwards Lists in Excel

You can re-create paste the cells with information validation to other cells, and it will copy the data validation as well.

For example, if you lot have a drib-down list in cell C2, and y'all want to apply it to C3:C6 likewise, simply copy the prison cell C2 and paste it in C3:C6. This volition copy the drop-downward list and make it bachelor in C3:C6 (along with the drib downwardly, information technology volition also copy the formatting).

If y'all only want to copy the driblet down and non the formatting, hither are the steps:

This will merely copy the driblet down and not the formatting of the copied cell.

Caution while Working with Excel Drib Down List

You need to to be conscientious when you lot are working with drib down lists in Excel.

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

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

How to Select All Cells that accept a Drop Down List in it

Sometimes, it 'south hard to know which cells contain the drib down list.

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

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 practical to it (this includes driblet down lists as well).

At present you can simply format the cells (give a border or a background color) so that visually visible and you don't accidentally copy another cell on it.

Here is another technique by Jon Acampora yous can utilize to always go on the drop downwards arrow icon visible. You can likewise meet some ways to exercise this in this video by Mr. Excel.

Creating a Dependent / Conditional Excel Drib Downwards List

Here is a video on how to create a dependent drib-down list in Excel.

If you prefer reading over watching a video, continue reading.

Sometimes, you may have more than one drop-down list and you want the items displayed in the second drop down to be dependent on what the user selected in the first drop-downwardly.

These are called dependent or conditional drop downwards lists.

Below is an example of a provisional/dependent drop downward list:

In the in a higher place example, when the items listed in 'Drop Downwards 2' are dependent on the pick made in 'Drop Down 1'.

Now permit's see how to create this.

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

Now, when you brand the selection in Driblet Down one, the options listed in Drop Down List 2 would automatically update.

Download the Case File

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

Of import Note While Working with Conditional Driblet Downwards Lists in Excel:

• When you take made the option, and then yous change the parent drib down, the dependent driblet downwards would not change and would, therefore, be a wrong entry. For case, if you lot select the Usa as the state and so select Florida as the state, and then go back and change the land to India, the state would remain as Florida. Hither is a great tutorial by Debra on clearing dependent (conditional) drib down lists in Excel when the option is changed.
• If the main category is more than than one word (for example, 'Seasonal Fruits' instead of 'Fruits'), then y'all need to apply the formula =INDIRECT(SUBSTITUTE(D3," ","_")), instead of the simple INDIRECT office shown to a higher place. The reason for this is that Excel does not allow spaces in named ranges. And then when you create a named range using more than one discussion, Excel automatically inserts an underscore in betwixt words. And so 'Seasonal Fruits' named range would exist 'Seasonal_Fruits'. Using the SUBSTITUTE function within the INDIRECT function makes sure that spacesare converted into underscores.

You May Also Like the Following Excel Tutorials:

• Extract Data from Drib Downwards List Selection in Excel.
• Select Multiple Items from a Drop Downwards List in Excel.
• Creating a Dynamic Excel Filter Search Box.
• Brandish Main and Subcategory in Drib Down Listing in Excel.
• How to Insert Checkbox in Excel.
• Using a Radio Button (Option Push) in Excel.

Source: https://trumpexcel.com/excel-drop-down-list/

Posted by: fultonvellut.blogspot.com

Share this post