We can create a drop-down menu in preparing a pivot table to ensure that there aren’t spelling mistakes. This also prevents values that we do not allow to be entered into the table. The steps below will walk through the process.
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
- Open Microsoft Excel and the document where you want to add the drop-down list. Then, follow these steps. 1) Enter your list items onto a spreadsheet. You can use a new sheet in your workbook or existing cells where you have the items entered.
- The best practice is to create a separate worksheet for your drop-down list. To create a new tab, click the '+' icon next to the last tab in your spreadsheet. Double click the tab to rename it.
- How to Create Dependent Drop-down Lists Note: The steps apply to both Excel for Windows and Mac. Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.
Figure 1: Options for the Drop-down List or menu
This blog post will take you through the steps to create a searchable drop down list in Excel – just like Google search. This is a great Excel trick for working with large drop down lists. In this tutorial we will use a list of 87 names that as we type into the drop down list, it searches the names, and the list shortens to show only those.
- 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
- We will click on Data Validation
Figure 3: Using Data Validation Feature
- We will select List in the Allow Validation criteria and select the range (I3:I5) of the options for the list as the source
- We will click on OK
Figure 4: Drop-down Menu
- If we enter a value in the range (A3:A11) that is not in I3:I5, we will receive an error message
Figure 5: Error message due to an Invalid Input in the range
Setting up the Data
- With the drop-down menu, we will create data that has clients and Sales
- We will select the range A2:B11
- We will click on the Insert tab and click on Table
Figure 6: Clicking on Table
- We will click on OK
Figure 7: Create table dialog box
Figure 8: Created table
Inserting the Pivot Table
- We will click on anywhere within the table and click on Pivot table as shown in figure 7
- We will fill the Location field as D3 and check the existing worksheet box
Figure 9: Creating the Pivot Table
- We will click OK
- We will check the Pivot Table Fields as shown in figure 11
Figure 10: Checking Pivot Table Fields
Figure 11: Created Pivot Table
Adding values to the Pivot Table
- Assuming someone wants to add another client to the Pivot Table, if the individual adds a name that isn’t in I3:I5, an error message is displayed.
- We can add more values by placing the cursor on CELL A11 AND PRESS ENTER. This will make the table to continue the range. If we simply click on Cell A12, the result will be allowed but can’t go into our Pivot Table.
Figure 12: Adding Values to the Pivot Table
- After adding the values, we will click on a cell within the pivot table. We will right-click and click on REFRESH to update the pivot table
Create A Drop-Down List In Excel From Another Worksheet
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
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.
- 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:
How To Add A Drop Down List To An Excel Cell
![How To Create A Dropdown List In Excel For Mac How To Create A Dropdown List In Excel For Mac](/uploads/1/3/4/4/134438259/174851458.jpg)
- 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
How To Create A Dropdown List In Excel For Mac Os
- 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