An Easy Guide to Using a Google Sheets Drop Down List

Watch Video – Creating a Google Sheets Drop down List

A Google Sheets drop down list makes it easy to take pre-specified data and use it freely withing your spreadsheets. To make one you simply have to select your data and then head to the Data validation menu and follow the menu prompts. If you need a little more help than that. Read on for a step-by-step guide. Alternatively, you could take our Google Sheets course to learn all you need to know at once.

Why Use a Google Sheets Drop Down List?

A Google Sheets drop down list is one such feature that allows you to select from a pre-filled list quickly. It creates a data collection inside one cell that lets Google Sheets select from the list. This way, you don’t have to enter the text manually – simply select it from a drop-down list.

The process for adding drop-down lists in Google Sheets has long since changed. However, you don’t have to worry, in this tutorial, you’ll learn how to add a drop-down option in Google Sheets using data validation.

How to Use Drop-Down Lists in Google Sheets

Drop-down menus are a pretty convenient and fun tool in Google Sheets.

Here’s how you can use them:

• Drop Down lists can be used in data entry where you want the user to select from predefined options only. This also ensures that there are no errors (such as misspelled words).
• You can also use drop-down lists in Google Spreadsheets to create interactive charts, to-do lists, and dashboards.

For example, you can create a drop-down with a to-do list where the user can select from options such as a task completed, a task in progress, or yet to start.

• Another (advanced) use of the drop-down list can be to extract data for the selected item. For example, suppose you have a list of students and their marks in five subjects.
• You can use the drop-down menu to select the student’s name and use the formulas to extract the score details of that student automatically.

You will also learn how to copy drop-down lists from one cell and paste it into other cells. You can also easily remove all the drop-downs at once from all the cells.

Whether you’re making a budget for your monthly expenses or calculating the grades of every student in the class, a drop-down list can be a helpful and quick way to do that.

The upside to making a drop-down list is that it’s quite simple and gives immediate results.

It also makes sure that your data entry is error-free as you’re allowing a user to select from a drop-down list instead of entering the data manually (which may lead to misspelled words or incorrect format).

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

You can create a drop-down list in Google Sheets by using a dataset that is already there in a range of cells.

Suppose you have a dataset, as shown below, and you want to create a drop-down list in cell C2 (you can also create these drop-downs in multiple cells at once).

Below are the steps to create a drop-down list in Google Sheets:

1. Select the cell or range of cells where you want to create a drop-down list
2. Click the ‘Data‘ option in the menu.

3. Click on the Data Validation button.

4. A window for data validation rules will pop up to the right of the sheet. Click Add rule

5. In the Criteria option, select the option – “Dropdown (from a range)“

6. Click the box in the filled below criteria. The option to select data range will pop up

7. Select the range of data you want to appear in your drop down menu Google Sheets. Click OK

8. Click Done.
9. You will see a chip in the cell with a down arrow that shows the drop down list when you click it.

You can find the Show validation help text in the advanced options, which have now been changed to Show help text for a selected cell. Now you have the drop-down lists in the selected cell. You can click on the drop-down icon (small downward pointing arrow), and it will show you all the options from which you can select. And as soon as you make a selection, that value is added to the cell.

Dropdown Shortcut

There’s a faster way to get to the dropdown: Simply right-click on the cell and choose dropdown

This wll open the data validation window to the right of the screen.

How to Add a Drop Down List In Google Sheets by Manually Specifying the Items

In case you don’t want to have the list of items in a range in the worksheet and instead specify these manually, you can also do this.

This could be the case when you want to have options such as Yes/No or Good/Ok/Bad as the items in the drop-down. Instead of having these somewhere in the sheet, you can easily specify these while creating the drop-down list.

How to Insert Dropdown in Google Sheets

Below are the steps to create a drop-down list by manually specifying the options:

1. Select the cell (or range of cells) in which you want the drop-down list
2. Click the Data option in the menu
3. In the Data Validation window, click Add rule. The drop down menu will automatically be added.

4. Under Criteria, you will see two fields labeled Option 1 and Option 2. Fill in the fields with the values you want in your drop down list.

5. Click Add another item to add more options.
6. Once you are finished, click Done.

Now when you click on the drop-down icon in the cell, it will show the options you added manually in the drop-down.

How to Add a Drop Down in Google Sheets by Copying Another Drop-Down List

After you add a drop down to a Google Sheet, You can easily copy and paste the drop-down list from one cell to another.

All you have to do is select the cells where you have the drop-down menu already, copy the cell (Control + C) and then paste it on the cells/range where you want it.

As soon as you do this, the in-cell drop-downs will be available in the copied cells as well.

If you only want to copy the drop-down list from a cell and not the formatting (such as color, border, or number format), follow the below steps:

1. Select the cell from which you want to copy the drop-down list
2. Copy the cell (control + C)
3. Right-click on the cells where you want to create the drop-down
4. Hover the cursor over the Paste Special option.

5. Click on the ‘Paste data validation only‘ option.

The drop down menu will copy to the new cell.

How to Change or Remove a Drop-Down Menu In Google Sheets

In case you have a drop-down list (or data validation rule) in a cell and want to remove it or edit it, you can do that easily.

Below are the steps to remove the drop-down list from a cell or range of cells:

1. Select the cells from which you want to remove the drop-down list
2. Click the Data option from the menu
3. Click on Data Validation. This will open the Data Validation window to the right.
4. In the Data Validation dialog window, click on the rule you want to remove. Make sure the correct cell is selected. If you want, you can change the cells in the dialog box as well.
5. Click on the Remove rule option.

6. Click on Done.

You can also remove the drop down menu by clicking on the bin sign next to the rule in the data validation window or by clicking Remove all if you want to remove all the rules.

If you want to edit the drop-down menu and change it (such as adding more items to it or making it refer to some other range as the source), you can do that using the same steps. Just make the changes and click on the Save button.

How to Expand the List to Multiple Cells

You can expand the drop down menu to multiple cells in different ways. The simplest way is to drag the small box at the bottom right corner of the cell to the range you want to expand to.

You can also expand to using the Paste special method we have shown you above. This will copy the drop down list to the cells you want to insert drop down in Google Sheets.

How to Edit the Drop Down List

If you want to edit the drop down list, you can use the drop-down validation window. It will remain open to the right of the sheet unless you close it, in which case you can reopen it through the data menu.

All you need to do is click the rule for the drop-down menu you want to edit. From there, you can make all the necessary changes you want to make. You can also go to edit by clicking on the drop-down list and clicking the pen sign at the very bottom.

You can add or remove items to your list or change the names of the items. You can also add colors to your options in the drop-down list.

You can also copy the cell to the cells you want to expand to using the paste special method we have shown you above:

In the advanced options, you have more options, including:

• Show validation help text
• If the data is invalid – This has two options in case you may have some invalid data in your range: show a warning or reject the input
• Display style- This lets you change the type of display for the drop-down list. There is the chip style, arrow style, and plain text style. However, if you choose plain text, the drop-down menu will disappear.

How to Assign Background Colors to Items in Your Dropdown

Google Sheets added a new feature that allows you to assign colors to your drop-down list items.

Color coding your dropdown list is a great idea, especially if you want to make user selections easier to identify.

You can use conditional formatting to color code your dropdown list. For example, if your dropdown list has Yes or No options, you could use conditional formatting to display all “Yes” selections in Green and “No” selections in red.

Here are the steps you can follow to achieve this:

1. Select your dropdown list(s)
2. From the Format menu, select Conditional Formatting
3. From the Conditional Formatting sidebar that appears, set your formatting rules.

In our case, you can have two formatting rules. For the first rule, select “Text is exactly” from the dropdown menu under “Format cells if…” and type “Yes”. Select a fill color, say red, from the Formatting style options. Click “Add another rule” and repeat to specify a green color formatting when the text is exactly “No”.

This is a great method to color code your drop down menu. However, Google Sheets have also added a color coding option in the data validation window in the new update. We have already shown you how to use it in the article. Here’s how it works:

1. Go to the data validation window. You can access it by going to the drop down menu and click edit.

2. Click on the Rule for the drop down list.

3. Click the circle next to the items for your list

3. Choose the color you want for that option. You can see what it will look like in the preview at the bottom of the colors option. You can choose the colors available or customize your own colors.

4. Once you are done choosing all your colors click Done.

Your drop-down menu will now be color coded as well.

Note: If you have numeric value options in your Google Sheets drop-down list, you can color code it using the Color scale tab of the Conditional formatting sidebar.

Frequently Asked Questions

What Happens When You Insert Data That Wasn’t in the Drop-Down List?

Two potential things can happen depending on the options you chose when you created the drop-down list. If you select Reject input, the data won’t stay in the cell. If you choose Show warning,  a small red triangle will appear in the cell to indicate that there is invalid data.

How Do I Alphabetize a Drop-Down List in Google Sheets? / How Do I Sort a Drop Down List?

All you have to do to alphabetize or sort a drop-down list in Google Sheets is sort the column or range that has the validation data in it. To do this:

1. Select the range you want to sort (the source data for the drop-down)
2. Navigate to Data > Sort
3. In the pop-up menu, decide how you want to sort your drop-down list

How Do You Color Code a Drop-Down List in Google Sheets? / How Do You Associate a Drop-Down List With a Color in Google Sheets?

1. Go to the data validation window and click the rule. You can also go to the drop down menu and click edit.
2. Click the circle next to the options for your list
3. Choose the color you want for that option. You can see what it will look like in the preview at the bottom of the colors option. You can choose the colors available or customize your own colors.
4. Once you are done choosing all your colors click Done.
5. Your drop-down menu will now be color coded as well.

Wrapping Up

As you have seen, adding a drop down list in Google Sheets is not a complex process. Although the system may have changed from previous years, the premise still remains the same. Now that you know how to add a Google Sheets drop down list, you can check out our other article on multiple selection drop down lists in Google Sheets.

Related:

• How to Autofill Numbers in Google Sheets
• How to Insert CheckBox (Tick Box) in Google Sheets
• How to Insert BULLET POINTS in Google Sheets
• How to Insert a Degree Symbol (°) in Google Sheets
• How to Insert an Image in a Cell in Google Sheets