Create Dynamic Drop Down From Table in Excel & Google Sheets

This tutorial demonstrates how to create a dynamic drop-down from a table in Excel and Google Sheets.

 

 

When you create a drop-down list based on a range of cells using data validation, if the data in that specific range changes, then the values in the drop-down list change.

Create Drop-Down List

1. The first step in creating a drop-down list in Excel is to create the list of items in a table.
   Type in a heading for your list (for example, Region).
   Below this heading, type in the items for the drop-down list.
2. Once the list of items is created, format that list as a table.
   Select the list. In the Ribbon, go to Home > Styles > Format as Table. Choose a format type.

 

 

3. You can now create the drop-down list with data validation.
   Select the cell where you wish the drop-down list to go and then in the Ribbon, go to Data > Data Tools > Data Validation > Data Validation…

 

 

4. In the Settings tab, select List from the Allow drop down, and then select the table data as the Source for your list.

 

 

5. Click on the Input Message tab to type an input message if required

 

 

6. Finally, click on the Error Alert tab and type in any error message to be returned to the user if they enter an invalid value.

 

 

7. Click OK to create the drop-down list.

 

 

Amend List Items

To amend any item in the drop-down list, you can just amend the item in your table source data.

 

 

Add or Delete List Items

As your list source is an Excel table, when you add items in the table, the drop-down list automatically adjusts.

 

 

Similarly, if you delete any items from the table, they’re removed from the drop-down list.

 

 

Dynamic Drop Down in Google Sheets

1. You can create a drop-down list in Google Sheets using a named range and data validation.
   Type in a heading for your list (for example, Region).
   Below this heading, type in the items for the drop-down list.
   Highlight all list items, and then create a range name for the list by typing the name for your range in the name box.

 

 

2. Select the cell where you want a drop-down list, and then in the Menu, select Data > Data validation.
3. Select List from a range in the Criteria drop down, and then type equals (=) and the name of your range (e.g., =Region).

 

 

As with Excel, if you amend any of the data entries in your range name, the drop-down list updates automatically.

 

 

Add or Remove Items

If you delete an item from the range, then the list updates. However, if you wish to add an item to the list, insert a row within the range name or redefine the named range to include any additional rows. This is necessary because Google does not have a table feature like Excel’s.

1. To insert a row into an existing range name, right-click on the row header where you wish to insert the row.
2. Select Insert 1 row above and then type in the additional item.

 

 

The range name now updates automatically to include the additional row.

 

 

3. Click on the drop-down list to see the added entry.