This post is going to show you all the ways to create a Yes or No column in Google Sheets.
While working with Google Sheets, you often come across situations where you only need a yes or no answer in your data.
A good example of this would be a list of student grades. Even though a student receives a numeric grade between 0% and 100%, you may only be interested in whether or not the student has passed.
If a student’s grade exceeds 50% then he has passed. Otherwise he has failed. In this case, a column of Yes or No data would be a good way to show which students passed.
If you want to insert a Yes or No column in Google Sheets, how can you do it? This post discusses the different ways to do just that!
Download your copy of the example workbook used in this post and follow along!
Create a Yes or No Column with a Data Validation Dropdown List
One way to create a Yes or No column is through the use of data validation dropdown lists.
A dropdown list would contain a selectable Yes or No value on each row. This allows for an intuitive way to populate the column and prevents any data entry errors.
Fortunately, you can save time by creating all of your dropdown lists at once!
Follow these steps to create a Yes or No dropdown list.
- Go to your sheet and select the empty cells that will contain the Yes and No values.
In the above example, column C is the desired Yes or No column.
- Go to the Data menu at the top.
- Choose Data validation from the menu options.
This will open the Data validation dialog menu.
- Set the Criteria to a List of items.
Yes,Nointo the empty textbox in the Criteria section.
This indicates that, when it comes time for data entry, a
No value is the only value considered valid.
- Check the Show dropdown list in cell option.
This will ensure the the
No values are visibly present in a dropdown list in the cells.
- Select the Reject input option in the On invalid data section. This will prevent the entry of any value besides
- Press the Save button.
The dropdown lists now appear in your Yes or No column!
To confirm that everything is working properly, click on the small arrow icon displayed in the cell to reveal the dropdown list choices.
💡 Tip: Double click anywhere in the dropdown cell to reveal your choice of Yes or No.
Choosing Yes updates the cell value to Yes, and choosing No updates the cell value to No. You can see these values in the formula bar.
Create a Yes or No Column with a Checkbox
In Google Sheets, a cell can contain a checkbox.
By default, a checkbox receives a TRUE value when checked. Otherwise, it receives a FALSE value.
But, you can customize these TRUE or FALSE defaults to be something more meaningful, such as Yes or No.
This gives you the ability to create an entire Yes or No column with checkboxes!
Follow these steps if you’d like to create a Yes or No column with checkboxes.
- Select the empty cells that will contain your checkboxes.
- Go to the Insert menu.
- Select the Checkbox option from the menu.
Notice that your selected column now contains all the necessary checkboxes. Now you can customize these checkboxes for Yes or No values.
- Go to the Data menu.
- Select the Data validation option from the menu.
This will open the Data validation dialog.
- Check the Use custom cell values option
- Type Yes in the textbox labeled Checked and type No in the textbox labeled Unchecked In the textboxes that appear underneath.
- Select Reject input in the On invalid data section. This will prevent the entry of any value besides
- Press the Save button.
The new Yes or No column appears in column C above.
To test it out, check any checkbox within the column and notice how the cell value updates to Yes in the formula bar.
Uncheck the same checkbox and notice how its value changes to No, as intended!
Create a Yes or No Column with an IF Function
While demonstrating Yes or No columns, a list of student grades has been used as an example.
Based on this example data, a student’s pass status of Yes or No can be automatically calculated using an IF function, then applied to the rest of the column.
= IF ( B2 > 50%, "Yes", "No" )
Select the topmost empty cell within your intended Yes or No column. Assuming your selected cell is C2, paste the above formula into the formula bar, then press Enter to calculate the formula and create your Yes or No column with the IF function.
The IF function examines the student’s grade in cell B2 and returns
Yes if the grade exceeds 50%, otherwise it returns
The last step is to apply this IF function to the rest of the students. Hover the mouse cursor over the bottom-right corner of the calculated cell until your cursor changes to a cross.
Left-click and drag your mouse down to the bottom of the column.
Notice that your Yes or No column now contains the appropriate Yes or No value for each student, answering the question of who passed based on their numeric grade.
💡 Tip: Check out these other methods to apply your formulas across an entire column.
A Yes or No column is a common requirement in Google Sheets. This post has examined the different ways to create a Yes or No column.
In cases where your column will need to be manually populated by someone, you could use the data validation dropdown lists or the checkboxes.
The data validation dropdown lists are particularly good if it’s important to emphasize that an empty cell is neither Yes nor No.
On the other hand, if your Yes or No column could be populated based on data from your other cells, it would make sense to create the column using an IF function.
Either way, there’s an option of creating a Yes or No column that suits your needs!
Do you use Yes or No columns in your Google Sheets solutions? Let me know in the comments below!