How To Create A Dropdown List In Excel For Mac

 admin

In this type of basic dropdown, the list of allowable entries is specified within the data validation itself; therefore, to make changes to the list, the user must open and edit the data validation. This may be difficult, however, for inexperienced users, or in cases where the list of choices is lengthy. To create a drop-down list: Select cell B3 to make it the active cell.

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 A Dropdown List In Excel For Mac

Creating the Options for the Drop-down Menu

Create Drop Down List In Excel For Mac

  • 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

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

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

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.

You can add a drop down list in a cell, to make it easy for people to enter data. It's really easy to make a simple drop down. Then, after you've made a basic list, experiment with fancier features, like hiding items that have been previously selected.

Make a Basic Drop Down List

It's easy to make a drop down list with Data Validation. In this example, people will pick the name of the printers that they use in the office.

List the Items

First, create a list of printers for the drop down

  • List the printers on a different sheet, in a single column
  • Then, to name the list, select all the cells, click in the Name Box, type a one-word name 'PrimaryList', and press Enter

Here is the name, 'PrimaryList', being typed in the Name Box.

Create the Drop Down List

Next, select the cell(s) where you want the drop down list to appear

  • On the Excel Ribbon, click the Data tab
  • Click the Data Validation command
  • From the Allow drop down, select List
  • Click in the Source box, and press the F3 key, to see a list of the names in the workbook
  • Click on the PrimaryList name, and click OK. That name is added to the Source box, with an equal sign in front of it
  • Click OK, to create the drop down list in the cell.
Select an Item

Now, when the cell is active, you can see an arrow at the right border. Click the arrow, and select your primary printer from the list.

That blue cell has also been named – PPSel (short for Primary Printer Selected)

Hide Used Items

Now it's time to make the data entry sheet a little fancier. We'll add two more drop down lists, where people can choose the names of the backup printers that they use.

The list of printers is almost the same, but we don't want them to pick their primary printer again. That printer should be removed from the list.

Number the Unused Items

To check which printer was selected as the primary printer, we'll add a formula in the column beside the PrimaryList.

How To Add A Drop Down List To An Excel Cell

NOTE: I've formatted the list as a named Excel Table, so when I enter the formula in the first row, it will automatically fill down to the last row.

  • The new column has the heading NotUsed.
  • The formula in cell C4 is:

=IF(COUNTIF(PPSel,[@Printer]),',SUM(MAX(C$3:C3),1))

How To Create A Dropdown List In Excel For Mac

That numbers every row, except the one with the printer selected as the Primary Printer (PPSel)

List the Backup Printers

Next, create another named table, with a list of ID numbers from 1 to 10 (to match the number of printers)

In the column to the right, put the heading, 'Backup'

Put this formula in the first data row in that column:

=IF(E4>MAX(Table1[NotUsed]),', INDEX(PrimaryList, MATCH([@ID], Table1[NotUsed],0)))

The formula:

  • Checks the ID number, to see if it's greater than the MAX number in the NotUsed column
  • If it is greater the result is an empty string
  • Otherwise, it uses MATCH to find the position of that ID number, in the NotUsed column
  • The INDEX function returns the printer name from that row.

Name the Backup List

Next, you'll create a dynamic named range with the list of Backup Printers.

  • Select the cell F2, just above the 'Backup' heading cell
  • On the Excel Ribbon, click the Formulas tab, and click Define Name
  • Type 'BackupList' as the name
  • In the Refers to box, enter the following OFFSET formula, and click OK
Mac

=OFFSET(Lists!$F$3,1,0,MAX(Table1[NotUsed]),1)

The formula returns a range of cells, with the number of rows based on the maximum number in the NotUsed column. You can see that range outlined with a green dashed line, in the screenshot below.

How To Create A Dropdown List In Excel For Macs

Add the Backup Printer Drop Down Lists

How To Create A Drop Down List In Excel Macro

As the final step, add two more drop down lists on the data entry sheet.

For these drop downs, use the named range that you just created – BackupList

How To Add A Drop-Down List To A Cell In Excel

After you select a Primary Printer, its name won't appear in the list for the Backup Printer selection cells.

Get the Sample Workbook

There are other examples, and the sample workbook for this example, on the Hide Used Items page of my Contextures site.

_________________