Do you want to add a column of Yes or No data to your Excel dataset?

Yes or No is a common replacement for the Boolean data type where the Yes value is interpreted as true and the No value is interpreted as false.

This can make the information in your Excel dataset easier to read and interpret the meaning for your users.

This post will show you all the ways to create a yes or no in Excel.

Add Yes or No with a Dropdown List

The best way to add a yes or no value to your workbook is by using a dropdown list.

This allows you to ensure only the yes or no value is entered into your data and will help with data consistency.

Here are the steps to add a yes or no dropdown in Excel.

1. Select the range of cells in which you want to add a dropdown with the yes or no values.
2. Go to the Data tab in the ribbon.
3. Click on the Data Validation option in the Data Tools section.

This will open the Data Validation menu where you can set up the dropdown list and properties about how it will behave.

4. Go to the Settings tab of the Data Validation menu.
5. Select the List option from the Allow dropdown.

When you select the List option, this will show two more options in the Data Validation menu.

Uncheck the Ignore blank option if you don’t want the cells to contain blank values. When unchecked, the cell will show a warning when selected until either a Yes or No value is selected from the dropdown list.

Keep the In-cell dropdown option checked for Excel to show the user a dropdown list in the cell.

6. Add the text Yes, No in the Source field.
7. Press the OK button.

You can also add an Input Message and Error Alert for your dropdown list to help guide users and let them know when values are allowed in the cells.

Now you will have a dropdown list in the cell. When you select a cell with the dropdown, it will reveal a dropdown handle on the right of the cell.

Click on the dropdown handle to select either a Yes or No value for the cell.

Add Yes or No Dropdown with VBA

Creating a range of yes or no dropdown cells might be something you do a lot.

If this is the case, you might want to save some time and automate the process with a VBA script you can run with the click of a button in the Quick Access Toolbar.

This way, all you will have to do is select the range and click on the command in the quick access toolbar.

Sub AddYesNoDropdown()
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="Yes, No"
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = True
    .InputTitle = "Your input title"
    .InputMessage = "Your input message"
    .ShowError = True
    .ErrorTitle = "Your error title"
    .ErrorMessage = "Your error message"
End With
End Sub

This will add a yes or no dropdown menu to the active range.

This creates a dropdown with an input message and an error message which can be changed to suit your needs.

If you don’t want these at all, you can also set the title and message properties to an empty string "" in the above code.

Add Yes or No Dropdown with Office Scripts

Creating your yes-no dropdown list can also be automated with Office Scripts for use in Excel online.

function main(workbook: ExcelScript.Workbook) {
	let selectedRange = workbook.getSelectedRange();
	selectedRange.getDataValidation().setRule({ list: { inCellDropDown: true, source: "Yes, No" } });
	selectedRange.getDataValidation().setIgnoreBlanks(true);
	selectedRange.getDataValidation().setPrompt({ showPrompt: true, message: "", title: "" });
	selectedRange.getDataValidation().setErrorAlert({ showAlert: true, style: ExcelScript.DataValidationAlertStyle.stop, message: "", title: "" });
}

The above Office Script will create a yes or no dropdown list in the selected range.

Add Yes or No with Pick from List

There is a way you can add yes or no values from a list without adding dropdown data validation to your sheet.

There is an option in the right-click menu that allows you to pick values from a list.

This option allows you to select from previous values entered in the column.

All you need to do is enter at least one Yes and one No value in your column, then you will be able to select from them with this feature.

Follow these steps to use the Pick from Dropdown List option from the right-click menu.

1. Enter at least one Yes and No value in your column.
2. Select the empty cell just below the previous values.
3. Right-click on the empty cell.
4. Select the Pick from Drop-down List option in the menu.

This will show you a list in the cell containing your Yes and No option.

You can either select your value with the mouse or use the up and down arrow keys to highlight the value and then press Enter to add it to the cell.

Add Yes or No with a Keyboard Shortcut

The pick from dropdown list option in the right-click menu is interesting, but it does require a lot of steps.

In fact, it will take you more effort than just typing out the value.

There is a better way to use this feature by using a keyboard shortcut.

All you need to do is select the empty cell below all your other values and hold the Alt key and press the down arrow key.

This will show you the same list as from the right-click menu.

Add Yes or No with the IF Function

You might not want to manually enter a yes or no value in your data, but rather you want to base the yes or no on another value in your data.

For example, suppose you have a list of student grades as a percentage. Any student with a grade over 50% is considered a passing grade. You want to create a column that shows Yes if the student passed and No otherwise.

You can do this with an IF formula.

=IF(C3>=50%,"Yes","No")

The above formula will check if the grade in C3 is greater than or equal to 50% and return Yes if this is true. Otherwise, it will return No.

You can copy and paste this formula down the column to get the results for your entire column.

Add Yes or No with AutoComplete

Once you enter a few values in a column, Excel will try to automatically complete new entries as you type.

When entering Yes or No values in Excel, you will only need to type y or n after a full Yes and No has been entered.

When you type y just below the previous entries, Excel will suggest yes and all you will need to do is press the Enter key to accept the suggestion.

This turns typing Yes into typing only a y.

You can turn this feature on or off from the Excel Options menu.

1. Go to the File tab.
2. Select Options.
3. Go to the Advanced tab in the Excel Options menu.
4. Check or uncheck the Enable AutoComplete for cell values option in the Editing options section.
5. Press the Ok button.

When this setting is enabled, you will only need to type y or n and then press the Enter key to create your Yes or No value.

Add Yes or No with AutoCorrect

AutoCorrect is a similar option that will allow you to complete certain values you enter based on rules you set.

You can set up a rule that always changes the value y to Yes and n to No.

When you set this rule up, it will work across the entire workbook and not just in a single column like the auto-complete option.

Here’s how you can set up an AutoCorrect rule to change y to Yes and n to No.

1. Go to the File tab.
2. Select Options.
3. Go to the Proofing tab in the Excel Options menu.
4. Click on the AutoCorrect Options button.

This will open the AutoCorrect menu.

5. Go to the AutoCorrect tab.

6. Enter y in the Replace field and Yes in the With field. This will mean any time you enter the value y it will automatically get replaced with the value Yes.
7. Press the Add button to create the new rule.

You can make a similar rule to replace n with No. Then press the OK button to close the AutoCorrect menu, and press the OK button to close the Excel Options menu.

Now wherever in the workbook you enter a y or n, they will automatically get changed to Yes or No!

Conclusions

A yes or no column is a useful descriptive data column that can help you filter large lists or find the items you need visually.

There are many ways to get a yes or no value in your data set.

When creating a column of yes or no values, using a dropdown list should be your go-to method. Setting up these dropdown lists can even be automated with VBA and Office Scripts.

If your yes or no value depends on values in another column, then the IF function will be the preferred way to add the yes-no to your data.

But there are other useful methods worth knowing such as autocomplete and autocorrect!

Do you have any other tips for generating a yes-no column of values? Let me know in the comments below!