We know that digital devices work with bits and use the binary system to process tasks. So, they just understand 1 and 0. Now, when you type yes or no as some decisions in your required worksheet and want to use this decision further, you have to get these values as 1 or 0 to process further. If you are looking forward to accomplishing this, you have come to the perfect place. In this article, I will show you 2 effective methods to make yes as 1 and no as 0 in Excel.
Download Practice Workbook
You can download our practice workbook from here for free!
2 Effective Methods to Make Yes 1 and No 0 in Excel
Say, we have a task set containing tasks and commands. Now, we want to get those commands in binary. That means we want to take the command yes as 1 and no as 0. Follow the methods below to accomplish your desired result.
In this article, we have used the Office 365 version of Microsoft Excel. You can use any other version of Excel available to you to apply these methods. If you face any problems regarding versions, please leave a comment below.
1. Make Yes 1 and No 0 Using IF Function in Excel
You can use the IF function to make yes 1 and no 0 in Excel. Follow the steps below to do this.
📌 Steps:
- First and foremost, click on the D5 cell.
- Subsequently, insert the following formula and hit the Enter button.
=IF(C5="Yes",1,0)
- Consequently, you will get 1 as the command is yes for the first task.
- Afterward, place your cursor in the bottom right position of the cell.
- As a result, a black fill handle will appear. Drag it down to copy the formula for all the cells below.
Thus, you can make all yes into 1 and all no into 0 in Excel. And, the final outcome should look like this.
Read More: Excel Conditional Formatting Formula
Similar Readings
- Excel If Cell Color Is Green Then Show or Customize Outputs
- How to Sum in Excel If the Cell Color Is Red (4 Easy Methods)
- If Cell Color Is Red Then Execute Different Functions in Excel
- Change Font Color Based on Value of Another Cell in Excel (2 Methods)
- Conditional Formatting on Text that Contains Multiple Words in Excel
2. Use Find & Replace Option
Another way to achieve your desired result is to use the Find and Replace dialogue box. Go through the steps below to do this.
📌 Steps:
- At the very beginning, select the cells C5 to C10.
- Subsequently, click your right mouse button and choose the Copy option from the context menu.
- Afterward, left-click on the D5 cell.
- Subsequently, right-click on your mouse and choose the Paste option from the context menu.
- Now, select all the pasted cells from D5 to D10.
- Following, go to the Home tab >> Editing group >> Find & Select tool >> Replace option.
- As a result, the Find and Replace window will appear.
- Now, go to the Replace tab.
- Subsequently, write Yes in the Find what: text box and insert 1 in the Replace with: text box.
- Last but not least, click on the Replace All button.
- Consequently, a Microsoft Excel confirmation window will appear. Click on the OK button.
- Now, similarly, in the Replace tab, write No in the Find what: text box and insert 0 in the Replace with: text box.
- Finally, click on the Replace All button.
- Again, a Microsoft Excel confirmation window will appear. Click on the OK button.
Consequently, you will see you have made 1 as yes and 0 as No in Excel. For instance, the output should look like this.
Read More: How to Apply Different Types of Conditional Formatting in Excel
How to Make 1 as Yes and 0 as No in Excel?
Now, you may need to reverse the output. That means you might need to make 1 as yes and 0 as no in Excel. You can also do this by using a Custom format. Follow the steps below to do this.
📌 Steps:
- Here, you are given 1 and 0 as commands. You need to have them as Yes and No.
- For this, first, select C5:C10 cells and right-click on your mouse.
- Subsequently, choose the option Copy from the context menu.
- Now, left-click on the D5 cell to select the cell.
- Following, right-click on your mouse and choose the option Paste from the context menu.
- At this time, selecting the D5:D10 cells, right-click on your mouse.
- Subsequently, choose the Format Cells… option from the context menu.
- As a result, the Format Cells window will appear.
- Subsequently, go to the Number tab >> choose the Custom option from the Category: pane >> write any of the following formats in the Type: text box.
[=1]"Yes";[=0]"No"
Or,
“Yes”;;”No”;
- Finally, click on the OK button.
Thus, you will see that all the 1 and 0 will have Yes and No output now. And, the result would look like this.
Read More: How to Make Yes Green and No Red in Excel (8 Examples)
Conclusion
In a nutshell, in this article, I have shown you 2 effective methods to make yes as 1 and no as 0 in Excel. Read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.
And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!
Related Articles
- Conditional Formatting If Cell is Not Blank
- Excel Formula to Change Text Color Based on Value (+ Bonus Methods)
- How to Copy Conditional Formatting to Another Workbook in Excel
- How to Remove Conditional Formatting but Keep the Format in Excel
- VBA Conditional Formatting Based on Another Cell Value in Excel
- How to Find Highest Value in Excel Column (4 Methods)