1. Create dropdowns from lists
Dropdowns are a kind of data validation available through Google sheets that are useful when importing categorical data. In this lesson, we will explore how to implement dropdowns and their use in mitigating bad data entry.
2. Find the issue
We want to figure out what is wrong with the data from our fictitious marketing team.
Take a few seconds to try and figure out the issue.
3. Find the issue
The issue is just a difference in casing, which does not seem to be a major problem. However, it will categorize the highlighted rows differently and the data will not aggregate correctly.
We need to figure out how to limit the entries by providing a finite list of options for the user to select from.
4. Data validations: dropdowns
Data validation dropdowns are the best way to provide a list of options, and there are two types.
The first is a list from a range. The options are generated from a selected range of cells, which is best suited for larger lists that are modified often.
The second is a list of items. The options are entered manually as comma separated values in the data validation settings, which is helpful when there are fewer options to choose from.
5. Dropdown: list from a range settings
To set up a dropdown, we start by selecting the range of cells we wish to add the dropdown to, then right click, and add a data validation to the selected range.
For a dropdown that is a list from a range, you will need to define the range that the options will come from.
In this example, you may notice that there are two occurrences of Item 2. However, list from a range only shows unique values. This ability to filter out duplicates makes the dropdown particularly helpful when multiple parties are contributing options to the list, since it reduces confusion about which option to choose.
6. Dropdown: list of items settings
For a dropdown that is a list of items, we enter the comma separated values, which will be the options, and check the "Show dropdown list in cell" checkbox. Here, the dropdown options are Item 1, Item 2, and Item 3.
7. Dropdown: usage
In this example, instead of Items 1, 2, and 3, our campaigns are the dropdown options: DataCamp Brand, R Users, and Python Users.
This may seem like a great fix, but what if one of the marketing teams has a different campaign naming scheme or if something is misspelled?
8. Dropdown: bad data entry
Recall, cells that do not meet the data validation criteria will be flagged. The cells that are flagged can be resolved by simply selecting the best fit value from the dropdown.
9. Let's get to work!
Dropdowns are a great tool to use when a column only has a finite number of categorical options so users can simply select the best fitting category, rather than risk making mistakes. Let's get to work and practice using dropdowns!