Are you looking for a Yes No type drop-down inside the Google Sheets? You are at the right place.
Data Analysts often come across a situation where they need to restrict the input in a specific cell or entire column to a mare Yes or No. For example, imagine you are a teacher and want to know whether your student has attended the lecture or not.
The general approach is to write down “Yes” or “No” in the column next to the student names.
But, there can be data quality issues in this way. Someone might type in YES, yes, Yes, Yeess, etc.
What if you restrict this by adding a drop-down list of your own that only shows two values, “Yes” and “No”? People then have to choose either one of those.
With this approach, you can potentially eliminate the data quality issue I mentioned above.
This article will introduce you to different ways of creating a Yes/No drop-down list in Google Sheets.
Example Dataset
For this article, I will be using the following dataset: five students and weekly dates. I want to create a drop-down list for each day of the week where the attendance can be captured through a Yes/No drop-down across cells B3:G7.
Using the Data Validation and List of Items
You can create a Yes/No drop down list in Google Sheets using the Data Validation and by choosing a custom List of Items. Let’s see how!
1. Select the range for which you want to add the Yes/No drop down. In this example, the range is B3:G7.
2. Click on the Data tab and select the Data validation option.
3. The Data validation rules window appears on the right-hand side of the sheet. Click on the + Add rule button.
4. You will see the range B3:G7 selected in Apply to range selection. The Criteria is by default set to Drop down. Below the Criteria are two List of items named Option 1 and Option 2. These are editable, and you can type in any item of your choice.
5. Replace the List of items Option 1 by Yes and Option 2 by No inside the Data validation rules window. Click the Done button to apply this rule to the selected range.
6. Now, go to your sheet, and you will see the drop-down applied to all the selected cells. Click on any of the drop-downs and you now have two options to choose from. If the student has attended the class on a specific date, choose Yes else choose No.
This is how you can add a drop down menu with the Yes/No type of inputs inside Google Sheets.
Pro Tip 1: Instead of hovering over the Data menu and clicking on the Data validation option, you can use the keyboard shortcut Alt + A + V (on Windows PC), and it will instantly open up the Data validation rules window on the right-hand side of your sheet. Unfortunately, I am not aware of the equivalent Mac shortcut key for the same (because I never worked on Macs), and users who know about it can share the same through the comment section with me!
Pro Tip 2: If you are lazy (like me) to even do this, you can right click on any cell and select the Drop down to add the same instantly in your selected cell. By doing this, you will potentially save three steps: click on the Data menu > select Data validation from the list > and Add rule inside the Data validation rules window.
Tackling Invalid Input inside the Drop down
It is often seen that irrespective of creating a drop down to restrict the users from adding their own text, which can be irrelevant in the context.
For example, in the above example, where you add a Yes/No drop down, the user can still add another input in any of these cells. Google Sheets will give a warning message that the input is invalid and should be from a list of selected items, but it doesn’t really restrict the user from adding it.
For example, see the example below:
These issues will compromise the data quality and consistency, the two pioneers for which you implemented the drop down list inside your sheet.
You can tackle this issue by amending a small setting inside the Data validation rules window. It will ultimately restrict the user from adding anything apart from the Yes/No drop down inside the selected cells.
1. Click on the Advanced options button in the Data validation rules window.
2. You will see two radio buttons inside the “If the data is invalid:” section. Select the second radio button named Reject the input. Finally, click on the Done button to amend the rule.
Now, if someone tries to add another text apart from Yes/No inside any cell where the drop down is added, they will get an error message, as shown below, that suggests that they should only add values from a predefined list of items: yes and no.
Moreover, Google Sheets will also reject the input they have added. Instead, they will see the Yes inside the cell.
This is how you should tackle with the invalid inputs from users inside the cells where the Yes/No drop down is added.
You can also customize how these drop downs are added in each of the demos through the Data validation method.
Customizing the Display style
You can change the way your drop downs are displayed on the front end. You have the option to choose from three different Display styles.
Go to the sheet where you have applied the Yes/No drop down using any of the methods described above. You will see on the right hand side the Data validation rules applied on the sheet. If you don’t see it, just use the Alt + A + V from your Windows PC or go to the Data tab and select the Data validation option.
1. Click on the rule to edit it.
2. Go to the Advanced options, and you will see the Display style option, which consists of three radio buttons.
- The Chip radio button makes the drop down look like stylized and a small element that looks like a tag or a label. When you hover the cells with drop down, there will be a small gray box like a tag, which sometimes gives a more stylized look.
- The Arrow style is the simple and standard style with just an arrow on the right-hand side rather than the tag, and clicking on the arrow reveals the values from your drop down.
- Finally, the Plan text radio button removes any additional graphical elements the Chip or Arrow style provides. Instead, to access the drop down list, you will have to double-click on any of the cells.
Customizing the Colors of Items in the Drop down
You can also customize the colors for each item from your list in the drop down. In this case, you have two items: Yes and No. You can give a Green color to the Yes and a Red color to the No.
Using these colors will give you a better visual representation, can also give you a status indication, and impasizes on the priority in some cases.
1. Inside the Data validation rules window, go to the lists you created. In front of each item, you will see a drop down. Expand the drop down before the item Yes and select the Green color as shown below.
2. Expand the drop down of item No and select Red from the list to represent it. Click on the Done button to apply the changes to this rule.
3. Now, if you select any item from the drop down list inside the cell, it will also show the colors you have added to them. For example, all the Yes values will be shown in Green color.
That’s how you can customize the appearance of the Yes/No drop down in Google Sheets.
Using the Data Validation and Range from Another Sheet/Same Sheet
In the last section, you saw how to add a List of items that create a yes/No drop down through the Data validation in Google Sheets. However, they are static, and in the future, if you decide to make any changes, for example, adding another category, “Maybe,” to the list, you will have to add it through the Data validation rule window manually.
For such cases, where users need constant modifications in the criteria definition, I suggest using the option Drop down(from a range).
The option allows the user to select a predefined list of values stored in the range of cells on the same sheet where you add a drop down or on another sheet and then link it to the drop down.
The most significant benefit of this method is that if you update any changes in that range, the drop down will automatically pick those changes and make your data validation consistent without requiring manual adjustments.
For this demo, you will use the following data against five students you wish to populate the Yes/No drop down based on whether they have attended the class or not.
1. Create a new sheet named List (you can rename it as you want) and put two values, Yes and No, in cells A1 and A2 of that sheet.
2. Now, go to the sheet where you want to add the drop down across cells. Select all the cells where you want the drop down. In this case, range B3:G7.
3. Navigate and click on the Data tab and select the Data validation option from the list.
4. The Data validation rules window will open up on the extreme right of the sheet. Click on the Add rule button to create a new data validation rule for the selected range.
5. You will see that the range B3:G7 is already selected in the Apply to range section. Inside the Criteria drop down, select option Drop down (from a range).
6. A new range picker will appear below the drop down criteria selected. Click on the Select data range button.
7. A new dialoge box named Select a data range will appear. Navigate to the sheet named List, where Yes/No values are stored in cells A1:A2, and select that range. Click OK to add this range for the data validation rule definition.
8. Click the Done button to apply this rule on the Data validation rules window. And that’s it!
9. Now, inside the sheet, if you see any cell from the range B3:G7, there is a drop down with Yes/No values.
Similar to the previous example, you can also go to the Advanced options and select the Reject the input radio button for If the data is invalid option.
This option will allow you to control the user inputs by restricting them from typing anything apart from the Yes/No drop down values, ultimately improving the data quality.
Note: For this demo, I created a new sheet named List to hold the Yes and No items. However, you don’t always need to store the data in another sheet. You can keep the range from the same sheet as well. Just ensure it doesn’t collide with the range where you add the drop down.
Using Data Validation and Named Ranges
When working with Data validation to create a drop down list from a range, You have a more convenient option of defining Named Ranges.
Using Named Ranges, you define a name for the range where the Yes/No values are stored, and then, instead of selecting the range from that sheet every time, you can specify the name of the range. Google Sheets will automatically pick the values from that Named Range.
Below is the data you will use for this example..
1. To add a Named Range, Go and click on the Data tab > select the Named ranges option.
2. In the Named ranges window that opens up on the right, click on the Add a range button.
3. Give a name to the range as YesNoDropdown.
4. Click on the Select the data option and select the range A1:A2 from the sheet List. Click on OK to assign this range to the name YesNoDropdown. Click on the Done button to create the Named range.
5. Go to the Data validation rules window by clicking on the Data tab and selecting the Data validation option.
6. Select the range B3:G7 from the Named Ranges sheet. Set the criteria as a Dropdown (from a range). Type the name =YesNoDropdown, which is the named range you defined. You will see that the content of this named range is populated below as a list of items Yes and No. Click on the Done button.
That’s it! You have used the named ranges to create a Yes/No dropdown in Google Sheets.
Once you are done setting up the drop-down using the values from the range from the sheet named List, you can hide the sheet so that others can’t alter the content of the range.
Right-click on the List sheet tab at the bottom and then select the Hide sheet option to hide this sheet for users.
And that marks the end of this article, where I used three different methods within the Data validation to create a drop-down inside Google Sheets.
The first method used a List of values where you can specify Yes and No manually inside the Data validation rules window.
The second method used a range from another sheet where Yes and No values were stored in cells A1 and A2. The method has its own benefits over selecting a list of items, and you can read through those in the article itself.
The third method was where I created a named range of the list of items from your drop-down list and used that named range as a reference to call those out in the Data validation rule rather than giving the sheet and cell reference.
If you know any other way to add a drop down list in Google Sheets, please let us all know through the comment section. Until the next time, ciao!
Other articles you may also like: