How to Add Drop Down List in Google Sheets in 2 Minutes

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 – How to Add Google Sheets Drop Down List

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 Add a Google Sheets Drop Down List

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.

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

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

Here’s how to add a drop down menu in Google Sheets:

  1. 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.
  2. Go to Data –> Validation.Drop Down List in Google Sheets - Validation Option
  3. 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 Show dropdown list in cell option
    • Optional – Show validation help text: You can add a custom message here for when users try to add an incompatible drop down option in Google SheetsThe data validation menu in Google Sheets
  4. Click on Save.

This will make a drop down list in Google Sheets 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 menu in Google Sheets, 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 in the Google Sheets cell dropdown:

Drop Down List in Google Sheets - Error

How to Edit Drop Down List In Google Sheets Manually

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 of how to change drop down lists in Google Sheets:

  1. 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.
  2. Go to Data –> Validation.Drop Down List in Google Sheets - Validation Option
  3. 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 option
      Manual values for a drop down list
  4. 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 a Google Sheet Drop Down List

If you have a drop down list in one (or more cells) and you want to quickly move it, you can copy drop down box in Google Sheets 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.
Related: Creating a drop down list in Excel.

How to Add Drop Down List In Google Sheets FAQ

Can You Do Drop-Down in Google Sheets? / How Do I Create a Drop-Down List in Google Sheets?

Yes, you can do a drop-down list in Google Sheets by navigating to Data > Data validation. Then you just have to set the cell ranges and make sure Show dropdown list in cell is checked.

How Do You Add a Yes No Drop-Down in Google Sheets?

here’s how to add a dropdown in Google Sheets with only Yes/No options:

  1. Navigate to Data > Data validation
  2. Enter the Range to have the drop down list in
  3. Under the Criteria, select List of items and type Yes,No in the box
  4. Click Save

How Do I Sort a Drop Down List?

The simplest method to sort a drop-down list is to sort the source columns by highlighting the range, then navigating to Data > Sort sheet and clicking on the method you wish to sort by.

After Learning How to Add a Google Sheet Drop Down List, You May Also Like the Following Tutorials:

Sumit
Spreadsheet Expert at Productivity Spot | Website | + posts

Google Sheets and Microsoft Excel Expert.

Popular Posts