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.
Table of Contents
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:
- Using the โInsertโ menu
- Using Data validation
- 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:
- Select a cell where you want to add the drop down list.
- Go to the โInsertโ menu and select the โDropdownโ option to open the side menu.
- Under the “Criteria” section, select โOption 1โ and type in the predetermined answers you want to add to your list.
- Click โAdd another optionโ to add more choices to your list.
- 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:
- Select a cell where you want the drop down list to be.
- Go to the โDataโย menu and select the โData validationโย option. A side menu will pop up.
- Go to the section that says โOption 1โ and enter the predetermined options into your list.
- Click โAdd another optionโ to add more choices to your list.
- 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:
- Select a cell where you want to add the drop down list.
- Right-click on the cell to open the โright-clickโ menu and select the โDropdownโย option.
- Go to the section that says โOption 1โ and enter your predetermined answers for your list.
- Click โAdd another optionโ to add more choices to your list.
- 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!
[adthrive-in-post-video-player video-id=”6hTmMonR” upload-date=”2024-01-10T14:57:42.000Z” name=”How to Make a Drop Down List in Google Sheets” description=”To make a drop down list in Google Sheets, choose your cell and select the insert menu. Then you can choose the dropdown option. My video also shows you how to use other data to automatically create options for your menu.” player-type=”default” override-embed=”default”]
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.
- Go to Data > Data Validation.
- That brings up a menu box. There, change the “Allow” option to “lists”.
- Then, update the “Source” option to include your choices.
- 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.
- Select a cell where you want to add the drop down list.
- Go to the โInsertโย menu and select the โDropdownโ option.
- You can also use the โData Menuโ or the โright-clickโ method.
- In the criteria section, click the drop down and choose โDropdown (from range).โ
- Click on the โData rangeโ button (window icon).
- Select your dataset range, and click โOK.โ
- 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:
- Tap the cell you want to add to the drop down list.
- Click the vertical three-dot menu.
- Choose โData Validation.โ
- Under the โCriteriaโ options, chooseย โList of Items.โ
- Add the options in the items section and separate the options by using a comma.
- Toggle on the options you want to include in your list.
- 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:
- Click and hold the small circle at the bottom right of the cell.
- 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:
- Select the cell with the drop down list.
- Click on the โEditโ setting (pencil icon) at the end of the list.
- 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:
- 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:
- Select the cell with the drop down list.
- Click on the โEditโ icon (pencil sign) at the end of the list to open the โData validationโ window.
- There are gray circles next to the drop down options, allowing you to customize the colors.
- Customize your options according to the colors.
- 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:
- Select a cell.
- In the top bar menu, click โInsert.โ
- Select the โDropdownโ option.
- 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:
- Select the cell with the drop down list.
- Click on the edit icon at the end of the list to open the data validation window.
- In the window next to the options, there are gray circles. Click on the drop down.
- Choose the color you want to assign the option.
- Click โCustomizeโย to choose a specific color.
- Once youโre finished assigning the colors, click โDone.โ
- 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
- Google Support. AppSheet User Guide [Internet]. Google; [cited 2024 Jan 9]. Available from: https://support.google.com/appsheet/answer/10107325?hl=en
- 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
- MIT. Formula 1 Spreadsheet Solutions [Internet]. MIT; [cited 2024 Jan 9]. Available from: https://www.mit.edu/~mbarker/formula1/f1help/04-data9.htm
- 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:
5 thoughts on “Google Sheets Drop Down Lists: Complete Guide”
Hi!
Is there a possibility to create a dropdown list where multiple options can be chosen (for example two different options)?
Great tutorial! Can you show how to use Data Validation from another Spreadsheet (without importing its range)?
The purpose is to separate and hide the source of the data from the user selection.
I made a dropdown with 5 different options. Now I wanted to make a formular, that is reading the value out of the dropdown field, but nothing happenes. Is there a trick to get the value from the dropdown or doesn’t it work at all?
Can I create a dropdown only using formulas in sheets?
No