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.
How to Make Yes 1 and No 0 in Excel: 2 Effective Methods
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.
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: [Fixed!] IF Function Is Not Working 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.
Download Practice Workbook
You can download our practice workbook from here for free!
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.
Related Articles
- How to Use Excel IF Function with Range of Values
- How to Use Excel IF Between Multiple Ranges
- How to Use Multiple IF Statements with Text in Excel
- How to Use IF Function with Multiple Conditions in Excel
- How to Write Greater Than or Equal To in Excel IF Function
- How to Use MAX IF Function in Excel
- If a Value Lies Between Two Numbers Then Return Result in Excel
- How to Check If a Value Is Between Two Numbers in Excel
- How to Check If Value Exists in Range in Excel