How to Insert a Drop Down Menu in Google Sheets

A Drop-down list (also known as a drop-down menu or a pulldown menu) is a menu, similar to a list box, that allows users to choose an option from a list of pre-defined options. The drop-down list ensures that users enter only the values that are available in a given list into the cells.

When collaborating on Google Sheets, sometimes, you may want other users to enter repeated values into a column. To avoid inaccuracy, typos, and to make the data entry fastener, you may want to add a drop-down list to ensure that users only input the provided values.

For example, you’re creating a list of tasks that you want your team to perform. And you want your team to update the status of each task as Done, Pending, Priority, Skipped, and Task-in progress. To do that, you can create a drop-down menu with a list of task statuses where the user can select from instead of entering the data manually.

In this tutorial, we’ll cover everything about creating and managing a drop-down list in Google Sheets.

Creating a Drop-Down List in Google Sheets

Drop-down is a data validation function that allows users to control what can be entered in the worksheet. You can quickly create a drop-down using the data validation dialog in Google Sheets.

There are two ways you can add a drop-down menu in Google Sheets. You can either make a drop-down list with the values in a range of cells or manually specifying items for the list.

Create a Drop-Down Menu Using a Range of Cells in Google Sheets

You can make a drop-down list in Google Sheets by using a list of items contained in a range of cells.

First, open the Google spreadsheet and enter the list of items in a range of cells that you want to include in the drop-down list. Here, in this example spreadsheet, we have a list of city names as branches.

Next, select the cell or the range of cells where you want to create the Drop Down list. It can be on the same worksheet or a separate worksheet.

Then, click on the ‘Data’ menu at the top of the spreadsheet and select the ‘Data validation’ option in the drop-down.

Or right-click after you selected the cell(s) and select ‘Data validation’ at the bottom of the context menu.

The data validation dialog box will pop up. Here, you can define and customize your drop-down list with different settings.

Cell Range

The first field, ‘Cell range’, specifies the location where the drop-down list will be created. Since we already selected a range for this, it is automatically filled.

You can also change this by clicking on a ‘table icon’ in the Cell Range field and selecting the range of cells where you want to insert the drop-down list.

Criteria

Next, the Criteria section has a list of options in a drop-down and you only need the first two options. You cannot create a drop-down menu with the other options.

Click on the drop-down and select the ‘List from a range’ option to add a range as the criteria.

In the field next to it, enter the cell reference/range that contains all of the values that you want to be included in the drop-down list. Instead of manually entering the range, just click on the ‘Select data range’ icon (table icon).

Another pop-up named Select a data range will appear. Now, just select the cell/range that contains the list of items and location is automatically added to the ‘Select a data range’ dialog box. Then, click ‘OK’. When you select a range from a different sheet, the sheet name is also included on the list’s location.

In the above example, the list of items in A2:A13 of Sheet 3 and the drop-down is being created in C2:C17 of Sheet 4.

Show dropdown list in cell

Make sure the ‘Show dropdown list in cell’ checkbox is enabled. If this option left unchecked, you can still enter the items from the list by double-click on the cell but you won’t see the drop-down icon in the cell.

On invalid data

You can also choose what happens when someone enters something (invalid data) that is not on the list. You can choose either the ‘Show warning’ or ‘Reject input’ radio button for the ‘On invalid data’ setting. The ‘Show warning’ option accepts the user’s input but with a warning while the ‘Reject input’ option rejects the entry entirely.

Appearance

With the Appearance option, you can give the user a hint on how to use the drop-down list or what kind of values are accepted in the cells.

To activate this option, select the ‘Show validation help text’ check box and a text box will appear right below it. Type the instructions that you wish to display in the text box.

Once you’re done with configuration, click the ‘Save’ button to apply changes.

How Drop-Down Works in Google Sheets

Now, the drop-down lists are created in cell C2:C17 and when you click on a drop-down icon, it will show the list of items.

Now, if a user enters a value that is not on the list then that cell is marked as invalid data. You can notice the marker at the top left corner of cell C6. This is because we chose the ‘Show warning’ option in the ‘On invalid data’ setting in the Data Validation dialog.

And when you hover over the marker, you would see the warning message.

This is how you create a dynamic drop-down list in Google Sheets.

Create a Drop-Down Menu by Manually Entering the Items in Google Sheets

If you want to create a static drop-down list and you don’t have the list of items in a range of cells in the spreadsheet, then you can manually specify the items in the data validation dialog box.

This is method could be useful if you only want to create a simple drop-down list with items like Yes and No, Approved and Rejected, In stock and sold out, etc. Here’s how you do this:

Select the cell or group of cells for which you want to add a drop-down list.

Click the ‘Data’ tab in the menu and select the ‘Data Validation’ option.

On the Data Validation pop-up box, make sure the right cell or range is selected in the ‘Cell range’ field.

In the Criteria drop-down, you need to select ‘List of items’ this time.

In the field next to it, manually type the items you want in the drop-down list, and be sure to separate each item by a comma with no space. For our example, we want to display genders, so we enter ‘Male, Female, and Not Specified’.

You can add space inside the item’s name but not between the items. The items can texts, dates, numbers, formulas, and checkboxes.

Make sure the ‘Show dropdown list in cell’ option is checked. Since this is a static list and we don’t want any other input in the cell, we selected the ‘Reject input’ option.

Once you made the changes, click ‘Save’ to apply.

Now, a static drop-down is created in each cell of the selected range. Click on the drop-down icon or double-click on the cell or press ‘Enter’ on the cell to select an item.

If a user enters a value that is not on the list, the data validation rejects the input and will show you an error message. This is because we chose the ‘Reject input’ option for the ‘On invalid data’ in Data validation.

The error will inform the user that they entered invalid data on a particular cell and they need to enter one of the specified values.

Edit a Drop-Down List in Google Sheets

There may be times when you want to add new items, change items or remove items from an already created drop-down list. It is also possible to supply a new list of items to a pre-existing drop-down list.

Go to the range of cells or the sheet with cell(s) that contains the list of items that you used for your drop-down list. Make the desired changes to the contents of these cells. You can add new items, edit the pre-existing items, and remove items from these cells.

This is the original list we used to create a drop-down before:

Now, we made few changes to the list. We added a new item Stockholm, removed Vienna, and changed New York to New Jersey.

You can also edit the manually entered items in the Data validation dialog box.

After we edited the list of items, the drop-down list will be automatically updated. But some of the previously entered entries using the original drop-down are marked as invalid. Since these values are edited in the list, they are marked as invalid.

Now, when you click on the drop-down, you will get the a newly updated drop-down list.

Note: If you add or delete items in the list, make sure to change the range selection in the Data validation criteria to show all items properly in the drop-down list.

Copy a Drop Down List in Google Sheets

Instead of creating the same drop-down list in multiple sheets, you can quickly copy a drop-down list from one cell to multiple cells in one or multiple sheets.

You can use the simple copy and paste method to quickly copy drop-down list.

To do that, select the cell with a drop-down list, right-click and select ‘Copy’ or press ‘Ctrl + C’. Then, select the cell or range of cells where you want to insert the drop-down list, right-click and select ‘Paste’ or press ‘Ctrl + V’ to paste the drop-down.

But this method will also copy cell formatting (color, style, border, etc.) as well as entry of the drop-down list.

To avoid this you need to use paste special feature. Here’s how you do it.

First, select and copy the cell (Ctrl + C) with the drop-down list. Then, select the cell/range in which you want to copy the drop-down list. Right-click on the selected cell(s), expand the ‘Paste Special’ option, and select the ‘Paste data validation only’ option.

This will copy the drop-down list to your desired location.

Remove a Drop-Down List in Google Sheets

In case you have no need for a drop-down menu anymore and you want to delete them, you can easily do that with a few mouse clicks.

To remove a drop-down list, select the cell or cells where you want to remove the drop-down list. Then, click on the ‘Data’ menu, and select ‘Data validation’.