How to Add Drop Down List in Google Sheets?


Do you want to know how to add a drop-down list on your Google Sheets? Do you even know what they are? Learn about this feature through this handy article!

If you’re dealing with repeatable data on your spreadsheet, it’s only prudent to make sure they couldn’t be entered manually since that could result in typographical errors and invalid data input. To reduce human errors, you can make dropdown, drop-down, or drop-down menus for such items.

Where can you use these dropdown menus? Use them when asking things like “U.S. State (where you’re from)”, “Sex/Gender”, “Marital Status”, or any limited set of answers.

Let’s therefore discuss how to add drop-down list in google sheets down below! You can now make the dropdown lists you see in email or account registrations on websites with ease on your Google Sheets spreadsheet.

Why Add Drop-Down Lists in Google Sheets?

Drop-down lists keep human errors from invalidating the data on your electronic spreadsheet. It’s one of the most efficient ways to ensure people enter data you want to put into the sheet. Offer a dataset as a preset and they can just go about it on a multiple-choice manner!

You can even have a dropdown list for the different spellings of “dropdown” you can use. In this article alone, you can choose between dropdown, drop-down, or drop down.

You should be aware of what drop-down lists are by now. If you’ve ever come across an online questionnaire or registered an email, you’re sure to see these preset lists you can click to give you a menu of options.

Types of Data Validation

By the way, there are two types of data validation or criteria for your Google Sheets drop-down lists.

  • List of Items: This option—separated by commas—allows you to set a handful of preset options for your dropdown list. This hardcoded data option doesn’t support dynamic updates or insertion of the latest items, thus necessitating regular manual updates.
  • List of Range: This more dynamic solution allows you to list items on a separate cell and then add additional options into the dropdown through the separate cell so that you won’t have to update the whole range of cells every time you wish to add a new item.

How to Add Drop Down List In Google Sheets

Step 1: Open a Google Sheets spreadsheet, preferably one with tables and data included.

Drop Down Menu Step 1 - Open Google Sheets

Step 2: Scroll down and choose the data you wish to turn in as part of the drop-down menu. In this case, we’ll be adding a new “Status” header and data list to this table.

Drop Down Menu Step 2 - Make new header and select cells

Step 3: Now click the “Data” menu and select “Data validation”.

Drop Down Menu Step 3 - Go to Data then Data validation

Step 4: You should end up bringing up the Data Validation dialog box or pane. Make sure the “Show drop-down list on cell” option is checked before heading to the criteria dropdown list.

Drop Down Menu Step 4A - Data validation dialog box appears

Right beside the criteria is a drop-down list. Your two choices here relative to this guide are “List of items” and “List of range”.

Drop Down Menu Step 4B - Pick between List of items or List of range

By the way, for the “Status” data list (as in the status of this list of articles to be written), the user can pick from the following.

  • Pending
  • Completed
  • Approved
  • Rejected
  • For Revision
  • Paid
How to Add Drop Down List in Google Sheets
How to Add Drop Down List in Google Sheets

Method 1: List of Items

Step 1: If you’ve chosen “List of items”, you ostensibly need to put up the list of items on the box right beside the dropdown menu. The one that says “Enter items separated by a comma(,)”.

Method 1 Step 1 - Choose list of items

Step 2: Based on the list above, enter “Pending, Completed, Approved, Rejected, For Revision, Paid”. In other words, it’s the list above separated by commas but with no spaces. Afterwards, press “Save”.

Method 1 Step 2 - Add items separated by commas no spaces

Step 3: Every cell you’ve selected on your new header should now have a drop-down menu listing down all the options you’ve included on that list!

Method 1 Step 3 - The cells all have drop-down menus now

Method 2: List from a Range

If you want to make it easy to update your list of options without selecting all those cells again and entering a new list of items for your data validation dropdown lists, do the following.

Step 1: Make a new sheet on your spreadsheet and add that list of items in one column. On the other column, you can even add descriptions if you want.

Method 2 Step 1 - Add new list with your list of items for the Status header

Step 2: Select the whole column containing your list (Column A, in this case) then go to the “Data” menu in order to select the “Named ranges” option.

Method 2 Step 2 - Select column with data then select Named ranges

Step 3:  A new window should appear on the left side of the spreadsheet. Give a proper name on the name range box, something like “Article_Status” (with underscore included). Click “Done” once you’re through.

Method 2 Step 3 - Name the Name range properly

Step 4: Go back to the other sheet you were finagling with. Select the cells you wish to add dropdown menus to again. Go to the “Data” menu and select “Data validation” once more.

Method 2 Step 4 - Go back to original sheet and select Data validation again

Step 5: On the Data validation window, right beside “Criteria”, select “List from a range” from the drop-down menu and add the Name range you named earlier (which in this case, is “Article_Status”).

Method 2 Step 5 - Select List from a range and add the Name range you named earlier

Step 6: This should result in the same result as earlier but with a twist. More on this in the next step.

Method 2 Step 6 - Result is the same but with a twist

Step 7: Remember the other sheet? Every time you add an additional status on your “Article_Status” named range, this reflects on your dropdown menu without you having to manually update each and every menu every time.

Method 2 Step 7A - Change the dropdown menu options at will

You can add items, delete items, or overhaul the choices at will. They’re automatically updated based on what you’ve entered on the other sheet. This results in a scalable and dynamic sheet when push comes to shove!

Method 2 Step 7B - The changes of one sheet will reflect on the other sheet

Isn’t that cool? That’s what digital spreadsheets are supposed to do. They’re supposed to automate data entry rather than make it more complicated.

So Essentially

So essentially, go to “Data” and “Data validation” then pick “List of items” or “List from a range” in order to put in drop-down lists for a given data set, whether it’s to check on the status of your list of pending articles or any other limited set of data you want entering properly with no errors.

Although the “List from a range” is a lot more complicated to do and involves a separate sheet and naming the range altogether, it’s a worthwhile solution down the line when you want to make dropdown menu adjustments in the future without manually changing every cell.

References:

  1. Create an in-cell dropdown list“, Google.com Support, Retrieved June 18, 2022
  2. Amy Brink, “How to Create a Dropdown List In Google Sheets : Best Tips 2022“, AtozGoogleDocs.com, April 9, 2021
  3. HowTech Tutorials, “How to Create a Drop-Down List in Google Sheets | Create an In-Cell Drop-Down List | Drop-Down Menu“, YouTube, July 6, 2021

Andy Avery

I really enjoy helping people with their tech problems to make life easier, ​and that’s what I’ve been doing professionally for the past decade.

Recent Posts