Yes or No Drop-Down in Excel: How to Set It Up Fast

To add a Yes or No drop-down in Excel for Microsoft 365 on Windows, select the cell or cells, then choose Data > Data Validation > Data Validation. Set Allow to List, enter Yes,No in Source, make sure In-cell dropdown is checked, and select OK. The cell will then offer Yes and No as choices, helping keep entries consistent.

What You’ll Need

An Excel workbook you can edit. If it contains important data, save a copy before changing validation settings.
The cell or range where people should choose Yes or No.
Either the two list items to enter directly or two worksheet cells containing Yes and No. The direct-entry method is fastest; a cell range is useful when regional settings affect list separators or when you want to maintain the choices in one place.

You do not need a formula, add-in, or macro. Data Validation is a built-in Excel feature. The instructions below use Excel for Microsoft 365 on Windows; the Mac and web differences are noted where they matter.

Step-by-Step

Select the destination cell or cells. Click one cell if you need a single answer, or select the full range if every row should have the same Yes/No choices. For example, select D2:D100 to add the drop-down to 99 response cells while leaving the header in D1 alone. You can select a range by dragging, or click the Name Box to the left of the formula bar, type D2:D100, and press Enter.
Open Data Validation. On the ribbon, go to Data > Data Validation > Data Validation. In some Excel layouts, Data Validation appears directly on the Data tab, so select that button to open the dialog. If the command is unavailable, check whether the worksheet is protected or the workbook is in a mode that prevents editing; see Troubleshooting below.
Choose a list as the validation type. In the Settings tab of the Data Validation dialog, open the Allow menu and select List. Keep Ignore blank checked if an empty response should be permitted. If every selected cell must have a value, you can clear Ignore blank, but note that validation does not force a user to fill an otherwise empty cell immediately. It controls entries when they are made.
Enter the Yes and No choices. For a quick setup, click the Source box and type Yes,No, without quotation marks. Ensure In-cell dropdown is checked, then select OK. Click one of the selected cells: a small arrow should appear at its right edge when the cell is active. Open it and choose Yes or No. If Excel rejects the comma-separated source or shows the choices as one combined item, use the cell-range method in the next step.
Use worksheet cells as the source if needed. Enter Yes in an unused cell, such as H2, and No in H3. Reopen Data > Data Validation > Data Validation for your destination cells, choose List, and enter =$H$2:$H$3 in Source. The dollar signs keep the source range fixed as the validation is applied. Select OK and try the drop-down. This method also makes it easy to edit or expand the available choices later. You can put the helper cells on the same worksheet, then hide their column if you do not want them visible.
Decide how invalid entries should be handled. In the Data Validation dialog, open the Error Alert tab and confirm Show error alert after invalid data is entered is selected. The default Stop style blocks a typed value that is not on the list and displays a message. You can set a clear title and message, such as Choose Yes or No, so people know what to do. Warning and Information styles do not enforce the list as strictly as Stop.
Test the finished cells. Select a destination cell and use its arrow to choose each option. Then try typing a different word and press Enter to confirm that the error alert behaves as expected. If you applied validation to many cells, test a cell near the bottom of the range too. Save the workbook after confirming the list works.

To apply the same validation to another range later, select the validated cell, copy it, select the new destination range, and use Paste Special to paste Validation. Alternatively, open Data Validation with the new range selected and enter the same settings. Copying the cell normally can bring along formatting and other content, so Paste Special is preferable when you only want the rule.

Common Mistakes

Entering the list in the wrong box. Yes,No belongs in Source after Allow is set to List. Typing it into the Cell value or Error Alert area will not create choices.
Leaving In-cell dropdown unchecked. A list rule may still exist, but the selection arrow will not appear. Reopen Data Validation and check the box on the Settings tab.
Selecting only the first cell. If the choices are required for an entire column of responses, select the intended range before setting up the rule. Applying it to D2 alone does not automatically validate D3 and the rows below.
Using a separator Excel does not recognize. The separator for a typed list can depend on Windows and Excel regional settings. If Yes,No appears as one option or Excel reports an error, put Yes and No in separate cells and use that range as Source.
Assuming validation repairs old data. Adding the rule does not automatically replace entries already in the cells. Review existing values separately, and correct anything that is not Yes or No.
Expecting the rule to block every paste. Typing an invalid value usually triggers a Stop alert, but pasting data from another cell or source can bypass data validation in some situations. Recheck imported or pasted responses rather than treating the drop-down as a complete data audit.

Tips

Choose a consistent spelling and case. Decide whether the labels should be Yes and No, YES and NO, or another pair, and use those exact labels throughout the workbook. Consistency makes filtering and summaries easier.
Use a source range for a workbook others will maintain. Keeping the options in cells makes them visible and easy to update. If the source list is on another worksheet, Excel may not accept a direct cross-sheet reference in the Source box. Define a named range for the two source cells first, then use that name in Source. To create one, select the source cells, click the Name Box, type a name such as YesNoOptions with no spaces, and press Enter. Use =YesNoOptions as the list source.
Set the rule before sharing the sheet. Add validation to the full response range before users start entering answers. This reduces inconsistent data and avoids having to identify and clean up variations later.
Consider whether blank is a valid response. A blank can mean not answered, while No is an actual answer. Keep those meanings separate if reports or decisions depend on whether someone responded.
Use an input message for extra guidance. In the Data Validation dialog, the Input Message tab lets you show a short prompt when a validated cell is selected. For example, explain what Yes means in your form. This is optional, but can help when the labels need context.
Use the same approach on Mac or the web, with interface variations. Excel for Mac has Data Validation under the Data tab, although dialog layout can vary by release. In Excel for the web, select the cells and look for Data > Data Validation; available controls can differ from desktop Excel. If a workbook’s web interface does not expose the setting you need, open it in the desktop app.

Troubleshooting

The drop-down arrow is missing. Select the cell and reopen Data > Data Validation > Data Validation. Confirm Allow is List and In-cell dropdown is checked. The arrow normally appears only while the cell is selected. Also check that you are looking at a cell that actually received the rule, rather than a nearby header or an unselected row.

Excel shows one option called Yes,No. The list separator may not match the comma you typed. Replace the typed list with the two-cell source method: put Yes and No in separate cells, select the destination range, and use the source range such as =$H$2:$H$3. If those cells are on another worksheet, use a named range instead of a direct sheet reference.

Excel accepts an entry that is not Yes or No. Open the Error Alert tab and ensure the alert is enabled and its style is Stop. Remember that pasted values can sometimes circumvent validation. To find inconsistent entries, use the drop-down to review cells or filter the response column and inspect unexpected values. Correct existing entries manually; adding a validation rule does not clean them up.

The Data Validation button is unavailable or the settings cannot be changed. The sheet may be protected, or the workbook may have restrictions. If you have permission, use Review > Unprotect Sheet and provide the password if prompted, then change the rule. Some shared or protected workbooks limit editing. Do not remove protection without authorization; ask the workbook owner for access or for the validation to be updated.

The list works in one cell but not the whole range. Select the intended cells and apply the rule again, or copy a correctly validated cell and use Paste Special > Validation on the rest. Be careful not to include a header or unrelated cells. If the rule was already applied but some values still look wrong, remember that existing entries are not automatically converted.

You removed the rule by accident. If the change just happened, use Ctrl+Z on Windows or Command+Z on Mac to undo it. If you have made other changes since then, undo may reverse those too, so save a copy before making further repairs. You can also recreate the validation by selecting the intended cells and following the steps above.

Frequently Asked Questions

Can I make the Yes or No drop-down the default value?
Data Validation creates a choice list but does not automatically select one option for every cell. If a cell should start with Yes or No, enter that value separately after applying the rule. Only use a default when it reflects the real answer; otherwise, it can make unanswered rows look complete.

Can I apply the drop-down to an entire column?
Yes. Select the cells where responses will go, such as D2:D500, and apply the list. Selecting a practical range below the header is usually easier to manage than applying the rule to the header or every cell in the worksheet.

Can people type Yes or No instead of using the arrow?
Yes. If the Error Alert uses Stop, typing an allowed value is generally accepted, while an unlisted value is rejected. Keep spellings consistent. Pasting can behave differently from typing, so validate data after imports or bulk pastes.

Can I change the choices to another pair later?
Yes. For a directly typed list, reopen the validation settings and edit Source. For a cell-based source, change the text in the two source cells. The drop-down then reflects the revised choices, but review existing responses because old values may remain in the worksheet.

Can I use checkboxes instead of a Yes/No list?
A checkbox is another way to represent a binary choice, but it takes more setup and is not the same as Data Validation. A drop-down is usually simpler for a compact form or table. Choose a checkbox when a visible, clickable control is important and the workbook’s version supports the checkbox feature you plan to use.

Does a Yes/No drop-down work in Excel for the web?
Workbooks with data validation can generally be opened and used in Excel for the web, but the available editing controls may vary. If you cannot create or adjust the rule in the browser, open the workbook in desktop Excel and apply the validation there.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.