Have you ever wanted to create a drop down list in Google sheets so that you or someone else can choose from a list of multiple different options instead of typing into the cell? Well, in this article, that’s exactly what I’m going to show you how to do.
Prefer to watch a video about how to create a drop down list in Google Sheets? Click here.
How To Make A Drop Down List In Google Sheets
To create a drop down list in Google Sheets, the first thing you’ll need to do is decide whether you want the options in the drop down list to come from a range of cells on the spreadsheet, or from a comma separated list of items you’ve typed into a box.
I would probably recommend using the comma separated list because it keeps your spreadsheet nice and tidy and really gets the list options out of the way. But if you want to do something fancy with equations or you want to be able to easily change the options in the drop down menu, you might want to go with the range of cells.
First, I’m going to show you how to use the comma separated list because that’s what I imagine most people will want to do. But if you’ve decided to use the selection of cells instead, you can click here to skip down to where I show you how to do that.
Using A Comma Separated List
To create a drop down menu with options from a comma separated list, you’ll first need to select the cell(s) where you want the drop down list to appear.
If you want the same drop down list to show up in several different cells, you can click and drag to highlight all the cells you want it to be in.
Or, if you just want to insert your list in one specific cell, you can just click on that specific cell.
Then, once you’ve selected the cell(s) you want the list to be in, click on “Data” in the menu across the top of the screen…
And then, in the menu that appears, click “Data validation”.
Now, a box will appear on the screen, and this is where you’ll get to start creating your drop down list. And the first thing you’ll need to do here is set the “Criteria”.
By default, the criteria will be set to “List from a range”, and that would mean you need to select a range of cells on the spreadsheet where the options in your drop down list will be.
But to enter a comma separated list of options to go in your drop down list, you’ll need to click on the drop down arrow next to “List from a range”…
And then, in the menu that appears, select “List of items”.
Now, in the box next to that drop down menu, you can type out all the different options you want to include in your drop down list. Just make sure you separate each item with a comma as shown below.
And that’s pretty much all you need to do. This “Data validation” feature is able to do a lot more than just create drop down lists, so there are a few settings that don’t really apply to drop down lists, and you can really just leave all these set to default.
The box that says “Show dropdown list in cell” will be checked by default, and you’ll want to make sure you leave it that way or else you won’t actually get a drop down list.
And the “On invalid data” section is not very relevant for a drop down list so you don’t really need to change anything there. It just means if someone tries to type something in this cell that is not on your drop down list, you can choose to either show a warning or reject the input completely.
You can set this to “Reject input” if you want, but I would just leave it set to “Show warning” so that anyone who tries to type something different in the cell will get a little warning.
There’s also a checkbox labeled “Show validation help text”, which will be unchecked by default. And this option really doesn’t apply to drop down lists so you should really just leave this one unchecked.
But now, once you’ve set all the settings correctly, you can go ahead and click the “Save” button in the bottom right hand corner…
And your drop down menu will be created!
So now, you will notice a little arrow inside the cell(s) you selected.
And, if you click on the arrow, you will see a drop down menu with all the options you put in the comma separated list!
And if you click on one of the options in the list…
It will be inserted in the cell!
And, if you have a fairly long drop down list, you can start typing to find things faster. So if, for example, you we’re looking for the option that said “Google Drive”, you could start typing goo in the cell, and then you would only see the options in the list that begin with goo.
And that’s all there is to it!
Using A Selection Of Cells
To create a drop down menu using a selection of cells, the process if fairly similar, but it is slightly different.
First, you’ll need to choose where you’re going to type out all the different options for the list. I would recommend somewhere near the edge of the spreadsheet or perhaps quite far down to keep it out of the way, but it can be anywhere you like on the sheet.
Once you’ve chosen where you’re going to type all the list options, go ahead and start typing them all in your chosen location with one cell per item.
Now that you’ve typed out all your list items, you’ll need to select the cell(s) you want the drop down list to appear in. If you want the same drop down list to appear in several different cells, you can click and drag to highlight all the cells you want…
Or, if you just want to insert the drop down list in one single cell, you can just click on that cell to select it.
Once you’ve selected the cell(s) you want your drop down list to be in, click on “Data” in the menu across the top of the screen…
And then, in the menu that appears, click “Data validation”.
Now, a box will appear on the screen, and in this box, you’ll need to click on the little icon that looks like four squares next to where it says “Enter a range or formula”…
And then, you’ll see a different box that says “Select a data range”, and you’ll just need to click and drag to highlight the cells that you typed your list in before.
Now, the list you typed will be highlighted, and the cell range will be automatically entered into the box. So now if you just click the “OK” button, the range of cells will be set.
Alternatively, you could just type the cell range into the box (eg: K1:K4).
And that’s pretty much all you need to do. This “Data validation” feature is able to do a lot more than just create drop down lists, so there are a few more settings that don’t really apply to drop down lists, so you can really just leave all these set to default.
The box that says “Show dropdown list in cell” will be checked by default, and you’ll want to make sure you leave it that way or else you won’t actually get a drop down list.
And the “On invalid data” section is not very relevant for a drop down list so you don’t really need to change anything there. It just means if someone tries to type something in this cell that is not on your drop down list, you can choose to either show a warning or reject the input completely.
You can set this to “Reject input” if you want, but I would just leave it set to “Show warning” so that anyone who tries to type something different in the cell will get a little warning.
There’s also a checkbox labeled “Show validation help text”, which will be unchecked by default. And this option really doesn’t apply to drop down lists so you should really just leave this one unchecked.
But now, once you’ve set all the settings correctly, you can go ahead and click the “Save” button in the bottom right hand corner…
And your drop down menu will be created!
So now, you will notice a little arrow inside the cell(s) you selected.
And if you click on the arrow, you will see a drop down menu with all the options you put in the other selection of cells.
And if you click on one of the options in the list…
It will be inserted in the cell!
And, if you have a fairly long drop down list, you can start typing to find things faster. So if, for example, if you were looking for the option that said “Google Drive”, you could start typing goo in the cell, and then you would only see the options in the list that begin with goo.
And that’s all there is to it!
How To Delete A Drop Down List In Google Sheets
Regardless of the way you created your drop down list, if you ever want to remove it from your Google sheet, that is fortunately quite easy to do. Just click on the cell where the drop down list is (Or click and drag to select multiple cells).
And then, click on “Data” in the menu across the top of the screen…
And in the menu that appears, click “Data validation”.
Now, simply click the “Remove validation” button, and the drop down menu will disappear!
And that’s all there is to it! So now, whenever you want to add or remove a drop down list in Google Sheets, you’ll how it’s done.
