Don’t forget to share this post

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 article, 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.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

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.
How to Add a Drop Down List in Google Sheets Select Cell for Drop Down List
How to Add a Drop-Down List in Google Sheets - Select Cell for Drop-Down List
  1. 2. Go to Data > Data Validation.
How to Add a Drop Down List in Google Sheets Go to Data Validation
How to Add a Drop-Down List in Google Sheets - Go to Data Validation
  1. 3. Under “Criteria”, select “List from a range” and click the button to select the range of cells.
How to Add a Drop Down List in Google Sheets Select List From Range
How to Add a Drop-Down List in Google Sheets - Select List From Range
  1. 4. Select the cells with the values for your drop-down list and click “OK”.
How to Add a Drop Down List in Google Sheets Select Data Range
How to Add a Drop-Down List in Google Sheets - Select Data Range
  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.
How to Add a Drop Down List in Google Sheets Show Dropdown in Cell
How to Add a Drop-Down List in Google Sheets - Show Dropdown in 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”.
How to Add a Drop Down List in Google Sheets Reject Invalid Input
How to Add a Drop-Down List in Google Sheets - Reject Invalid Input
  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.
How to Add a Drop Down List in Google Sheets Click to Display Drop Down List
How to Add a Drop-Down List in Google Sheets - Click to Display 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 Add a Drop Down List in Google Sheets Select From Drop Down List
How to Add a Drop-Down List in Google Sheets - Select From Drop-Down List
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

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.
How to Add a Drop Down List in Google Sheets Go to Data Validation 2
How to Add a Drop-Down List in Google Sheets - Go to Data Validation
  1. 2. Under “Criteria”, you will find “Format cells if”. Select “List of items”.
How to Add a Drop Down List in Google Sheets Select List of Items
How to Add a Drop-Down List in Google Sheets - 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.
How to Add a Drop Down List in Google Sheets Type in Items
How to Add a Drop-Down List in Google Sheets - Type in Items
  1. 4. There you have it. Your drop-down list is ready.
How to Add a Drop Down List in Google Sheets Drop Down List
How to Add a Drop-Down List in Google Sheets - Drop-Down List

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.
How to Add a Drop Down List in Google Sheets Go To Conditional Formatting
How to Add a Drop-Down List in Google Sheets - Go To Conditional Formatting
  1. 2. From the sidebar, choose the “Text is exactly” option under “Format rules”.
How to Add a Drop Down List in Google Sheets Choose Format Rules
How to Add a Drop-Down List in Google Sheets - Choose 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”.
How to Add a Drop Down List in Google Sheets Add Text Format
How to Add a Drop-Down List in Google Sheets - Add Text & Format
  1. 4. Click on “Add another rule” to add the second option.
How to Add a Drop Down List in Google Sheets Add Another Rule
How to Add a Drop-Down List in Google Sheets - Add Another Rule
  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.
How to Add a Drop Down List in Google Sheets Second Rule
How to Add a Drop-Down List in Google Sheets - Second Rule
  1. 6. Add one more rule for the third option and click “Done”.
How to Add a Drop Down List in Google Sheets Third Rule
How to Add a Drop-Down List in Google Sheets - Third Rule
  1. 7. That’s it. You have a color-coded drop-down list.
How to Add a Drop Down List in Google Sheets Color Coded List
How to Add a Drop-Down List in Google Sheets - Color-Coded List

How to Use Conditional Formatting in Google Sheets?

Google Sheet allows you to make data more readable by changing the color of cells based on their value. Here's how to use conditional formatting in Google Sheets.

READ MORE
How to Use Conditional Formatting in Google Sheets

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.
How to Add a Drop Down List in Google Sheets Data Validation
How to Add a Drop-Down List in Google Sheets - 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.
How to Add a Drop Down List in Google Sheets Add New Options
How to Add a Drop-Down List in Google Sheets - Add New Options
  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 Add a Drop Down List in Google Sheets Customize Help Text
How to Add a Drop-Down List in Google Sheets - Customize Help Text

How to Copy a Drop-Down List?

Copying a drop-down list is easy.

  1. 1. Select and copy the cell.
How to Add a Drop Down List in Google Sheets Copy Drop Down List
How to Add a Drop-Down List in Google Sheets - Copy Drop-Down List
  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 Add a Drop Down List in Google Sheets Paste Drop Down List
How to Add a Drop-Down List in Google Sheets - Paste Drop-Down List

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”.

How to Add a Drop Down List in Google Sheets Remove Validation
How to Add a Drop-Down List in Google Sheets - Remove Validation

How to Automate Your Data on Top of Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

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 post on How to Use Conditional Formatting in Google Sheets.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Dec 13 2022, Updated Dec 12 2022