Create a Drop Down Menu in Excel by 2 Simple ways

Create a Drop Down Menu in Excel by 2 Simple ways

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. In our Soccer Prediction App tutorial we created a drop down menu. So if want you can download the excel file there to see a practical example.

There are primarily two types of drop down menu in Excel.

Static Drop down menu in excel

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 in excel. And then,

Go to “Data” tab Data Tools Data Validation

Data validation 1

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.

excel data validation dialog

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 in excel you just created.

static drop down menu in excel

Dynamic Drop down menu in excel

In a dynamic drop menu in excel, 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 in excel, 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.

list for dynamic drop down menu in excel

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 in excel. 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 in excel.

There are two methods to modify the drop down menu in excel, 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 in excel 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