Excel Yes or No Drop-Down: Create and Customize It

Yes. In Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation > Data Validation, set Allow to List, and enter Yes,No in Source. Click OK to add a drop-down with those two choices; the sections below explain how to make it easier to maintain, use, and troubleshoot.

How a Yes or No drop-down works

A Yes/No drop-down is a list-based data validation rule. It gives a person filling in a cell a small arrow and a choice of permitted values. The rule helps keep entries consistent: people choose “Yes” or “No” rather than entering variations such as “Y,” “yes,” or “Nope.” This consistency is useful when you later filter, count, or summarize responses.

The drop-down does not turn a cell into a special yes/no data type. The selected values are text, and Excel stores the selected word in the cell. That distinction matters if you plan to use formulas: test for the text “Yes” or “No,” rather than treating the cell as a Boolean value such as TRUE or FALSE. A modern Excel checkbox is a different option, discussed below.

Create a Yes or No drop-down in Excel

For a small list, enter the choices directly into the validation rule:

Select the cell or range where people should make a choice. For example, select B2:B30 to set up 29 response cells.
On the ribbon, select Data > Data Validation > Data Validation. If the button appears as a single command, select Data > Data Validation.
In the Settings tab, set Allow to List.
In Source, type Yes,No. Keep In-cell dropdown selected.
Select OK. Select one of the cells to see its drop-down arrow.

Leave Ignore blank selected if an unanswered cell is allowed. Clear it if users should be required to choose a value when they enter data. This setting does not automatically fill blank cells, and it does not make an existing blank cell into a completed response. Consider adding a separate instruction or a completion check if every row must be answered.

Excel’s list separator can depend on regional settings. If typing Yes,No does not create two choices, use a source range instead. A range also makes it simpler to edit the choices later.

Use a worksheet range as the source

To make the choices easy to maintain, put them in two cells on the same worksheet—for example, type Yes in H2 and No in H3. Select the destination cells, open Data Validation, choose List, and enter =$H$2:$H$3 in Source. The dollar signs keep the source fixed if you copy the validation rule to other cells. You can hide column H after setting up the list, but make sure workbook users who maintain the choices can still find it.

A source range is especially useful when the choices may change, when your regional list separator causes problems, or when you want to add a third value such as “Not applicable.” Edit the source cells to update the options. If you put the source values on a different worksheet, Excel may not accept a direct sheet reference in the Source box. One practical approach is to define a named range for the cells through Formulas > Define Name, then use that name as the list source. This also gives the source a clear label, such as YesNoOptions.

For a short, fixed list, typing the values directly is usually quicker. For a shared workbook or a list that may be revised, a range is easier to inspect and maintain. Either way, keep the option spelling consistent; a list containing “Yes” will not automatically treat manually entered “YES” or “Y” as the same text in every downstream formula or process.

Configure prompts and invalid-entry alerts

The Data Validation dialog can do more than supply a list. On its Input Message tab, you can show a short instruction when a user selects a validated cell, such as “Choose Yes if the request is complete.” This is useful when the meaning of Yes is not obvious from the column heading. Keep the instruction specific: explain what the response means, not just that a drop-down exists.

On the Error Alert tab, choose whether Excel displays a message when someone enters a value that is not on the list. The Stop style is the strictest choice for ordinary typing and lets you provide a title and a message such as “Select Yes or No from the list.” Warning and Information alerts offer less restrictive behavior. Review the alert settings if users need to paste or enter other values intentionally.

Data validation is a helpful input control, not a security boundary. Copying and pasting data into validated cells can bypass or replace the expected rule, and imported data or automation may also introduce unexpected values. For important records, check the data after entry and restrict access to the workbook or source data as appropriate. A drop-down alone cannot guarantee that every cell contains a permitted answer.

Use the responses in formulas and summaries

Because the selected value is text, a formula can compare it with the word Yes. With a response in A2, enter this formula in B2 to display “Approved” when A2 contains Yes and “Review” otherwise: =IF(A2="Yes","Approved","Review"). If A2 is Yes, the result is Approved; if it is No, the result is Review. If blank responses are possible, account for them separately rather than letting a blank cell be treated as No.

For example, with responses in A2:A9 containing five Yes values and three No values, enter this formula in another cell to count the Yes responses: =COUNTIF(A2:A9,"Yes"). The result is 5. COUNTIF counts cells matching the specified text, so an unexpected spelling such as “YES ” with a trailing space may not be counted as intended. A validation rule reduces these inconsistencies, but it is sensible to inspect data that has been pasted or imported.

For a quick review, you can also filter the response column to show only Yes or only No. Use the worksheet’s filter controls rather than changing the validation list: the filter determines which rows are visible, while validation determines the choices offered when editing a cell.

Common problems and how to fix them

The arrow is not visible: Select the cell and check Data > Data Validation. Confirm that Allow is List and In-cell dropdown is selected. The arrow generally appears when the validated cell is selected, not permanently beside every cell.
The Source box rejects the comma-separated list: Try a two-cell source range, such as H2:H3. Regional settings can affect which separator Excel expects for a typed list.
Some cells have the list and others do not: Check the validation rule on a working cell, then apply the same rule to the missing cells. Copying and pasting can also overwrite validation, so check the destination cells after pasting.
Users can type something else: Review the Error Alert tab and use Stop if appropriate. Remember that paste operations can bypass validation, so inspect data when strict consistency matters.
The list choices need changing: Edit the source range if you used one, or reopen Data Validation and change the Source entry if you typed the choices directly.

If a cell already contains an invalid value, changing the rule does not necessarily clean up that existing content. Correct the cell itself, then verify the list rule. If you need to check many rows, filter or search for values that do not match the approved choices.

Drop-down list or checkbox?

A Yes/No drop-down is a good fit when the actual words Yes and No should appear in the cell, when people need to pick among more than two options later, or when the workbook must work in versions without the newer checkbox feature. It also makes the intended response explicit in a table that may be read or exported elsewhere.

Recent Microsoft 365 versions of Excel include in-cell checkboxes where the feature is available. A checkbox represents a Boolean TRUE or FALSE value rather than storing the words Yes and No. That can suit a simple completion flag, but formulas and summaries must then work with TRUE/FALSE. Availability and the ribbon interface can vary by update and platform. If you specifically need text responses, use Data Validation with a list; if you prefer a compact checked/unchecked indicator and have the checkbox command, consider a checkbox instead.

Excel for Mac and Excel for the web

In Excel for Mac, select the destination cells and use Data > Data Validation; the dialog uses the same basic List and Source concepts, although the ribbon layout can differ. In Excel for the web, select the cells and look for Data > Data Validation to create or edit a list rule. The exact controls can vary as Microsoft updates the web interface. If a workbook opens in a version that does not expose a setting in the same place, use the desktop Excel application to configure the rule.

Frequently Asked Questions

Can I make a Yes/No drop-down for an entire column?
Select the cells that should accept answers, such as B2:B500, before creating the rule. Applying validation to a defined range is generally preferable to selecting every worksheet cell, because it avoids adding a rule where it is not needed.

Can I make the cell show Yes or No by default?
Validation offers choices but does not choose one automatically. Type a value into the cell, or use a separate process to populate it. Leaving the cell blank can be useful when a response has not yet been provided.

Can I use Yes, No, and Not applicable?
Yes. Enter all three options in the Source box if your regional separator permits it, or put them in three cells and use that range as the source. Make sure formulas and reports account for the additional response.

Can I copy a Yes/No drop-down to other cells?
Yes. You can apply the validation rule to a selected range from the start, or copy a validated cell and use Paste Special > Validation where that command is available. Check the destination cells afterward, especially if other pasted content may replace their rules.

Does a drop-down prevent every other value?
No. An error alert can reject an invalid value entered normally, but copying, pasting, imports, and other workflows can circumvent validation. Treat the list as a way to guide data entry, and validate important data separately.

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.