Creating a dropdown list in your Excel spreadsheet can help to increase the efficiency of your work. This might come in handy, especially when you want your coworkers to provide certain information that may be relevant to the company. By using an Excel dropdown list, you can control exactly what can be entered into a cell by giving the users an option to select from a pre-defined list. When you add a dropdown list to a cell, an arrow will be displayed next to it.
This example will show you how to display a list of parts in a drop down menu. This video is a tutorial on how to create drop-down menus in Excel 2007. How To 3:09 pm: Hack Your Nook Color into a Full (But Cheap) Android. Step 1: Define the Contents of Your List. Open up a new Excel worksheet and put down the contents you want to appear on your list. Make sure each entry occupies one cell, and all entries are vertically aligned in the same column. Also, ensure there are no blank cells between the entries.
Clicking on this arrow will open up the list and give the user the option to choose one of the items on the list. This will not only save you space on your spreadsheet but also make you look like like a superuser and impress your co-workers and boss.
Here’s a step-by-step guide on how to create a dropdown list in Excel. Step 1: Define the Contents of Your List 1. Open up a new Excel worksheet and put down the contents you want to appear on your list. Make sure each entry occupies one cell, and all entries are vertically aligned in the same column. Also, ensure there are no blank cells between the entries. In our case our dropdown menu will open a list of cities to choose from.
Once you’re done assembling the list, highlight all the entries, right-click on them, and select “Define Name” from the menu that will show up. This will open a new window with the title “New Name.” Choose a name for your list and enter the same in the “Name” text box. Click “OK.” Step 2: Adding Your Dropdown List to a Spreadsheet The next step is to add your dropdown list to a spreadsheet. Here’s how to do it. Open a new or existing worksheet where you want to place your dropdown list. Highlight the cell where you want to place the dropdown list.
Click the “Data” tab, then locate the “Data validation” icon in the data tools section and click on it. A data validation box will appear that has three tabs: Settings, Input Message, and Error Alert. In the Settings tab, select “List” from the “Arrow” dropdown list. A new option titled “Source” will now appear at the bottom of the window. Click the text box and then enter a “=” sign, followed by the name of your dropdown list. In our case it should read =cities.
The “Ignore blank” and “In-cell dropdown” boxes are checked by default. With the Ignore blank cell checked, it means it’s okay for people to leave the cell empty.
But if you want every user to select an option from the cell, uncheck the box. You have added a dropdown list to your spreadsheet. With that completed, you can now proceed to the Input message tab. Step 3: Set Input Message for Data Validation (Optional) At times you may want a message (with description) to pop up when the cell containing the dropdown list is clicked. In that case you’ll need to click the “show input message” box in the Input message tab.
You’ll also need to fill out the title and the input message in their respective boxes. Your dropdown list should now look something like the following image.
An Excel drop down list is a useful feature when you’re creating data entry forms. It shows a list of items as a drop down in a cell, and the user can make a selection from the drop down. This could be useful when you have a list of names, products, or regions that you often need to enter in a set of cells. Below is an example of an Excel drop down list: In the above example, I have used the items in A2:A6 to create a drop down in C3. Read: Here is a detailed guide on how to create an. Sometimes, however, you may want to use more than one drop down lists in Excel such that the items available in a second drop down list are dependent on the selection made in the first drop down list.
These are called dependent drop down lists in Excel. Below is an example of what I mean by a dependent drop down list in Excel: You can see that the options in Drop Down 2 depend on the selection made in Drop Down 1. If I select ‘Fruits’ in Drop Down 1, I am shown the fruit names, but if I select Vegetables in Drop Down 1, then I am shown the vegetable names in Drop Down 2. This is called a conditional or dependent drop down list in Excel.
Creating a Dependent Drop Down List in Excel Here are the steps to create a dependent drop down list in Excel:. Select the cell where you want the first (main) drop down list. Go to Data – Data Validation.
This will open the data validation dialog box. In the data validation dialog box, within the settings tab, select List. In Source field, specify the range that contains the items that are to be shown in the first drop down list. This will create the Drop Down 1. Select the entire data set (A1:B6 in this example). Go to Formulas – Defined Names – Create from Selection (or you can use the keyboard shortcut Control + Shift + F3).
In the ‘Create Named from Selection’ dialog box, check the Top row option and uncheck all the others. Doing this creates 2 names ranges (‘Fruits’ and ‘Vegetables’). Fruits named range refers to all the fruits in the list and Vegetables named range refers to all the vegetables in the list.
Select the cell where you want the Dependent/Conditional Drop Down list (E3 in this example). Go to Data – Data Validation. In the Data Validation dialog box, within the setting tab, make sure List in selected. In the Source field, enter the formula =INDIRECT(D3).
Here, D3 is the cell that contains the main drop down. Now, when you 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 drop down list (in 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 ‘Fruits’ (through the ) and hence lists all the items in that category. Important Note: If the main category is more than one word (for example, ‘Seasonal Fruits’ instead of ‘Fruits’), then you need to use the formula =INDIRECT(SUBSTITUTE(D3,” “,””)), instead of the simple INDIRECT function shown above. The reason for this is that Excel does not allow spaces in named ranges.
So when you create a named range using more than one word, Excel automatically inserts an underscore in between words. For example, when you create a named range with ‘Seasonal Fruits’, it will be named SeasonFruits in the backend. Using the within the INDIRECT function makes sure that spaces are converted into underscores. Reset/Clear Contents of Dependent Drop Down List Automatically When you have made the selection and then you change the parent drop down, the dependent drop down list would not change and would, therefore, be a wrong entry.
![For For](/uploads/1/2/5/5/125599379/616245925.jpg)
For example, if you select the ‘Fruits’ as the category and then select Apple as the item, and then go back and change the category to ‘Vegetables’, the dependent drop down would continue to show Apple as the item. You can use VBA to make sure the contents of the dependent drop down list resets whenever the main drop down list is changed. Here is the VBA code to clear the contents of a dependent drop down list: Private Sub WorksheetChange(ByVal Target As Range) On Error Resume Next If Target.Column = 4 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset(0, 1).ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub The credit for this code goes to this on clearing dependent drop down lists in Excel when the selection is changed. Here is how to make this code work:.
Copy the VBA code. In the Excel workbook where you have the dependent drop down list, go to Developer tab, and within the ‘Code’ group, click on Visual Basic (you can also use the keyboard shortcut – ALT + F11). In the VB Editor Window, on the left in the project explorer, you would see all the worksheet names. Double-click on the one that has the drop down list.
![How to create a drop down list in excel How to create a drop down list in excel](http://www.homeandlearn.co.uk/excel2007/images/2NamedRangeDB.gif)
Paste the code in the code window on the right. Close the VB Editor. Now, whenever you change the main drop down list, the VBA code would be fired and it would clear the content of the dependent drop down list (as shown below). Download the Example File If you’re not a fan of VBA, you can also use a simple conditional formatting trick that will highlight the cell whenever there is a mismatch. This can help you visually see and correct the mismatch (as shown below). Here are the steps t0 highlight mismatches in the dependent drop down lists:.
Select the cell that has the dependent drop down list(s). Go to Home – Conditional Formatting – New Rule.
In the New Formatting Rule dialog box, select ‘Use a formula to determine which cells to format’. In the formula field, enter the following formula: =ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,MATCH(D3,$A$1:$B$1)),1,0)). Set the format. The formula uses the to check whether the item in the dependent drop down list is the one from the main category or not.
If it isn’t, the formula returns an error. This is used by the ISERROR function to return TRUE which tells to highlight the cell.
Download the Example File You May Also Like the Following Excel Tutorials:. I want to create two cells dependent on the data entered into the first cell. So say I have a list of Company Branches listed by city. Then I have 6 multiple lists that list the Foremen that work in each city AND I have 6 lists of Superintendents that work in each city. I created the city list in cell B2. In the Superintendents cell E3 I used =Indirect(B2) and it lists all the Superintendents working in the city showing.
Now in cell E2 I want to have the Foremen that work in each city. I tried =Indirect(B2) which gives me the same list in cell E3. How do I get E2 tied to B2????