Using dropdown lists in Google Sheets is an excellent way to make your spreadsheet interactive. This feature helps provide a seamless experience when categorizing or adding data. Instead of writing out the same thing repeatedly, adding a dropdown list is a better way to encode information.
Now, there are about two ways you can create a dropdown list in Google Sheets.
2 Ways to Create a Dropdown List in Google Sheets
- Create dropdown list using a range of cells
- Create dropdown list by manually specifying
We’ll cover both of these methods in the guide down below and give you extra tips in regards to copy-pasting dropdown lists. At the same time, we’ll also expound on the importance of having a dropdown list in your spreadsheet. If you’re interested in figuring out how to edit dropdown lists, we’ll go over that as well at the end of the article.
With that said, don’t feel intimidated going into this article. This guide was written with beginners in mind, so it should be relatively easy for you to follow the steps. To guide you even further, we’ll provide copy links down below so you can create a copy of the spreadsheets used in this method.
Let’s dive right in!
Why Should I Create A Dropdown List In Google Sheets?
There are many reasons as to why creating a dropdown list is an excellent addition to your spreadsheet. For one, the dropdown list streamlines everything. Since the dropdown list has a limited number of options you can choose from, you’ll be able to guarantee that every entry in your column or row is correct.
You’ll also have the satisfaction of making your spreadsheet easier to encode. At the same time, this lets you avoid errors that would otherwise stem from misspelt entries. Overall, there are a lot of perks that come with using a dropdown list.
A good rule of thumb as to when you should create a dropdown list is when:
- You need a status cell with a dropdown list where that column only has a set of unique possible entries (e.g. complete, incomplete, or pending).
- You need a dropdown list with all the employees you have, so you can simply click and assign without typing their names out.
These two instances are the most basic examples of when having a dropdown list would be more than ideal. Of course, there are definitely a lot of other cases as to when a dropdown list is useful, but that will have to be up to your judgment.
That being said, here’s how you can create a dropdown list in Google Sheets.
Method 1: Create Dropdown List Using A Range Of Cells
In this method, we’ll be creating a dropdown list using an already existing range of cells. This works by creating a range of cells with the options we want present in our dropdown list.
Here’s how you can do that.
Step 1: Open up your Google spreadsheet file.
Let’s start by opening up a spreadsheet file on Google Sheets. You can either create one from scratch or use an already-existing spreadsheet that you’d like to edit. Alternatively, you can also create a complete copy of the document used in this method so you can follow us at every step of the way.
Step 2: Click on the cell(s) where you want to add a dropdown list.
Next, highlight or select the cell where your dropdown list is meant to be added into. For the purposes of this tutorial and for the ones following the spreadsheet provided above, click on A4. You can choose to drag this down as far as you want. Don’t feel pressured about not creating enough dropdown lists, as you will be able to copy-paste this later on.
Step 3: Create the dropdown list with existing cells.
With those cells selected, click on Data and select Data validation. If you’re relatively new to Google Sheets, this might seem intimidating since there are a lot of options to fill out. Despite that, you have nothing to worry about! The process here is fairly simple, just follow these steps. Next to Criteria: make sure that the dropdown option selected is List from a range.
With that done, you can click on the four-square icon at the right of the Criteria: box. This should allow you to select a data range. Now, you can either type it out or simply drag it from the spreadsheet. If you want to type it out, then type in ‘F4:F7’ for the sake of this tutorial. Alternatively, you can also simply drag the cells on the spreadsheet.
When that’s done, you should be redirected back to the Data validation window. Simply click on Save to finalize and set your dropdown list. Your previously selected cells should now look like this:
Step 4: Select an option from the dropdown list.
Finally, go ahead and click on the arrow pointing downwards to expand the dropdown list.
Alternatively, you can also click on the cell itself and just type out the option you want to choose. This will bring out a search field that will look for the text you just typed in through the available options in the dropdown list.
Congratulations! You’ve successfully created a dropdown list in Google Sheets! Moving on, we’ll be going over another way you can create a dropdown list before we teach you how to edit and copy-paste dropdown cells.
Method 2: Create Dropdown List By Manually Specifying
You don’t have to make a list of the dropdown options in your spreadsheet, like the Status Legend column in the method above. We understand that listing down a legend might look messy, so here’s an alternative way of setting up a dropdown list.
Thankfully, Google Sheets allow you to manually specify the items in your dropdown list right from the Data validation window. Here’s how you can do just that.
Step 1: Open up your Google spreadsheet file.
First off, go ahead and open up a Google Sheet spreadsheet file to get started. You can either create a new one from scratch or use an already-existing one that you own. We’ll also provide a copy of the spreadsheet used in this example so that you can follow the step-by-step process with an identical document.
Step 2: Select the cell(s) where you want the dropdown list to be.
For the next step, we’ll be highlighting the cells designated for the dropdown list. If you created an exact copy of the document provided above, then select cells A4:A7. However, if you’re using your own personal document, then this step will have to be done at your own discretion.
Step 3: Manually input the dropdown list elements.
Similar to the previous method, click on the Data tab at the top part of your screen while your cells are highlighted. From there, click on Data validation.
Now, click on the dropdown menu next to Criteria: and select List of items. This is the option that will allow us to manually specify the options we want on the dropdown list without taking it from the spreadsheet. Go ahead and enter the elements that you want. However, for the ones following through on the sample document, type in ‘To Order, Pending, Shipping, Received’. The comma is the separator here.
Click on Save to finalize and set the dropdown list into your spreadsheet.
Step 4: Click on the dropdown list and select an entry.
That should be it! Click on the inverted arrow icon and select the option you want to enter!
Voila! You’ve successfully created a dropdown list without having to use a range of cells in the spreadsheet.
Copy-Pasting Dropdown Lists
Before we go to the conclusion, let’s first figure out how to copy-paste dropdown lists. While it might not seem like a problem, there’s an issue with copying dropdown lists that might bug you later on. You see, if you copy-paste a dropdown list with a field that was already chosen, you’ll end up copy-pasting the dropdown list with that chosen field as well. Here’s what we mean:
We copy pasted the dropdown list that was already chosen with “To Order” and that ended up copy-pasting the To Order entry as well. While that might not seem like a problem, the issue here lies in the fact that you can no longer revert these lists into a blank copy easily.
To fix that, you’ll have to copy-paste the format of the cell instead. Simply do that by copying the cell the normal way. You can do this by clicking on it and pressing Ctrl + C. After that, make a selection of where you want to copy the dropdown list into and right-click those cells. Go to Paste special and select Paste data validation only.
Doing this will allow you to copy-paste the dropdown lists as completely blank cells. If that’s too much for you, then you can always just highlight the copy-pasted cells with the “To Order” entry and click on the Delete key from your keyboard.
Deleting or Editing a Dropdown List
Now, what if you wanted to edit a dropdown list or delete on entirely? If you tried to simply press the Delete key, you’ll find that doing so will delete the entry, but not the dropdown list. While this is a convenient way of clearing out dropdown entries, there’s a dedicated step to deleting and editing a dropdown list properly.
You can do this by highlighting the dropdown cells and right-clicking your selection. From there, scroll down and click on Data validation.
That should open the Data validation window. Once you’re there, you can simply edit your elements easily or completely remove the dropdown list by clicking on Remove validation.
Now that you’ve reached the end of this article, hopefully, we’ve helped you figure out how to create a dropdown list in Google Sheets. Here’s a brief summary of the main topics we’ve discussed.
Creating dropdown lists in Google Sheets is a fantastic way of providing interactivity to your spreadsheet. This also reduces the complexity of the spreadsheet when conveying and managing the data stored. You can create a dropdown list in two primary ways. First, you can use pre-defined data within a range of cells as the dropdown list items. Second, you can also provide your own data values for the dropdown list manually. Both are equally useful ways on their own right, and learning both of these methods can come in handy the next time you manage a spreadsheet.
We hope you found this article helpful!