If you’re looking to make Excel show a colored Yes or No based on user input, then you’ve come to the right place. There are a few methods that you can go about doing this:
- Create a data validation dropdown list where the user selects Yes or No. You can then apply conditional formatting to the cell to color code it as well
- If you’re trying to create the Yes/No column based on another column, you can create an IF formula
- Create a custom cell format whereby it displays YES or NO when a user types in 1 or 0
Still interested? Then keep on reading!
How To Format Yes Or No In Excel
Yes/No Data Validation List
In Excel, you can create a dropdown list that has the selection Yes or No. To do this:
- Click on the Data tab
- Select Data Validation
- Within the Data Validation Menu, under the Allow dropdown, select “List”
- In the “Source” type in “Yes, No”. If you want additional selections in the dropdown list, they are separated by a , (comma).
- Click OK
Now when you click on a cell, you will get a down arrow representing the dropdown list.
Note – when you create the dropdown, you can highlight a range and it’ll apply to all those cells. Alternatively, you can paste special validation – more information in Copy Paste Like A Pro In Excel.
Conditional Formatting
Once you have the dropdown list set up, you can apply Conditional Formatting over the cells to help user readability. In this example, let’s set Yes to Green and No to Red.
- Under the Home tab, click on Conditional Formatting and select New Rule
- Select the “Only format cells that contain” option
- Select “Specific Text” and “containing” – type in Yes. Select the Format you’d like and click OK
- Repeat these steps but instead of “Yes” type in “No” and select another color. Voila!
IF Formula
This method is useful when you want to calculate a Yes/No answer based on some existing data. For example, I want to check if the numbers in column A are greater than 25. If it is, then return “Yes”, else return “No”. You could utilize a formula like this =IF(A1>25,”Yes”,”No”).
Similar to the data validation list, you can also apply conditional formatting on top of this row to improve readability.
Custom Cell Format
Last but not least, you can apply a custom cell format to each cell. You can use the custom format [=1]”Yes”;[=0]”No”. This means when a user types in 1, it displays as Yes, and No when they type in 0.
- To open the custom format, right-click on a cell and select Format Cells or CTRL+1 shortcut.
- Under the custom category, add the custom format [=1]”Yes”;[=0]”No”.
- Click OK
This technique is useful when you need to reference the cell for a formula and require the underlying 0 or 1 integer. If you do use this method, I suggest clearly labeling it as this can cause confusion with some Excel users! Otherwise, consider adding a dropdown list for 0 and 1 to avoid any data issues.
Again, you can apply conditional formatting on top of this. Instead of the “Yes” or “No”, then you can set the formula to 0 or 1.
Thanks for reading and I hope this saves you some time!
Video Guide
Other Excel and productivity tutorials you may also like:
