Google Sheets Drop Down Lists: Complete Guide

To add a drop down list in Google Sheets, use the insert menu or right click on a cell. The process differs a little from Excel. But rest assured, I’ll show how to add drop down menus in both programs. I’ll also show how to use data validation in my examples below.

How To Add a Drop Down List in Google Sheets

What is a Google Sheet drop down list? A dropdown list in Google Sheets is a useful tool. It’s easy to insert and can help users to organize data more efficiently.

To add a drop down list in Google Sheets, select a cell, and in the top bar menu, click “Insert” and then select the drop down option. Next, you can input new data or use data from a specific range.

Google Sheets is always updating its features, and the same is true for the drop down list feature. There are three ways you can insert a drop down in Google Sheets:

1. Using the “Insert” menu
2. Using Data validation
3. Using the right-click menu

Keep reading as I walk you through all three methods and how to edit, copy, and remove a drop down list.

Using the Insert Menu

Google Sheets recently added the drop down option in the “Insert” menu. It is a quick and simple addition, as you’ll see below:

1. Select a cell where you want to add the drop down list.

2. Go to the “Insert” menu and select the “Dropdown” option to open the side menu.

3. Under the “Criteria” section, select “Option 1” and type in the predetermined answers you want to add to your list.

4. Click “Add another option” to add more choices to your list.

5. Click “Done” to confirm your choices.

Once you have created your list, a gray chip will appear in the selected cell. When you click on it, your drop down list with your predetermined options will appear.

Using Data Validation Rules

Data validation in Google Sheets lets you control the type and range of data. Follow the steps below to create e a drop down list using the Data validation option:

1. Select a cell where you want the drop down list to be.

2. Go to the “Data” menu and select the “Data validation” option. A side menu will pop up.

3. Go to the section that says “Option 1” and enter the predetermined options into your list.

4. Click “Add another option” to add more choices to your list.

5. Click “Done” when you are finished.

Using the Right-Click Menu

The third method is to add a dropdown list in Google Sheets using the “right-click” menu. Follow the steps below to use the “right-click” menu:

1. Select a cell where you want to add the drop down list.

2. Right-click on the cell to open the “right-click” menu and select the “Dropdown” option.

3. Go to the section that says “Option 1” and enter your predetermined answers for your list.

4. Click “Add another option” to add more choices to your list.

5. Click “Done” to confirm your options.

How-To Video

Need a quick recap on how to make a drop down list in Google Sheets? Check out my latest video, where I walk you through the easy steps in under one minute. Enjoy!

How to Add a Drop Down List in Excel

I’ve already covered a few ways to build these menus in Google Sheets, but I wanted to make sure I also covered Microsoft Excel. After all, it’s immensely popular with larger data sets. Whichever you use, there’s a way to use data validation to make a menu.

Here’s the process for adding a drop down list in Excel.

1. Go to Data > Data Validation.
2. That brings up a menu box. There, change the “Allow” option to “lists”.
3. Then, update the “Source” option to include your choices.
4. When you’re ready to insert the drop down list, click “OK”.

If you want to manually enter sources for your Excel drop down list, just make sure to separate them with a comma. Here’s an example. Say you want to allow someone to choose between apples, oranges, and bananas when they click the drop down menu. The source box should read:

apple,orange,banana

Once you click OK, your cell will have a little down arrow next to it. Click the arrow and you’ll see options to choose apple, orange, or banana.

How to Choose a Source Range for a Drop Down List in Excel

Microsoft Excel, like Google Sheets, allows you to automatically select options for your drop down list. To do this, just click the cell range button next to the “source” box in your data validation window. Then, select the cells where you list your options.

How To Add a Drop Down List in Google Sheets Using Existing Data

Sometimes, you may already have the dataset with predetermined options for your drop down list. In this case, you don’t need to input the options individually. Instead, you can use the data you already have in the Google Sheets list from a range.

It sounds complicated, but it really isn’t. Let me walk you through the steps using the example and dataset below.

1. Select a cell where you want to add the drop down list.

2. Go to the “Insert” menu and select the “Dropdown” option.
   • You can also use the “Data Menu” or the “right-click” method.
3. In the criteria section, click the drop down and choose “Dropdown (from range).”

4. Click on the “Data range” button (window icon).

5. Select your dataset range, and click “OK.”

6. Click “Done” to confirm your data options.

I find that although this is a quick and easy method to add a drop down list in Google Sheets, you have to be careful when changing the data. If you change any of the selected ranges, it will affect the drop down list as well.

I usually prefer to copy and paste the data range into a separate column or sheet to be safe. However, this is a personal preference.

Another option for having multiple rows of data is to use a dependent drop down list. This means that when you choose an option in the drop down list, it will pull up the corresponding data in the row.

You can also create dynamic drop down lists that automatically update when changes are made to the dataset, making it easier to manage your datasets.

How To Add a Dropdown List in Google Sheets on Mobile

If you often work on the move or need to use your mobile device, you can also add a drop down in the Google Sheets mobile app. Although the process is slightly different, it is just as easy.

Follow the steps below to add a dropdown via a mobile device:

1. Tap the cell you want to add to the drop down list.
2. Click the vertical three-dot menu.

3. Choose “Data Validation.”

4. Under the “Criteria” options, choose “List of Items.”
5. Add the options in the items section and separate the options by using a comma.

6. Toggle on the options you want to include in your list.

7. Click “Save” to confirm your choices.

Once you have confirmed your options, a grey chip (triangle icon) will appear in the selected cell. When you tap on the tab, your drop-down list will appear.

One thing to note is that the mobile app does not have the same features as the desktop version. Therefore, you will notice different color-assigning features, so I prefer using the desktop version.

How To Copy a Drop Down List in Google Sheets

Sometimes, you want to have the drop down list in multiple cells. You can copy the lists using the keyboard shortcut CTRL + C for Windows or CMD + C for Mac. Then, paste them into the other cells using CTRL +V for Windows or CMD +V for Mac.

Alternatively, rather than copying and pasting the drop down list to every cell, you can expand it instead. Here’s how:

1. Click and hold the small circle at the bottom right of the cell.

2. Drag it to the relevant cells/range and release your mouse button.

I like to use this method because it is easy and efficient to copy your drop down list while maintaining the same data options. So, give it a go and see what you think.

How To Remove a Drop Down Menu In Google Sheets

The easiest way to remove the drop down menu from a cell is to select the cell with said menu and click the backspace button on your keyboard. This automatically deletes the drop down list.

If you are looking for a more concise method, which includes deleting the dataset options, then follow the steps below:

1. Select the cell with the drop down list.
2. Click on the “Edit” setting (pencil icon) at the end of the list.

3. Click the option “Remove Rule.”

• You can also remove the drop down menu by clicking on the “Delete” button (trash can icon) next to the rule in the “Data validation” window.
• Go to “Data” and choose “Data validation” to open the window.
  

  

• If you click the “Remove all” option, it will remove all the previously added rules.

How To Edit a Drop Down List in Google Sheets

Sometimes, you want to change the choices in the drop down list or alternative options. In such cases, all the edits can be done on the “Data validation” window.

Follow the steps below to edit your drop down list:

1. Click the drop down and choose the “Edit” icon (pencil sign) at the end of the list.

• This will open the “Data validation” window where you can make your changes.
• Some of the changes you can make may include changing or adding more options or assigning colors to the options.

If you want to personalize your options, use the “Advanced options” setting.

If you want to add text to leave a hint or a guide for other users, you can select the “Show help text for a selected cell” option. This will pop up if the user inputs an invalid value.

Alternatively, there will be times when a user selects invalid data, or an unexpected problem occurs. Here is how you can deal with them quickly:

• If the data is invalid: If a user inputs data that is not in the drop down list. You can either choose “Show a warning,” which will show an error.
  • This means that the wrong input will remain in the data.

• Reject the input: A pop-up warning will appear if a user inputs invalid data.
  • The pop-up will also show the help text if you added one.

• Display style: This lets you personalize the type of display for the drop-down list.
  • There are three default options to choose from: Chip, arrow, and plain text style.
  • One thing to note is that the drop-down menu will disappear if you choose plain text.

How To Assign Background Colors to Items in Your Drop Down (Color Triggers)

You can also assign color triggers to each option in your drop down list. Previously, you had to do it using conditional formatting, but Google has since added a color assign feature for the drop-down list.

To personalize the color options in drop down list, follow these steps:

1. Select the cell with the drop down list.
2. Click on the “Edit” icon (pencil sign) at the end of the list to open the “Data validation” window.

3. There are gray circles next to the drop down options, allowing you to customize the colors.

4. Customize your options according to the colors.

5. Click “Customize” to choose a specific color.

This also lets you choose a different color for the text by clicking the “Text.”

Once you finish assigning the colors, click “Done: to confirm your choices. If you click on the drop down, your options will be color-coded.

Color-coding your options, like status options, is helpful when organizing data. For example, the color green can represent “completed” tasks, while red can represent “pending” items.

Assigning colors to your dropdown options is helpful because it makes it easier to analyze your data.

Related: How to Make a Google Sheets Date Picker

Why Use a Google Sheet Drop Down List?

Google drop down lists lets you predefine options to be input in a cell. Using a Google Sheet drop down list, you can control the data entered into the cell. Here are some benefits of using a drop-down list in Google Sheets:

• A drop down list enhances data entry workflows by saving time since you only need to choose a value from a predetermined list.
• The lists save time in data analysis because the data is minimized and more organized.
• The organization of data reduces the likelihood of mistakes in your data, like spelling errors, because it reduces the frequency of manual data entries.
• A drop down list brings consistency to your data by standardizing the inputs from the values to the format.
• The lists maintain an organized spreadsheet while cleaning data sets and cell values.
• Multiple selection dropdowns help organize data for complex data categorization.
• A drop down list offers a simple yet impactful way in Google Sheets to keep data organized, efficient, and error-free.

How To Use Drop-Down Lists in Google Sheets

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 simple and gives immediate results.

Furthermore, the use cases for drop down lists in Google Sheets are endless. Here are some common uses for the drop down list in Google Sheets:

• Categorize data: Users can extract data for the selected item. For example, if you have the months of the year, you can select a specific month to see the relevant data for that month. This is common in inventories, projects, or task lists.
• Status tracking: This allows users in a task list or project management to indicate what has been completed, in progress, or not started.
• Assigning roles: Employers can efficiently select which employees or departments are suitable for shift work and schedules.
• Quick responses in Google Sheets survey forms: This allows respondents in Google Sheets to select a predetermined answer from the list.
• Interactive charts, to-do lists, and dashboards: Drop down lists are used to improve efficiency as users can select the relevant answers without manually inputting data.

The examples above show the versatility of using the drop down list in Google Sheets. I find that drop down lists come in handy when using Google Sheets with a team, for example, when creating to-do lists and project management, so each team member knows what their tasks are.

Now that we know the benefits and use cases, let’s discuss creating a drop down list in Google Sheets.

Frequently Asked Questions

How Do I Add a Drop Down in Google Sheets?

To add a drop down list in Google Sheets, follow these steps:

1. Select a cell.
2. In the top bar menu, click “Insert.”
3. Select the “Dropdown” option.
4. You can either input new data or use data from a specific range.

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

If you or any other editor tries to input a value that isn’t in the drop down list, the following pop-up will appear.

This means that the data is incorrect, preventing you from inserting the data into the drop down list.

If you already have a value in the cell when you add the dropdown, a small red triangle will appear, representing an error.

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

You can sort your drop down lists into alphabetical order by using the “Sort” option. Select the drop down lists and go to the “Data” menu. Then, choose the “Sort” range. You can choose the sort range from column A to Z from there.

How Do I Auto Fill a Drop Down List in Google Sheets?

To autofill a drop down list in Google Sheets, select the cells with the dropdown, then click and drag the small circle at the bottom right corner of the selection.

One thing to note is that if the options form a series, like numbers, dates, or months, the series will continue like below:

How Do You Color Code a Drop Down List in Google Sheets?

Coloring your drop down sheet is simple and fun to do. Here’s how:

1. Select the cell with the drop down list.
2. Click on the edit icon at the end of the list to open the data validation window.
3. In the window next to the options, there are gray circles. Click on the drop down.
4. Choose the color you want to assign the option.
5. Click “Customize” to choose a specific color.
6. Once you’re finished assigning the colors, click “Done.”
7. If you click on the drop down, your options will be color-coded.

How Do You Add a Drop Down List in Excel?

To add a drop down list in Excel, first select the cells where you want to add this feature. Then, go to your toolbar and select Data. Choose “Data Validation” and you’ll see a box where you can select “list” from the Allow dropdown. Once you’ve chosen to allow a list in your cell, you can choose the source data that populates your drop down list in Excel.

Wrapping Up

That’s all there is to using Google Sheets to add a drop down list. With the latest updates, you can insert the drop down list via the “Insert” menu.

In this guide, we’ve used examples to show you how to use the Google Sheet drop down list. I’ve also covered practical examples of how to edit, assign color, and remove the drop down list, including screenshots.

Did you have any challenges adding the Google Sheet drop down list? Feel free to ask in the comments, and also let us know the creative ways you use dropdown lists.

References

1. Google Support. AppSheet User Guide [Internet]. Google; [cited 2024 Jan 9]. Available from: https://support.google.com/appsheet/answer/10107325?hl=en
2. Google Support. Use Google Docs to edit Word files [Internet]. Google; [cited 2024 Jan 9]. Available from: https://support.google.com/docs/answer/186103?hl=en&co=GENIE.Platform%3DDesktop
3. MIT. Formula 1 Spreadsheet Solutions [Internet]. MIT; [cited 2024 Jan 9]. Available from: https://www.mit.edu/~mbarker/formula1/f1help/04-data9.htm
4. Microsoft Support. Drop Down Lists [Internet]. Microsoft; [cited 2024 Feb 14]. Available from: https://support.microsoft.com/en-us/office/video-create-and-manage-drop-down-lists-28db87b6-725f-49d7-9b29-ab4bc56cefc2

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
• How to Make a Dependant Drop Down List in Google Sheets