How to Make Yes 1 and No 0 in Excel (2 Effective Methods)

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 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.

Task Set to Make Yes 1 and No 0 in Excel

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)

Use the IF Function to Make Yes 1 and No 0 in Excel

  • 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.

Drag Fill Handle Below to Copy Formula

Thus, you can make all yes into 1 and all no into 0 in Excel. And, the final outcome should look like this.

Made Yes 1 and No 0 in Excel

Read More: Excel Conditional Formatting Formula


Similar Readings


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.

Copy Yes/No Commands

  • Afterward, left-click on the D5 cell.
  • Subsequently, right-click on your mouse and choose the Paste option from the context menu.

Paste the Yes/No Commands

  • Now, select all the pasted cells from D5 to D10.
  • Following, go to the Home tab >> Editing group >> Find & Select tool >> Replace option.

Access to the Find & Replace Dialogue Box to Make Yes 1 and No 0 in Excel

  • 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.

Replace Yes with 1 in Excel

  • Consequently, a Microsoft Excel confirmation window will appear. Click on the OK button.

Confirm the Yes Replacement

  • 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.

Replace No with 0 in Excel

  • Again, a Microsoft Excel confirmation window will appear. Click on the OK button.

Confirm the Replacement

Consequently, you will see you have made 1 as yes and 0 as No in Excel. For instance, the output should look like this.

Made Yes 1 and No 0 in Excel

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.

Copy 1/0 Commands

  • 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.

Paste the Binary Commands

  • At this time, selecting the D5:D10 cells, right-click on your mouse.
  • Subsequently, choose the Format Cells… option from the context menu.

Choose the Format Cells Option to Make 1 as Yes and 0 as No in Excel

  • 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.

Type Custom Format to Make 1 as Yes and 0 as No in Excel

Thus, you will see that all the 1 and 0 will have Yes and No output now. And, the result would look like this.

Made 1 as Yes and 0 as No in Excel

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

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo