How To Create A Dropdown List In Excel For Mac

admin

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.

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. Click OK and your first drop down list will appear. Now make sure to note down which cell you put the first dropdown list in because you will need it later on, i.e. Now, click on another blank cell and go to Data Validation again. Choose List again, but this time type in “=indirect(E2)” into the Source. Instead of E2, you will put. 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.

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

  • 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

See More Results

  • 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

How To Create A Dropdown List In Excel For Mac
  • 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

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.

Excel For Mac > Fill In Empty Cells With Data From The ...

  1. 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).
  2. Select all of your values, right-click, and then click 'Define Name'
  3. Then it will prompt up a 'New Name' window. Type a name for the values of your drop-down list and then click 'OK'. (No space in the name. )
  4. Select the cells that you want to have drop-down list.
  5. Click Data Validation.
  6. In the settings tab, select 'List' from Allow box
  7. From the source field, type equal sign (=) and immediately followed by the name you defined in step 3.
  8. Click OK to confirm.

How To Create A Dropdown List In Excel For Macs

Apply the Conditional Formatting to color coded the drop-down list values.

How To Make A Drop Down List In Excel Using Macro


  1. Highlight the cells you want to have drop-down list.
  2. Then click 'Conditional Formatting' --> New Rule.
  3. Select 'format only cells that contain'
  4. Edit your condition in the session called 'Edit the Rule Description'.
  5. Click 'format' button to select your desired color in cell.

Creating Dropdown Menus In An Excel Mac 2011 Spreadsheet ...