- How To Create A Dropdown List In Excel For Macs
- Excel 365 For Mac: Using Data Lists To Populate Drop Down ..
![Full Full](/uploads/1/2/6/2/126238233/887468030.jpg)
If you want to make a drop down list dependent on another list, try this article from Excel by Joe. So there you have it – three different methods to create a drop down list in Excel. Each have their own benefits and drawbacks, so you’ll find that you might use different options for different scenarios. In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table. If you don’t, then you can quickly convert your list to a table by selecting any cell in the range, and pressing Ctrl+T. Create a drop-down list with color - Microsoft Excel in Mac On a new worksheet, type the values that you want to appear in your drop-down list. They should be in a single column or row without any blank cells (for easy to use, it is recommended to sort your values). Select all of your values, right-click, and then click 'Define Name'.
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.
How To Create A Dropdown List In Excel For Macs
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.
- 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
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
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).
![Mac Mac](/uploads/1/2/6/2/126238233/437216843.webp)
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.
Excel 365 For Mac: Using Data Lists To Populate Drop Down ..
See also this tip in French:Création d’une liste déroulante dans une cellule.
Maps creator for mac high sierra. Since then, mind mapping was widely used in the area of both business and education.