How to Insert Drop-Down Lists in Google Sheets

When many users need to enter data into a shared Google Sheet, it can often create confusion. This is when dropdown lists can be of great help.

If you don’t want teammates to type random entries, make typos, or mess up a formula, you can validate their entries by creating a dropdown list for them to choose from.

This article shows you everything you need to know about creating dropdown lists in Google Sheets. Let’s get started!

Using Data Validation to Insert Dropdown Lists

Once you’ve opened the Google Sheet you’re working on, inserting a dropdown list is easy:

1. To add a dropdown list to one cell, “Right-click” on it. To select multiple adjacent cells, go to Step 2.
   
2. To add a dropdown list to several adjacent cells (horizontally or vertically), left-click one, use the “Shift + arrow” keys to select them all (or left-click and highlight them using your mouse), then right-click on one.
3. To add a dropdown list to several non-adjacent cells, hold the “Ctrl” key, select the cells, and right-click one.
4. In the right-click menu, select “View more cell actions,” then choose “Data Validation -> Criteria.”
   
5. Choose the “List of items” option for items you’ll create or “List from a range” to choose cells as items, whichever works for you.
   
6. If you chose “List of items,” add your entries using commas with no spaces, such as “Approved,Rejected,Cancelled.”
   
7. If you chose “List from a range,” select the cells you want to become your selectable entries.
   
8. Next, add a checkmark to “Show dropdown list in cell” to enable a down arrow. If you want users to be able to type instead, simply uncheck the option.
   
9. To let people type data but see a warning, if anything is not on your list, select “Show warning.”
10. To prevent people from typing invalid items not found in your list, select “Reject input.”
11. Click “Save.”
    

How to Modify Your Dropdown List

To modify a dropdown list, simply follow these steps:

1. Click the cell or cells you’d like to modify.
2. Click “Data -> Data validation.”
3. Locate the listed entries, edit them, and click “Save.”

How to Remove a Dropdown List

If you need to start over, move the dropdown list, or remove it, do the following:

1. Click the cell or cells you’d like to modify.
2. Click “Data -> Data validation.”
3. Choose “Remove validation.”

What Is Data Validation?

Data validation is an option in Google Sheets that helps you organize your data by validating items. You can access it by clicking “Data” in the main menu and selecting “Data validation”. Most often, it’s used to create dropdown lists within different cells that validate the user’s entry to match preselected options. It also allows you to modify the content and design of any given list.

One of the ways to validate data is to apply criteria that let users enter a predefined type of data, such as numbers, dates, or lists of items.

How to Use Data Validation for Dropdown Lists

1. In the “Data validation” menu, click “Criteria.”
2. Check the appropriate type or types of items that you want your teammates to enter.
3. If you wish, you can add exact items, such as numbers, formulas, dates, or words into the input box.
4. When you’re done, click “Save.”

Working with Dropdown Lists

The next time you, or anyone else, click on that dropdown cell, there’ll be the list of items you added instead of the option to type something. If you’ve checked the “Show validation help text” box, the text you entered appears every time someone clicks one of the validated cells.

If you’ve chosen the “Show Warning” option, entering invalid data will trigger a red mark in the upper-right corner of the cell and include a warning when hovered over.

If you’ve chosen the “Reject Input” option, people will also get a popup warning and won’t be able to save the invalid items.

Using Colors to Sort Data in a Dropdown List

If you want to make it easier to navigate through your sheet and add some colors to a dropdown list, you can use “Conditional Formatting.”

1. Select the cells containing the dropdown list you want to be in a specific color.
2. Right-click and select “Conditional Formatting,” then choose “Single color” or “Color scale.”
3. Select a color or scale in the “Formatting style” section.
4. Click “Done” (or “Add another rule.”)

What Else Can You Do with Data Validation?

In addition to adding dropdown lists to your sheets, you can use Data Validation for other purposes, including:

1. Tracking future tasks. In data validation, select the “dates” option, set conditional formatting as explained above, and set it up so that all items that include a particular date get grayed out automatically.
2. Setting values to checkboxes. When you add checkboxes to a dropdown list, you can assign them values like “yes” or “no.”
   1. Select Data Validation in the Data menu.
   2. Under Criteria, select “Checkbox.”
   3. Select “Use custom cell values” and type “yes,” “no,” or whatever you’d like.
3. Preventing other people from messing with your scripts or formulas. Use the “Reject input” option to lock down any cells you want to keep intact.

Make a Difference with Dropdown Lists

With the option to insert dropdown lists, validate data, and much more, Google Sheets is a great free alternative to Microsoft Excel. Data Validation helps you limit the values within a cell to a range or a list of items you can define, change, or delete based on your needs. While your teammates can insert their data and contribute to your project, you can prevent them from making a mess of a shared sheet.