How to Create a Drop Down List in Google Sheets

How to create drop down lists in Google Sheets

A drop down list in Google Sheets can be helpful when you want the user to select from pre-defined options. It’s a great way to make the data entry consistent.

Watch Video – Creating Drop Down Lists in Google Sheets

 

For example, a user from a drop down list can select easily the country as the US. But if you give him the option to enter it manually, he may enter US or USA, or United States, or the United States of America.

Drop Down List in Google Sheets - Example

You see the benefit here. By giving him a ready option in the form of a drop down, you have made data entry easy as well as consistent.

In this tutorial, you’ll learn:

  • How to Create a Drop Down List in Google Sheets.
  • How to Copy Drop Down Lists.
  • How to Remove Drop Down Lists from Cells.

How to Create a Drop Down List in Google Sheets

There are two ways to create a drop down list in Google Sheets:

  • Using a range of cells to create the drop down.
  • Manually entering the items of the drop down list.

Using a Range of Cells to Create the Drop Down List in Google Sheets 

Suppose you have a country list in cells A1:A6, and you want to get drop down list in cells B1:B4.

Here are the steps to do this:

  • Select the cell in which you want to create the Drop Down list. If you want to get the drop down list in multiple cells, select all the cells.
  • Go to Data –> Validation.Drop Down List in Google Sheets - Validation Option
  • In the Data Validation pop-up box, make the following changes:
    • Cell Range: Sheet1!B1:B4
    • Criteria: List from a range (Sheet1!A1:A6)
    • On Invalid data: Show warning
    • Appearance: Check the Display in-cell button to show list optionDrop Down List in Google Sheets - select from cells
  • Click on Save.

This will create a drop down list in cells B1:B4 and will show the items in the range A1:A6.

Drop Down List in Google Sheets - Example

In the cells where you have the drop down list, you can either select an option from the drop down, or you can enter it manually. If you enter anything which does not match the items specified, you will see the cell get highlighted (with a red triangle at the top right of the cell) as shown below:

Drop Down List in Google Sheets - invalid content

There is also an option to disable data entry in the cells if the entered data does not match the items specified. To do this, in the data validation settings (as shown above), in the On invalid data, select Reject input option.

Now if you enter anything that does not match the specified items, you will see an Invalid Note as shown below:

Drop Down List in Google Sheets - Error

Manually Entering the Items of the Drop Down List

If you want to create a drop down list with static options (such as Yes/No, High/Medium/Low, Agree/Disagree), then you can also manually enter these options in data validation.

Here are the steps:

  • Select the cell in which you want to create the Drop Down list. If you want to get the drop down list in multiple cells, select all the cells.
  • Go to Data –> Validation.Drop Down List in Google Sheets - Validation Option
  • In the Data Validation pop-up box, make the following changes:
    • Cell Range: Sheet1!B1:B4
    • Criteria: List of items (in the field to the right, manually enter the items separated by comma)
    • On Invalid data: Show warning
    • Appearance: Check the Display in-cell button to show list optionDrop Down List in Google Sheets - enter items
  • Click on Save.

This will create a drop down list in the selected cells will show the specified items.

Drop Down List in Google Sheets - yes no

How to Copy Drop Down Lists in Google Sheets

If you have a drop down list in one (or more cells) and you want to quickly copy the drop down, you can do so by using this simple copy paste trick.

Here are the steps:

  • Copy the cell which has the drop down list that you want to copy.
  • Select the cell/cells in which you want to copy the drop down list.
  • Right-click and go to Paste Special –> Paste data validation only.Drop Down List in Google Sheets - paste data validation

This will copy the drop down list to the selected cells.

Note: You can do the same using a simple copy and paste as well, but that would copy the formatting as well.

How to Remove Drop Down Lists from Cells

To remove a drop down list from cells in Google Sheets:

  • Select cells from which you want to delete the drop down list.
  • Go to Data –> Validation.Drop Down List in Google Sheets - Validation Option
  • Click on the Remove validation button.Drop Down List in Google Sheets - Remove validation
Related: Creating a drop down list in Excel.

You May Also Like the Following Google Sheets Tutorials:

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X