Drop-down lists are common in spreadsheets like Google Sheets.
It allows you to specify choices that users of your Google Sheets can make without having to type them themselves. It makes displaying choices for users very easy.
This guide will walk you through the simple steps of creating a drop-down list in Google Sheets.
Without wasting much time, let’s dive right in.
Table of Contents
Two Ways to Create a Drop-Down List in Google Sheets
As you may already know, a drop-down list in Google Sheets is a graphical control feature that allows users to select one item from a list.
This feature is very useful if you want users to select an item from a list rather than typing their own values, which may result in incorrect data entry.
Examine the drop-down menu in action in the illustration below:
As you can see, it allows you to control exactly what can be entered into the cells in Google Sheets. As a result, the drop-down menu has become a very common and powerful data validation tool in spreadsheets and on the web.
Fortunately, if you know the exact steps, creating a drop-down menu is a breeze.
In a few ways, I’ll show you simple steps you can use to create your own drop-down list in spreadsheets, specifically Google Sheets.
Method One: Creating your Own custom Drop-down list
The only difference between this method and the next is how the drop-down list items are specified.
This option demonstrates how to manually type the items that will be displayed when a user clicks on the drop-down menu.
The steps below will walk you through the entire process.
Step 1: Launch your Google Sheets
Open Google Sheets and the spreadsheet to which you want to add the drop-down list.
Step 2: List your Drop-Down Items somewhere
If you already know offhand what items you want to include on the list, you can skip this step.
Before you can make a drop-down list in Google Sheets, you must first have the data that will be displayed when someone or you click on the drop-down.
With this manual method, you must either have all of the data ready in your mind or list them somewhere, such as a notepad, for easy access.
A screenshot of my “drop-down list” items is shown below. It is a list of employees in a business:
You could also jot them down on a piece of paper or something.
Step 3: Identify your Drop-down cell or cells
Choose the cell or cells that will be used for the drop-down menu.
If you want it on a single cell, click on it to make it active. For a range of cells, click and drag over all of them.
In this example, our drop-down list will be assigned to cell D3.
Step 4: Go to Data
This step is a no-brainer.
Simply move your cursor to your Google sheets Menu section and click on the Data menu.
Step 5: Select Data Validation
When you click the Data menu, you should see all of the items in the Data menu listed in a drop-down list.
Look through the list and select Data validation.
The Data Validation window will appear immediately.
Step 6: Next to “Criteria,” choose “List of Items”
Once the Data validation window appears, it’s time to make the necessary settings.
The cell range is the first item on the Data validation window. This is where you’ll specify which cell or cells will house the drop-down list. We already did that in step 3, so there’s nothing else to do here.
The Criteria settings come next, and this is where we will finally specify our list of items that will appear on the drop-down list.
Select “List of Items” as the criteria, as shown in the screenshot below.
Following that, a text box will appear in which you can enter the individual items. Enter the items in the text box, separated by commas and with no spaces between them.
You can either copy and paste it or type it in manually, as shown in the screenshot below:
Step 7: Click to select “Show drop-down list in cell”
The next setting under the Criteria is the option to show the drop-down list icon in the cell.
Simply click on the checkbox to enable this feature.
When this option is selected, the drop-down list arrow will appear inside the cell to alert users that there is a drop-down list in this cell.
If this is disabled, however, the drop-down icon is hidden, and you can’t tell that the cell has a drop-down list unless you double-click inside it.
It is recommended to always enable this feature if others are going to use your spreadsheet.
Step 8: Decide what happens when wrong data is entered
This feature is very valuable in Google Sheets.
It allows the spreadsheet creator to set rules for whoever is going to use the spreadsheet.
The two settings you can make here are Show warning and Reject input options.
The Show warning option will notify the user that the input item does not belong to the specified list. This will help to improve data entry accuracy.
The Reject input setting will only allow users of your spreadsheet to only enter items from the list. It prevents incorrect data from being entered into a drop-down list cell. When Google Sheets receives incorrect data into the list cell, it will reject it by displaying an error message similar to the one shown in the screenshot below.
Always be aware of the type of action you want to take in response to invalid data entry and select one of these options accordingly.
Step 9: Set instructions for the Drop-down list
The next and final setting for your drop-down list is the “Show validation help text” checkbox.
This feature allows you to give instructions in your spreadsheet so that users will know what is expected of them.
Simply click on the “Show validation help text” and a text box will appear for you to type in your instructions.
Now whenever someone clicks on the cell or cells, a tooltip should appear with the instruction you specify for it.
Step 10: Click the Save button to finish
Once you finish all the necessary settings, click on the Save button to apply all the data validation settings you’ve made.
You should now be able to use the drop-down list feature in your spreadsheets.
Note: Although you can create a drop-down list on the Google Sheets app on mobile, it is recommended that you do so on a computer. After it has been created, you can use the drop-down list on your phone.
Method Two: Using a range of Cells as List items
In the previous method, we manually entered all of the items we wanted in our drop-down list.
In this method, we’ll show you how to get the same results by using an existing list in the spreadsheet.
This is especially useful if you have a large number of items to add to the drop-down list. It will save you time typing all of the items.
So, let’s get started with the steps.
Step 1: Prepare the data list
This is the data we want to see when we open our drop-down list.
You can type or paste this data on the same sheet or a different sheet.
I’d create two sheets; one will contain the data and the other will have the drop-down menu.
The screenshot below has the datasheet.
There’s also the data entry sheet, where we’d like to be able to scroll through all of the names or items.
Let’s specify cell D3 to contain the drop-down menu.
Step 2: Go to Data
This step is a no-brainer.
Simply move your cursor to the Google Sheets Menus and select the Data menu.
Step 3: Select Data Validation
When you click the Data menu, you should see a drop-down list with all of the items in the Data menu listed.
Select Data validation from the drop-down menu.
The Data Validation window will immediately appear.
Step 4: Next to “Criteria,” choose “List of Items”
When the Data Validation window appears, it is time to make the important settings.
The first item in the Data validation window is the cell range. This is where you’ll tell the program which cell or cells will host the drop-down menus. That was completed in step 3, so there is nothing else to do here.
The Criteria settings are next, and this is where we will finally specify the items that will appear in the drop-down list.
Select “List from a range” as the criteria. This means that we are selecting a range of cells as the source of the drop-down list.
Here’s where the datasheet comes in. That is the sheet where we listed the names of the employees.
We’ll tell Google Sheets to pull our drop-down list items from that data in the field labeled “Enter a range or formula.”
There are two approaches to this. One method is to manually enter the range if you know it. In this case, the list’s range is ‘Data Sheet’! A1:A7.
Don’t be worried if determining the range proves difficult for you.
Simply click this four-square icon in the field labeled “Enter a range or formula”:
When you click on this icon, you’ll be taken to a small field where you can specify the data range.
Simply click the “Data” sheet and select all of the employee names.
When you do this, Google Sheets will insert the reference for you.
Step 5: Click on the Save button
Once you’ve specified the range to serve as the list’s items, complete the remaining settings as described in the previous method.
After that, simply click the save button.
The final result is shown in the illustration below:
How to copy and paste Google sheets drop-down list
There may be times when you need to copy and paste the drop-down list so that the same list and formatting applies to other cells.
This will save you time from having to go through the entire process again.
So, to copy and paste the drop-down list, simply click to select the cell that already has the list. Then click on the cell without the list and hit Ctrl+V to paste.
All data validation and formatting will be applied to the new cell or cells in this manner.
You can also use the paste special function to copy just the drop-down list and leave out the formatting. To do so, first, copy the cell that has the drop-down menu, then go to Edit > Paste special > Paste Data validation only in the new cell.
This will paste only the data validation without formatting.
How to edit/remove drop-down list in Google Sheets
You followed these simple steps to quickly add drop-down lists to your Google Sheets.
And you’ve just realized that there are some items on the list that you don’t want to include or that you want to add.
Or perhaps you simply want to remove the drop-down list for some reason.
Don’t worry, the steps to accomplishing all of this are simple.
Follow the steps below to change or remove a drop-down list in Google Sheets:
Step 1: Select the cell or cells that have the drop-down menu
If it is a single cell, just click on it.
For multiple cells, click and drag over the range of cells with the drop-down.
Step 2: Go to Data > Data validation
On the menus, click on Data. You should see a drop-down list a list of items including Data validation.
Select Data validation.
The data validation window will appear immediately.
Step 3: Make your changes
If you are making some edits to the drop-down items or other settings, do so once the data validation window appears.
You can change the drop-down items by specifying a new data source or by entering the items manually.
Step 4: Removing the drop-down list from your Google Sheets
If you just want to get rid of the drop-down list you can do so easily.
On the Data validation window, simply click on the Remove validation button at the bottom.
Once you hit on this button, the list will disappear from your Google sheets.
Ensure that the cells with the drop-down menus are selected before taking this action.
This is how you may change and delete the drop-down list in Google Sheets.
That concludes the step-by-step procedures for successfully creating a drop-down list in Google Sheets. Isn’t it clear and lovely how the steps are laid out?
With a little practice, you’ll be able to create your drop-down list almost instantly.
Thank you so much for taking the time to read this page. Don’t forget to check out the rest of the Google Sheets tutorials on this site. It is jam-packed with fantastic guides.
Other Google Sheets Resources You May Find Useful
- How to Create Data Entry Form in Google Sheets
- How to Make a Bell Curve in Google Sheets
- How to Create a Combo Chart in Google Sheets
- How to Create a Timeline Chart in Google Sheets
- How to Create Dynamic Chart Range in Google Sheets
- How to Create an Area Chart in Google Sheets
- How to Create Pivot Table in Google Sheets
- Google Sheets Conditional Formatting Custom Formula (with Examples)