Create Dynamic Drop Down From Table in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on February 6, 2023

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


dynamic dropdown intro


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.


dynamic dropdown format as table


  1. 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…


dynamicdropdown create dropdown


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


dynamicdropdown select list


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


dynamicdropdown input


  1. 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.


dynamicdropdown error alert


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


dynamic dropdown dropdown


Amend List Items

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


dynamic drop down amend drop down


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.


dynamic dropdown add to list


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


dynamicdropdown delete items


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.


dynamicdropdown gs rangename


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


dynamicdropdown gs create dropdown


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


dynamic dropdown gs updated dropdown


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.


dynamicdropdown gs insert row


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


dynamic dropdown gs amend range name


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


dynamic dropdown gs amend

AI Formula Generator

Try for Free

See all How-To Articles