How to Make a Google Sheets Drop Down List

Google Sheets Sample Drop down List

You can use this example spreadsheet to see both the settings and demo implementation of How to make a Google Sheets Drop Down List.

Why Make Drop Down Lists in Google Sheets?

We make drop down lists to control the user experience and user interface. By giving a drop down list users are limited to selecting from a predetermined list of options. We also make drop downs to control input and data typing, this prevents multiple spellings or variations from polluting your reports.

Prevent Bad User Experience

Let’s say you want to know which Employee to pull a record for, if the field you give your user is just a free text box they might spell the name incorrectly resulting in no record being found. This would be a bad user experience as the user had the right intentions but will now be frustrated because they have to double check their spelling.

Superior User Interface

Using drop downs provides important feedback for your user. First off, it tells them the entire range of options they can pick from. Second, it can be sorted and searched. In our employee name example the user may know the name of the employee starts with J and can either type the letter “J” or scroll to the J names alphabetically. From here it will be much easier to FIND the employee in question. If the user doesn’t know how to spell the name OR maybe does know how to spell the name and can see the name in the drop down is wrong you now have a better chance of catching and correcting errors automatically.

Create Reporting Buckets for Histograms and Tags

When we need to tag data for making a histogram or report the last thing we want to do is let the user enter in this information manually. Every spelling variation and mistake that your user puts into a free text field is a data problem for someone down the road. Let’s say you wanted users to mark which market they made a sale in. If they incorrectly typed in “Misouri” instead of “Missouri” on a big sale it wouldn’t appear on your reports, dashboards, or pivot tables correctly. Using a drop down allows us to force users to tag things with the tags we defined making reports easy and consistent.

What Kinds of Drop down List can Google Sheets make?

There are two kinds of drop down list that Google Sheets supports: Text and Referenced Ranges. In a text list you are basically just typing out the list of items you want separated by commas. If a referenced list you can choose any range in the spreadsheet to fulfill your drop down. Referenced lists are very powerful as they can be sorted and dynamically update each time the user opens the drop down.

Why is Drop down List in Data Validation?

Drop down lists in Google Sheets is located under Data -> Validation in the menu system. This makes sense from a programming and data management perspective but may be very unintuitive for a UX/UI designer or a laymen trying to add drop downs for convenience. When it comes to the actual programming function of the drop downs they exist to force consistency of choice to a selected pool of options. At its heart this is a data validation process: is item X in List: A, B, C, ….. N. Where X could be anything and the list may or may not contain X.

How to make a Drop down List from Text in Google Sheets?

1. Highlight the cell or cells you want to add a drop down to
2. In the top menu go to Data -> Validation
3. You can edit or override the range of cells you want the drop down to appear on, it can be anything from 1 cell to an entire sheet
4. For Criteria Pick “List of Items”
5. Type your items (separate each entry with a comma)
6. Ensure you pick “show drop down list in cell”
7. Pick if you want to outright reject invalid selections or just warn against them with an error marker on the cell
8. Choose if you want any custom validation text

How to make a Drop down List from a Range of Cells in Google Sheets?

1. Highlight the cell or cells you want to add a drop down to
2. In the top menu go to Data -> Validation
3. You can edit or override the range of cells you want the drop down to appear on, it can be anything from 1 cell to an entire sheet
4. For Criteria Pick “List from Range”
5. Select or manually enter your Range
6. Ensure you pick “show drop down list in cell”
7. Pick if you want to outright reject invalid selections or just warn against them with an error marker on the cell
8. Choose if you want any custom validation text

Learn More About Google Sheets

Google Sheets is a powerful and fee tool for organizing and analyzing data. Check out our other Google Sheets articles to learn how to become a spreadsheet expert – https://nathanielkam.com/tag/google-sheets/

Summary

Article Name

How to Make a Google Sheets Drop Down List

Description

Learn How to Make a Google Sheets Drop Down List using data validation tools in spreadsheets. You can make dropdown lists with text or cells.

Author

Nathaniel Kam

Publisher Name

Nathaniel Kam