How to Add a Drop-Down List in Google Sheets

Drop-down lists are a great way to simplify data entry and enforce data validation. By limiting input to predefined options, you can make it easier for users to input data and make it impossible for them to introduce spelling errors or nonexistent options. Additionally, you can combine drop-down lists with other functions and tools. For example, you can color-code the options using conditional formatting.

In this guide, you will learn how to create and edit drop-down lists in Google Sheets using different methods. Follow the step-by-step instructions to create drop-down lists using existing lists or manually inputting the items. Finally, you will learn how to use conditional formatting to assign colors to items in your drop-down list.

How to Add a Drop-Down List in Google Sheets?

Below, you have step-by-step instructions on creating and editing your drop-down lists in Google Sheets. First, you will learn two different ways to add items to the list. Second, you will learn how to color-code your list items using conditional formatting. Third, you will learn how to edit, copy and remove these drop-down lists.

How to Create a Drop-Down List in Google Sheets by Selecting a Range of Cells?

Let’s say you have a field where users need to input the day of the week, and you have the list ready in your spreadsheet. Follow the steps below to create a drop-down list using the existing list within your spreadsheet.

1. 1. In Google Sheets, select the cell where you want the drop-down list.

1. 2. Go to Data > Data Validation.

1. 3. Under “Criteria”, select “List from a range” and click the button to select the range of cells.

1. 4. Select the cells with the values for your drop-down list and click “OK”.

1. 5. Make sure you mark the checkbox to “Show dropdown list in cell”. If you don’t check this box, users will be restricted to the values on the list but will not have the list available within the cell.

1. 6. By default, the response to invalid input is to show the user a warning. However, you can change this so that invalid input is rejected, as shown below. Click “Save”.

1. 7. As you can see, a small inverted triangle has appeared on the right side of the cell. Click on it to see the drop-down list.

1. 8. There you have it. The drop-down list is presented in the same order as the original list, but if you prefer, you can change this by sorting the original list.

How to Manually Insert a Drop-Down List in Google Sheets?

As you saw in the previous section, creating a drop-down using an existing list is easy. However, imagine you only want to include three options. In that case, it’s much easier to type them in manually. For example, you may want a field that indicates a task’s status: “To do”, “In progress”, and “Done”.

1. 1. In Google Sheets, select the cell where you want the drop-down list and go to data validation.

1. 2. Under “Criteria”, you will find “Format cells if”. Select “List of items”.

1. 3. Type in the items, separated by commas, then click “Save”. They will be displayed in the order that you set here.

1. 4. There you have it. Your drop-down list is ready.

How to Create a Drop-Down List in Google Sheets with Conditional Formatting?

Let’s add conditional formatting to the drop-down list created in the previous section.

1. 1. Select the cell with the drop-down list and go to Format > Conditional Formatting.

1. 2. From the sidebar, choose the “Text is exactly” option under “Format rules”.

1. 3. Type in the text, choose the formatting you want to apply, and click “Done”. For this example, I have chosen a red background for the option “To do”.

1. 4. Click on “Add another rule” to add the second option.

1. 5. For the second rule, I will repeat the process but type in the new text and the new color. Click on “Done” to save the new rule.

1. 6. Add one more rule for the third option and click “Done”.

1. 7. That’s it. You have a color-coded drop-down list.

How to Edit a Drop-Down List in Google Sheets?

Once you have created your drop-down list, you can easily edit, copy, or remove it.

How to Edit a Drop-Down List?

Editing your drop-down lists is just as easy as creating them. Follow these steps to add items to your list or edit the settings.

1. 1. Select the cell with the drop-down list you want to edit and go to Data > Data Validation.

1. 2. You can change the location of your drop-down list under “Cell range” and add new options under “Criteria”, as shown below.

1. 3. Under “Appearance”, you can choose to show users a help text when they input invalid data. You can customize the text as you want.

How to Copy a Drop-Down List?

Copying a drop-down list is easy.

1. 1. Select and copy the cell.

1. 2. Paste it to another cell. If you want to paste the drop-down list but not the format, choose Paste Special > Data validation only.

How to Remove a Drop-Down List?

To remove a drop-down list, select the cell or cells containing it and go to Data > Data Validation. Click on “Remove validation”.

Conclusion

Creating drop-down lists in Google Sheets is easy. You can type the items manually or use an existing list in Google Sheets. These lists can help you speed up data entry and prevent mistakes by offering users a predefined set of options. Additionally, you can combine it with other tools in Google Sheets to add more functionality. 

You now know how to create drop-down lists in Google Sheets by manually inputting the options or using a predefined list. Additionally, you can combine these lists with other Google Sheets tools and functions to create more complex functionality, like color coding options using conditional formatting. Finally, you know how to edit, copy, and remove these drop-down lists.

To learn more about conditional formatting in Google Sheets, check out this guide on How to Use Conditional Formatting in Google Sheets.