Then select Data Validation from the Data ribbon. Let’s select the cell where we want to apply data validation or drop down list. We will implement first in a single cell. Step 3.3 – Edit the Name Step 4: Implement Data validation For example, L_EMP as this is a list of Employee Names. I usually provide all my lists names that start with L. In the next window that opens, specify the name we want to give. In the following window, click on New, as we are creating a new Name. Or select from the Formulas ribbon as shown below. Select the list of values and then press Ctrl+F3 to open Name Manager. Once you hit OK, you will see that the table is created. Please make sure that you check the box ‘My Table has headers’. Select all four cells with data and press Ctrl+T Step 2 – Convert to table Then, we have entered names of three employees. Step 1 – Enter list of valuesįirst value (Employee Name) refers to the header or field name. In a blank new sheet, let’s type the following information. How to build a drop down list? Step 1: Enter list of values Now, we know what a drop down list is, let’s see how we can build this. If the user enters a value not in the list, it will not allow. If the user enters an invalid value, there will be an error message. User can choose Mike from the list instead of typing the value. John, Mike and Kate are the allowed values When click on the downward arrow button, we see John, Mike, and Kate. In the image above, you can see the message for the user instructing what data is expected in that cell. Optionally, you can provide an error message when the user does not enter a valid value.It limits what values can be entered in a cell.It allows the user to choose from a list instead of typing the value.It informs the user what values are allowed.Optionally, you can provide message to guide the user on data entry.What are drop down lists?Ī drop down list is a way to implement data validation and improve data entry in Excel. If you would like to be notified of these videos, please subscribe to our YouTube channel. Video (How to create drop down list in Excel) In this tutorial, we are going to see how we can create a simple drop down list in Excel. Modified on: Tue, 9 Jun, 2020 at 12:07 PM
#How to create drop down list in excel how to
When you select an item in the first drop-down list, you should see the items pertaining to your selection as options in the second list.Solution home Excel Features How to How to create a drop down list in Excel? When you finish, click “OK” to add the drop-down list to the cell. If you’d like to include an Input Message or Error Alert, select those tabs in the pop-up window and enter the details. Note: The INDIRECT function “returns the reference specified by a text string.” For additional details on this function, check out the Microsoft Support page. Be sure to replace the cell reference in parentheses with the cell containing your first drop-down list. In the Source box, enter the formula below. Optionally, you can check the box to Ignore Blank cells if you like. Under Allow, pick “List,” and to the right, check the box for In-Cell Dropdown. Then, go to the Data tab and click “Data Validation” in the Data Tools section of the ribbon.Ĭhoose the Settings tab in the pop-up window. You’ll use the Data Validation feature in Excel, just like when creating your first list. Once all of your list items are in a sheet and named, it’s time to create the second drop-down list. For the purposes of this how-to, you’ll notice that we have everything on the same sheet. You can add the items for your dependent list on the same sheet where the list will reside or on a different one. Using our example, we’ll name our groups with the departments in our first list: Marketing, Finance, and HR. The names for each group should match the list items in your first dropdown. With the cells selected, go up to the Name Box on the left side of the Formula Bar and enter a name for the cell group. This places the cells in a group so that you can name the group. List the items for the dependent list, and then select the cells. These employees are who we need to add and name. Our departments include Marketing, Finance, and Human Resources (HR), and each has three employees.