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 where drop-down 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 drop-down list for them to choose from.

In this article, we’ll show you everything you need to know about this useful and time-saving feature.

Using Data Validation to Insert a Drop-Down List Into a Google Sheets Cell

Once you’ve opened the sheet you’re working on, inserting a drop-down list is easy:

1. Pick the cell that you’d like to insert a drop-down list into.
   
   
2. Right-click the cell.
   
   
3. Click “Data Validation”>”Criteria”.
   
   
4. Choose between “List of items” or “List from a range” – whichever works for you.
   
   
5. If you’ve chosen “List of items”, insert the appropriate items. Make sure they’re separated by commas and no spaces.
   
6. If you’ve chosen “List from a range”, select the cells that you’d like to include in the drop-down list.
   
   
7. Check the “Show dropdown list in cell” field (the list won’t appear otherwise) to enable a down arrow to appear.
   
   
8. Click “Save”.
   

If you want users to be able to type, uncheck the “Show dropdown list in cell” field. To prevent people from typing invalid items, select “Reject input”.

If you’d like to add a drop-down list to multiple cells:

1. Highlight them all by selecting the “Cell Range” or just select them with your mouse or keyboard.
2. Repeat the steps outlined above.

There’s also the option to let your collaborators type their data but see a warning if they enter anything invalid. In that case, you should choose the “Show warning” option. Alternatively, you can select the “Reject input” option and disallow entering anything that isn’t on your list of items.

To modify a drop-down list, simply follow these steps:

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

To delete a drop-down list:

1. Click the cell 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 on “Data” in the main menu, and then selecting “Data validation”. Most often, it’s used to create drop-down lists within different cells. 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 lets users enter a predefined type of data, such as numbers, dates, or lists of items.

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 – like numbers, formulas, dates or words – in the input box.
4. When you’re done, click “Save”.

Working with Drop-Down Lists

The next time you – or anyone else – clicks onto that cell, instead of the option to type something, there’ll be the list of items you added. 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 the warning. If someone isn’t sure what the warning means, they should just hover the mouse over the marked entry.

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

Using Colors to Sort Data in a Drop-Down List

If you want to make it easier to navigate through your sheet and add some colors to a drop-down list, you can use “Conditional Formatting”. Follow the steps below:

1. Select the cells containing the dropdown list that want to be in a certain color.
2. Right click and select “Conditional Formatting” > ”Single color” or “Color scale”.
3. In the “Formatting style”, select a color or a scale.
4. Click “Done” (or “Add another rule”).

What Else You Can Do with Data Validation

In addition to adding drop-down lists to your sheets, you can use Data Validation for other purposes too. These include:

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 certain date get grayed out automatically.
2. Setting values to checkboxes. When you add checkboxes to a drop-down list, you can also 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 up with your scripts or formulas. Use the “Reject input” option to lock down any cells you want to keep intact.

Make a Difference with Drop Down Lists

With the option to insert drop-down 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 that you can define, change, or delete based on your needs. While your teammates can insert their data and contribute to your project, you have the option to prevent them from making a mess of a shared sheet.

Have you already tried to insert a drop-down list in Google Sheets? How did it go? Let us know in the comments section below.