To create a Yes/No drop-down in Excel, select the cells, go to Data > Data Validation, set Allow to List, type Yes,No in the Source box, and select OK. Each cell now shows an arrow with Yes and No to choose from, and Excel rejects anything else typed in.
Applies to: Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016 on Windows and Mac, and Excel for the web. Last reviewed October 8, 2026.
Step-by-step
- Select the cells where you want the drop-down, for example C2:C100. Leave out the header row so the heading isn’t restricted.
- Go to the Data tab and select Data Validation (the icon with a green tick and red circle).
- On the Settings tab, set Allow to List.
- In Source, type Yes,No. Use a comma, with no spaces needed. In some regions Excel expects a semicolon: Yes;No.
- Leave In-cell dropdown ticked and select OK.
Click a cell and select the arrow to choose Yes or No. From the keyboard, press Alt+Down Arrow to open the list.
Add a third option
Change the Source to Yes,No,N/A or Yes,No,Maybe. To edit an existing list later, see how to modify a drop-down list in Excel.
Color Yes green and No red
- Select the same cells.
- Go to Home > Conditional Formatting > Highlight Cells Rules > Equal To.
- Type Yes and choose Green Fill with Dark Green Text. Select OK.
- Repeat with No and Light Red Fill with Dark Red Text.
Count the answers
- Number of Yes: =COUNTIF(C2:C100,”Yes”)
- Number of No: =COUNTIF(C2:C100,”No”)
- Percentage Yes: =COUNTIF(C2:C100,”Yes”)/COUNTA(C2:C100), formatted as a percentage.
Use the answer in other formulas, for example =IF(C2=”Yes”,B2*0.1,0) to add a 10% charge only when C2 is Yes.
Show a message or a custom error
- Input Message tab: add a hint such as “Choose Yes or No” that appears when the cell is selected.
- Error Alert tab: change the warning shown when someone types something else. Set Style to Warning if you want to allow other entries after a warning.
Use a checkbox instead
In Excel for Microsoft 365 and Excel 2024, Insert > Checkbox adds a tick box that returns TRUE or FALSE. It’s quicker to click for long lists. See how to add a checkbox in Excel.
Copy the drop-down to more cells
Drag the fill handle down from a cell that has the drop-down, or copy it and use Paste Special > Validation to add only the drop-down without changing values. If the cells are in a table (Ctrl+T), new rows get the drop-down automatically.
Troubleshooting
The arrow doesn’t appear
Make sure In-cell dropdown is ticked. The arrow only shows when the cell is selected.
Data Validation is greyed out
The sheet is protected or shared, or you’re editing a cell. Press Esc, then unprotect the sheet in Review.
Pasted values bypassed the list
Pasting overwrites validation. Use Data > Data Validation > Circle Invalid Data to find wrong entries.
How do I remove the drop-down?
Select the cells, open Data Validation and select Clear All. See how to remove data validation restrictions.
Frequently asked questions
Can I make other drop-downs the same way?
Yes, any short list works. For longer lists from a range of cells, see how to create a drop-down list in Excel; for lists that depend on another choice, see dependent drop-down lists; and to pick several items, see multi-select drop-downs.
What else can Data Validation do?
Limit entries to whole numbers, dates or a text length. See how to do data validation in Excel.
Related: how to add radio buttons in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.