How to create a drop down menu in Excel [2019]

How to create a drop down menu in Excel [2019]

In Microsoft Excel, it is possible to add a drop-down menu to choose inputs to cells from a predefined list by using data validation feature.

There are primarily two types of drop-down menus in Excel.

Static Drop down menu

This is the simplest way to add a drop-down menu. What you have to do is select the cells in which you like to add a drop-down menu. And then,

Go to “Data” tab  Data Tools  Data Validation

Now you will be presented with “Data Validation” dialog box.

In the “Settings” tab of the Data validation dialog box, select “List” from “Allow” drop down menu.

In the “Source” Input box type the list of drop-down item you wish to include in your drop-down menu separated each by a comma. Then make sure that both of the “Ignore Blank” and “In-cell dropdown” are ticked.

Click okay and select a cell in which you have insert the menu. You should see a small down arrow to the right of the cell. Click that small down arrow to find out the drop-down menu you just created.

Dynamic Drop down menu

In a dynamic drop menu, menu items are typed in a range of cells instead of hard coding inside the “Data Validation” dialog box.

Step 01 : Create a List of menu items in a cell range

Type the list of menu items you would like to have in your drop-down menu, one for each cell without keeping empty cells.

You can enter your menu items in a column or row, but it is highly recommended to make your list in a column if you wish to change and update your menu later.

Step 02 : Convert the cell range into a table (Optional but recommended)

This step is optional, but if you convert your cell range into a table you can remove or add menu items such that those changes automatically update the drop-down menus in each cell that you have inserted the drop-down menu.

What you have to do is select the cell range having your menu items list, and click the “Insert” in the main menu. Then go to “Tables” command group and click “Table”.

Go to Insert  Table

Now the Create Table dialog box will pop up.

Tick the check box “My table has headers” if your table’s first row is the header. Click OK.

Step 03: Insert the drop-down menu into selected cells

Select the cells in which you wish to insert your drop-down menu. It can be one cell, a range of cells or few cells which located apart from each other. Now same as previously,

Go to “Data” tab  Data Tools  Data Validation

Select “List” from the “Allow” drop-down menu.

Then click the Range selection button at the right edge of the “Source” input box. Now select the range of cells in which your list of menu items is entered. As you did previously, tick both the “Ignore Blank” and “In-cell drop-down” check boxes. Click OK to finish. You will see that drop-down menus are active in the cells you have chosen.

Modifying the drop-down menu.

There are two methods to modify the drop-down menu you created depending on the type of drop-down menu.

Static drop down menu.

  1. Select a cell that contains a drop-down menu.
  2. Go to “Data” tab  Data Tools  Data Validation
  3. “Data Validation” dialog box will pop up and you will see the list of menu items you previously entered in the “Source” input box.
  4. Modify the list of menu items as you wish.
  5. Tick the relevant check box to apply the changes to all other cells in the worksheet having similar drop-down menus.
  6. Click OK.

Dynamic drop down menu.

In these types of drop-down menus, we use the cell ranges in the work sheet to feed the drop-down items list. Therefore, modification of the drop-down menu can be done in two ways.

  1. Change range of cells of source
  2. Modify the list items within the source cell range.

Changing the range of cells of source

  1. Select a cell that contains a drop-down menu.
  2. Go to “Data” tab  Data Tools  Data Validation
  3. “Data Validation” dialog box will pop up and you will see the previously selected cell range in the source input box.
  4. Click the Range selection button at the right edge of the “Source” input box and select the new cell range which contains new list of menu items.
  5. Tick the relevant check box to apply the changes to all other cells in the worksheet having similar drop-down menus.
  6. Click OK.

Modify the list items within the already selected source cell range.

Following procedure works only if you have converted the cell range you used have already been converted to a table as recommended above.

Add a new menu item to the end of the list:

Type the new menu item just below the last item of the table.

Delete menu items from the list:

  1. Select the cells that contain menu items you need to remove.
  2. Right click on a selected cell.
  3. Go to Delete Table Rows in the appeared pop up menu.

If you only delete the cell content by pressing “backspace” or “delete” key in keyboard, it will show a blank menu item.

References:

  1. Microsoft office help on how to Create a drop-down list
  2. Apply data validation to cells

Leave a Reply

Close Menu