How to Create a Drop-down (Data Validation) List. To create a drop-down list, start by going to the Data tab on the Ribbon and click the Data Validation button. The Data Validation window will appear. The keyboard shortcut to open the Data Validation window is Alt, A, V, V. You’ll want to select List in the drop-down menu under Allow. 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. The most commonly used method of building scenarios is to use a combination of formulas and drop-down boxes. In the financial model, you create a table of possible scenarios and their inputs and link the scenario names to an input cell drop-down box. The inputs of the model are linked to the scenario table. Easily create drop-down list with checkboxes in Excel: The Drop-down List with Check Boxes utility of Kutools for Excel can help you easily create drop-down list with checkboxes in a specified range, current worksheet, current workbook or all opened workbooks based on your needs. Download and try it now! ( 30-day free trail). Now, select the cell into which you want to add a drop-down list and click the “Data” tab. In the Data Tools section of the Data tab, click the “Data Validation” button. The Data Validation dialog box displays. On the Settings tab, select “List” from the Allow drop-down list (see, drop-down lists are everywhere!).
Do this:
List the items to be on the pop-up menu in one column of a table.
Select the cells containing the item list.
Click the Format brush and choose Cell
Use the pop-up menu showing Automatic to change the cell format to Pop-up menu.
If you want the menus to include a 'blank' rather than display the first item make that change with the pop-up menu below the item list in the Inspector. The 'blank' choice will be seen as 'None' when the menu is opened, but will display as an empty cell when the menu is not active.
All of the cells are now pop-up menu cells.
To place the same menu anywhere in the document,
select the cell, press option-command-C to copy the format,
select the target cell, press option-command-V to paste the format.
Regards,
Barry
Jan 28, 2017 1:11 PM
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.
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))
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
=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.
Add the Backup Printer Drop Down Lists
How To Create A Dropdown List In Excel For Macs
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
After you select a Primary Printer, its name won't appear in the list for the Backup Printer selection cells.
Get the Sample Workbook
Create A Drop-Down List In Excel From Another Worksheet
There are other examples, and the sample workbook for this example, on the Hide Used Items page of my Contextures site.
How To Create A Dropdown List In Excel For Mac Shortcut
_________________