How to Insert a Yes or No Drop-Down in Excel Cells

To add a Yes/No box in Excel, select the cell or cells, open Data > Data Validation, choose List under Allow, and enter Yes,No in Source. The cells will then have a drop-down for choosing one of those two answers. This guide covers Excel for Microsoft 365 on Windows, with notes for Mac and Excel for the web.

What You’ll Need

An Excel workbook with the cells where you want people to choose Yes or No.
Permission to edit the workbook. If a worksheet is protected, you may need its password or the owner’s help to change validation settings.
For a simple list, nothing else is required. If your Excel region uses a different list separator, or you want to reuse the options in several places, use the helper-cell method below.

A Yes/No drop-down is useful for forms, checklists, surveys, status trackers, and data-entry sheets. It keeps answers consistent: users select an allowed entry instead of typing variations such as “yeah,” “Y,” or “Nope.” The result is text in the cell, not a clickable checkbox. If you specifically need a square that users tick, see the checkbox note in Tips.

Step-by-Step

Choose where answers should go. Click the cell for a single answer, or select the range you want to use for repeated answers. For example, to create a response column for 30 rows, select B2:B31. You can select a whole column, but limiting validation to the cells people will use makes the sheet easier to manage. If the worksheet has a header, do not include that header in the selection.
Open Data Validation. On the Windows ribbon, select Data > Data Validation. If the command is unavailable, first check whether the worksheet is protected or whether you are editing a cell rather than selecting a chart or object. The Data Validation dialog is where you set the accepted entries and whether Excel displays a drop-down.
Set the rule to a list. In the dialog’s Settings tab, open the Allow menu and choose List. Leave In-cell dropdown checked so users can open the selector from the cell. You can leave Ignore blank checked if an unanswered cell is acceptable. If every row must have a response, remember that this setting alone does not force users to fill every cell; it only affects whether a blank is permitted by the validation rule.
Enter the two choices. For a quick setup, click the Source box and type Yes,No, without quotation marks or spaces around the comma. This creates two text choices. If the entries are reversed or you prefer different capitalization, type them in your preferred order, such as No,Yes. On some regional settings, Excel expects a semicolon instead of a comma between typed list items. If Excel treats the entries as one choice or rejects the source, use the helper-cell method in the next step.
Use helper cells if needed. In two unused cells on the same worksheet, enter Yes in H2 and No in H3, for example. Return to Data Validation and set Source to =$H$2:$H$3. The dollar signs keep the source range fixed when the rule is applied elsewhere. You can hide the helper column after setup, but keep the cells on the same sheet for this direct-range method. If your choices are stored on a different sheet, create a named range for them and use that name as the validation source rather than entering a direct reference to another sheet.
Choose the invalid-entry behavior. Open the Error Alert tab and make sure Show error alert after invalid data is entered is checked if you want Excel to prevent other entries. Choose Stop for the Style, then add a short message such as “Choose Yes or No from the list.” A warning-style alert may let users keep an invalid value, so Stop is the clearest choice when consistent answers matter. You can also add an Input Message on its tab if you want instructions to appear when a user selects the cell.
Save and test the result. Select OK to apply the rule. Click a validated cell; the drop-down arrow typically appears when the cell is selected. Choose Yes, then test No in another cell. Also try typing an unrelated word. If you configured a Stop alert, Excel should reject it and show your message. Save the workbook once the choices and behavior are correct.

In Excel for Mac, the Data Validation controls are also on the Data tab; select Data Validation and set the same Allow, Source, and error-alert options. The dialog layout can differ slightly by release. In Excel for the web, select the cells and look for Data > Data Validation; if the workbook’s validation settings cannot be edited in the browser, open it in the desktop app. A rule already in the workbook can generally still be used by selecting its cells and choosing an allowed value.

Common Mistakes

Typing a space after the comma. A source such as “Yes, No” may include a leading space in the second entry, depending on how Excel parses the list. Type Yes,No, or use two helper cells to make the choices unambiguous.
Forgetting In-cell dropdown. A list validation rule can restrict what is accepted without showing a convenient arrow. Reopen Data Validation and check that In-cell dropdown is selected.
Applying the rule to the wrong cells. If you select only B2, only B2 gets the list. Confirm the full intended range before opening the dialog. To copy an existing validated cell, copy it and use Paste Special > Validation where available, or set the rule on the whole target selection directly.
Expecting a rule to fix old entries. Applying validation does not necessarily replace or remove values already in the cells. Review existing data separately, especially if it contains inconsistent spellings. Use the drop-down for future entries and correct older answers as needed.
Using a checkbox when the data needs to say Yes or No. A checkbox is a visual tick control and in-cell checkboxes represent a checked or unchecked state, not the words Yes and No. Choose a list if people need to select and store those literal answers.
Assuming validation is security. Validation helps prevent ordinary accidental entries, but it is not a secure barrier against deliberate changes, pasted data, or every import workflow. Do not rely on it to protect confidential or critical information.

Tips

Use a helper range for maintainability. Put Yes and No in clearly labeled cells and refer to them in Source. If you later change the wording to “Approved” and “Declined,” update the list once rather than editing a long set of rules. A helper range is also more dependable when regional settings affect separators.
Apply one rule to the full input area. Select all response cells before creating the validation rule. This is usually simpler than configuring each row separately. To verify what is applied, select a cell in the range and reopen Data Validation.
Make the purpose obvious. Use a nearby heading such as “Response” or “Complete?” and, if useful, add an Input Message that tells users what the choice means. Avoid relying on color alone to communicate the answer; the cell should contain the actual text.
Decide how blanks should work. If a response is optional, leave blanks allowed. If it is required, explain that to users and check the column before sharing or using the results. Data Validation does not automatically guarantee that every row is complete.
Use a real checkbox only when that interaction fits. In Microsoft 365 versions that include in-cell checkboxes, select a cell and choose Insert > Checkbox. The checked state is TRUE and the unchecked state is FALSE. Use this for a tick-box checklist; use the Yes/No list when the cell must visibly contain the words Yes or No. Availability can vary by platform and update channel.
Keep the choice wording consistent. Decide whether your workbook will use “Yes” and “No,” “Y” and “N,” or another pair, then use the same values throughout. Consistent text is easier to filter, count, and summarize later.

Troubleshooting

The arrow does not appear: Select the cell rather than expecting the arrow to stay visible all the time. If it still does not show, open Data > Data Validation and verify that Allow is set to List and In-cell dropdown is checked. Check that you are not selecting a merged cell or working in a protected sheet that blocks editing.

Excel accepts only one choice, or the list source is rejected: Your locale may use a different separator for list items. Replace the typed source with two helper cells and set Source to their range, such as =$H$2:$H$3 when Yes and No are in H2:H3. Make sure the helper values are on the same sheet for a direct range reference and that the source range does not include blank cells you do not want as choices.

Users can type other answers: Reopen the Error Alert tab and turn on Show error alert after invalid data is entered. Set Style to Stop. Be aware that pasting or importing values can behave differently from ordinary typing, so inspect the results after bulk updates. If people must not change the rule, protect the sheet after setting up the entry cells, while allowing users to select and edit the intended unlocked cells.

Some cells have the list and others do not: Select a cell where the rule works and inspect its settings. Then apply the same validation to the missing cells, or select the full intended range and create the rule again. If you copied cells from elsewhere, the paste operation may have replaced their validation.

The list contains duplicates or an unexpected blank: Review the helper cells for repeated entries, extra spaces, or empty cells within the source range. Correct the source, then reopen the validation dialog and confirm the exact range. The drop-down displays the source values as supplied; it does not automatically clean spelling or remove duplicates.

You cannot edit the validation rule: Check whether the sheet or workbook is protected, and whether you have edit access rather than view-only access. In Excel for the web, open the file in the desktop version if the needed dialog or setting is unavailable in your current browser session.

Frequently Asked Questions

Can I make the drop-down show Yes and No in a particular order?
Yes. Type the values in the order you want, such as Yes,No, or place them in that order in the helper cells. Excel presents list choices in source order.

Can I use different labels, such as Pass and Fail?
Yes. Replace Yes and No in the Source box or helper cells with the two labels you want. The list can contain other text choices; it does not have to be limited to yes-or-no wording.

Can users leave the cell blank?
Usually, yes. Keep Ignore blank selected if an empty cell is acceptable. If a response is mandatory, communicate that requirement and review the completed range; the setting does not automatically fill unanswered cells.

Can the same drop-down be added to many rows?
Yes. Select all the target cells before setting up Data Validation. You can also copy a validated cell to other cells, but check that the rule and any helper-range reference still point to the intended choices.

Can I make Excel display a pop-up message when someone chooses a value?
Data Validation’s Input Message appears when the user selects the cell, and its Error Alert appears when an invalid entry is attempted. It does not create a confirmation pop-up every time a valid list choice is selected.

Will the drop-down work if I share the workbook?
The validation rule is saved with the workbook, and users with compatible Excel apps can use it. If recipients open it in another spreadsheet program or import the data, behavior may differ, so check the shared file in the app your audience will use.

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.