Excel Yes or No Drop Down: Build It in Under a Minute (2026)

To make a yes or no drop down in Excel, select your cells, go to Data > Data Validation, set Allow to List, and type Yes,No in the Source box. Click OK, and each selected cell gets a small arrow that lets you pick Yes or No, with anything else blocked by default.

That’s the whole trick. The rest of this guide covers doing it cleanly for a full column, controlling what happens when someone types something else, and when Excel’s newer checkboxes make more sense.

Step-by-step: add a Yes/No drop-down

These steps follow Microsoft’s own instructions in Create a drop-down list and Apply data validation to cells. They work the same way in current desktop versions of Excel.

1. Select the cells that should get the drop-down. This can be one cell, a block, or a whole column of a table.
2. Open the Data tab and click Data Validation.
3. On the Settings tab, open the Allow menu and choose List.
4. In the Source box, type Yes,No. Separate the entries with a comma and no spaces, as Microsoft’s examples do.
5. Make sure In-cell dropdown is checked. If it’s unchecked, the rule still works but there’s no arrow to click.
6. Click OK.

Click any of the cells and you’ll see the arrow on the right. Pick Yes or No, or just type one of them.

Using a cell range instead of typed values

You can also point the Source box at cells that already contain the options:

1. Type Yes in one cell and No in the cell directly below it, ideally on a separate “Lists” sheet.
2. In the Data Validation dialog, click in Source and select those two cells (don’t include a header).
3. Click OK.

Microsoft recommends keeping list items in one column or row with no blank cells, and suggests turning the range into an Excel table (Ctrl+T) so the drop-down picks up new items automatically when you add them.

Which source method to pick

For a plain Yes/No field that will never change, the typed list is the simplest and has nothing that can break if someone deletes a helper sheet.

Control what happens with wrong entries

By default, Excel shows a Stop alert if someone types anything other than Yes or No. You can change this on the Error Alert tab of the Data Validation dialog, according to Exceljet’s data validation guide and Microsoft’s documentation of the alert styles.

Use Stop when the column feeds formulas or reports and must contain only Yes or No. Use Warning or Information if you occasionally need notes like “Pending” and just want to nudge people toward the standard answers. You can also write your own title and message here, for example: “Please choose Yes or No from the list.”

Add an input message

The Input Message tab lets you show a small tooltip whenever someone selects the cell, such as “Did the client sign off? Choose Yes or No.” It’s optional but helpful in shared trackers where not everyone knows the convention.

Apply it to more cells or edit it later

Covering more cells. The easiest route is to select the full range (or the whole table column) before you open Data Validation, so the rule is applied to every cell at once.

Changing the options. Select the cells, open Data Validation again, and edit the Source box. Microsoft’s add or remove items article explains that for comma-separated sources you simply type new entries or delete ones you don’t need, for example changing Yes,No to Yes,No,N/A. If the source is a range or table, edit the cells instead.

Removing the drop-down. Open Data Validation for the selected cells and click Clear All, then OK.

If Data Validation is greyed out. Microsoft notes this happens when the sheet is protected or the workbook is shared. Unprotect or stop sharing, make your change, then re-protect.

Things to know before you rely on it

• Validation isn’t a security feature. Sources including Contextures and Exceljet point out that people can get around validation, for example by pasting over cells or filling formulas across them. If the data really matters, check it with a formula or conditional formatting as well.
• Existing data isn’t cleaned up. Adding a rule restricts new entries; it doesn’t rewrite values that were already in the cells, so review those yourself.
• Spelling matters downstream. Formulas like =COUNTIF(B2:B100,"Yes") count the Yes answers, which works best when everyone picked from the list instead of typing “Y” or “yes ”.
• Use the arrow, not typing, for consistency. Picking from the list guarantees the exact spelling and capitalization your formulas expect.

Alternative: use a checkbox instead

If you don’t need the words Yes and No in the cell, Excel’s native in-cell checkboxes may be faster to use. Microsoft introduced them in Excel for Microsoft 365, and per Microsoft’s Using check boxes in Excel page you add them by selecting cells and choosing Insert > Checkbox.

A checked box stores TRUE and an unchecked box stores FALSE, so you can still write formulas such as =IF(C2,"Yes","No") to display a word elsewhere. Click a box or press Spacebar to toggle it. Pressing Delete once unchecks checked boxes, and pressing it again removes them.

Checkboxes need a current Microsoft 365 build. As of October 2026 Microsoft lists them as available on Windows, Mac, web and mobile, but if you don’t see the button your Excel may be out of date or on an older perpetual-license version. In that case the Yes/No drop-down above works everywhere Data Validation does.

Quick recap

Select cells, Data > Data Validation > List, Source Yes,No, OK. Adjust the Error Alert style if you want to allow exceptions, apply the rule to a whole range or table column at once, and switch to Insert > Checkbox if TRUE/FALSE suits your sheet better. Menu names and feature availability can shift with Microsoft 365 updates, so if something looks different, Microsoft’s support pages linked above are the up-to-date reference.