Pick a cell where you want the Excel drop down list to be located and use the ribbon to access the Data Validation function, which is located on the Data ribbon. The keyboard shortcut on a Windows computer is Alt, A, V, V. Step 3: Select “List” and Link the Data. Drop down list and Conditional formatting tools are very useful in Excel 2016, to view your data in a particular format manner. You can create a dropdown list in google sheets using the same method. You can create a dropdown list in google sheets using the same method. After you create the drop-down list, editing the entries is as easy as changing the cell data. In this example, the data is pulled from a cell that's located in a different worksheet. The instructions in this article apply to Excel 2019, 2016, 2013, and 2010 and Excel for Mac 2019, 2016, and 2011. Creating the Options for the Drop-down Menu. We will open a new sheet and type the content that will form the main part of our table; Figure 1: Options for the Drop-down List or menu. We will select the range where we will insert only values present in the list. We will use A3:A11; We will click on the data tab; Figure 2: Click on Data Tab.
To create your own drop-down list for the cell, do the following:
1. Create a column or a row of the items in theactive spreadsheet for the drop-down list.
2. Select the cell that needs a drop-down list fordata validation (cell B2, in this example).
3. On the Data tab, in the Data Toolsgroup, click Data Validation:
4. In the Data Validation dialog box, onthe Settings tab:
- In the Allow drop-down list, select the List item.
- In the Source box, specify the range that contains the drop-down list items(in this example, the range is E1:E8).
- Check In-Cell Dropdown option to show the drop-down list. Otherwise, Excelvalidates your data entry but does not show the drop-down button to select itemsfrom the list.
5. Click OK.
Notes:
- You can hide the range of values used in the Source box or create it onthe protected sheet that you can hide.
- You can add the drop-down list to multiple cells by selecting the appropriatecells or copy/paste the cell with a list.
- If the list is short, you can avoid Step 1. Instead, type your list items(separated by commas) in the Source box in the Data Validationdialog box:
- If you plan to share your workbook with users of Excel 2007 or earlier, makesure that the list is on the same sheet as the drop-down list. Alternatively,you can use a named range.
![How to create a dropdown list in excel for mac osx How to create a dropdown list in excel for mac osx](/uploads/1/1/8/6/118682438/750885076.gif)
See also this tip in French:Création d’une liste déroulante dans une cellule.
Note: The steps described in this article are based on Microsoft Excel 2007.
One of the uses of MS Excel is to easily and quickly create fill-in forms. A common feature in fill-in forms is the automation of field entries based on a list item selection. This article shows a simple way of how to create a drop-down list. The article than proceeds to show how cell values can be changed when the list item selected is changed.
Creating the drop-down list items
Creating a drop-down list is very easy. In this article we create the list in a separate worksheet from where the list will be used.
How To Create A Dropdown List In Excel For Macs
- Open a new worksheet. You can either use one of the worksheets already created by Excel when creating a new document or by using the ‘New Tab’ button at the bottom of the screen (see Figure 1).
- In the newly created worksheet write the items under each other. Figure 2 shows an example of a list.Figure 2
- The last step is to give a name to the list. To do this, highlight the list you created in point 2. Then in the Name Box (see Figure 3) type in the list name.
Note: For the purpose of this article the list name is set to ‘ListItems’
Creating the drop-down cell
- Switch back to the worksheet where the form is beign creating
- Select the cell that will be holding the drop-down list
- From the Data menu click the ‘Data Validation’ button (see Figure 4). This will open the window shown in Figure 5.Figure 4
- Change the ‘Allow:’ drop-down value to ‘List’ (Figure 6)Figure 6
- Now a new field labelled ‘Source:’ has been added to the window. In the newly added field type ‘=<the name of the list>’. Continuing on this article’s example the field value is set to ‘=ListItems’ (see Figure 7). Then press Ok and the cell selected on point 2 is converted into a drop-down list (see Figure 8).Figure 8
Linking a cell to the drop-down value
Earlier in the article we have created a list of items which we used as a drop-down list. Now it is time to create the automatic insertion of contents in a cell based on the list selected item. There are two scenarios that one might have to handle:
- The value of the drop-down cell needs to be copied into a new cell. This is an easy step as all that has to be done is to set the cell formula to ‘=<drop-down cell>’. If we had to do this in the article’s example the cell formula will be ‘=C1’.
- A more complex scenario is when the value displayed in the cell is different from the drop-down select item. To handle this scenario further steps needs to be undertaken:
- Switch to the worksheet containing the list items.
- In the column next to the list items add their corresponding values to be used in the form cell (as shown in Figure 9).
- Switch back to the worksheet containing the form.
- Select the form cell that will contain the value associated with the selected drop-down item.
- Set the cell formula toWhere the ‘INDEX’ function instructs Excel to select the value in a specific row and column from an array that is specified in the 1st parameter. In the case of this article the column is the second column in the array, so the value for the parameter is set to 2. However determining the row number is not that simple. As the drop-down cell will only contain the list item text we need to determine the related row. To do this Excel provides the ‘MATCH’ function. The ‘MATCH’ function searches an array for a specific criterion and returns the index that satisfies the search criteria best.
Figure 10
This article showed a simple way of creating a drop-down list and how cell’s can be linked to the drop-down list value.
Article sample file: How to link a cell value with a an Excel drop down list
Article sample file: How to link a cell value with a an Excel drop down list
How To Create A Dropdown List In Excel For Mac Shortcut
References
- Microsoft Office: Create a drop down list from a range of cells
- Microsoft Knowledge Base: How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel