How to Make Yes 1 and No 0 in Excel (5 Suitable Ways)

If your Excel data includes values like “Yes” and “No”, you might need to convert them to numerical equivalents such as 1 and 0 for analysis, calculations, or logical processing. Fortunately, Excel provides several easy ways to do this using formulas, Find & Replace, Power Query, VBA, Office Scripts, and even custom number formats.

In this article, you’ll learn all practical methods to convert Yes/No to 1/0 and vice versa. These work across Excel 365, 2021, 2019, 2016, and earlier versions. Let’s get started.

This is the most common approach when working with raw text. You can directly convert “Yes“/”No” to 1/0 using the IF function.

To follow along with the examples in this article, we’ll use a simple dataset where column A contains responses labeled either “Yes” or “No.” The goal is to convert these text-based responses into numerical values which are 1 for “Yes” and 0 for “No”, so they can be used in calculations, filters, pivot tables, or automated logic across your workbook.

Steps:

➤ Use formula in a blank cell like B2:

➤ Press Enter and drag the formula down using the AutoFill handle.

Now, “Yes” becomes 1, “No” becomes 0, and all others return blank (“”) if desired values are not found.

If you’re working with a static dataset and want to quickly turn “Yes” into 1 and “No” into 0 without using formulas or scripts, the Find & Replace feature in Excel offers the simplest solution. This approach is perfect for quick, one-time changes when you don’t need the conversion to update dynamically.

Steps:

➤ Press  Ctrl  +  H  to open the Find & Replace dialog box.
➤ Set Find to Yes and Replace with field to 1 >> Click Replace All and you will immediately see your original column transformed.

➤ Then set Find to No and Replace with field to 0 >> Click Replace All again.

All cells that originally had the word “Yes” become 1 and all “No” become 0 in your sheet.

If you’re handling large or recurring datasets like survey results, status logs, or imported text files, Power Query offers an efficient and repeatable way to convert “Yes” and “No” values into 1s and 0s. Unlike basic formulas or Find & Replace, Power Query lets you transform entire columns with structured logic, all while keeping your original data intact.

This is especially useful when you need to clean and prepare data before loading it into pivot tables, dashboards, or analytical models.

Steps:

➤ Select your data and go to the Data tab >> Get & Transform group >> From Table/Range.

➤ In Power Query, select the column and choose Add Column tab >> Custom Column.

➤ Give your column a name like Conversion.
➤ Use this formula:

➤ Click OK.

➤ Click Close & Load to return the transformed data to Excel.

This returns 1 for “Yes“, 0 for “No“, and null for all else in a brand new sheet.

VBA is perfect for users who frequently work with repetitive tasks or large datasets. By running a simple macro, you can automatically scan a column of “Yes” and “No” values and output their numeric equivalents (1 and 0) in an adjacent column. This method offers complete control and is ideal for advanced Excel users managing complex workbooks.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ Go to the Insert tab >> Click on Module.
➤ Paste this code in the blank area that appears:

Sub ConvertYesNoToOneZero()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Update as needed
    Set rng = ws.Range("A2:A7") ' Update as needed
    For Each cell In rng
        If cell.Value = "Yes" Then
            cell.Offset(0, 1).Value = 1
        ElseIf cell.Value = "No" Then
            cell.Offset(0, 1).Value = 0
        End If
    Next cell
End Sub

➤ Press  F5  key to run the macro.

This fills the next column with 1 for Yes and 0 for No.

Office Scripts is a cloud-based automation tool available in Excel for the web. It enables users to automate conversions like turning “Yes” into 1 and “No” into 0 using lightweight TypeScript code. This is especially helpful for shared or browser-based workflows, making automation accessible even without the full Excel desktop app.

Steps:

➤ Go to Automate tab >> Click New Script under All Scripts.

➤ Paste the following code in the Code Editor:

function main(workbook: ExcelScript.Workbook) {
            let sheet = workbook.getActiveWorksheet();
            sheet.getRange("B2").setFormulaLocal("=IF(A2=\"Yes\",1,IF(A2=\"No\",0,\"\"))");
            sheet.getRange("B2").autoFill("B2:B7", ExcelScript.AutoFillType.fillDefault);
}

➤Hit the Run button to see your data transformed immediately.

Now you have your desired output in column B with corresponding 1 and 0 as values.

Frequently Asked Questions

How do I convert “Yes” to 1 and “No” to 0 automatically in Excel?

You can use formulas like =IF(A1=”Yes”,1,0) or =IF(A1=”No”,0,IF(A1=”Yes”,1,””)) to convert Yes/No to numbers automatically. These formulas are simple, dynamic, and ideal for data processing.

Can I convert Yes/No to 1/0 without formulas?

Yes, Excel’s Find & Replace tool lets you convert Yes to 1 and No to 0 directly in the sheet. This method is great for one-time, manual replacements on static datasets.

Which method is best for recurring conversions?

For recurring data cleanup, use Power Query, VBA, or Office Scripts. These automate the process and reduce human error, especially when working with large volumes or regularly updated Yes/No values.

What if my Yes/No values are case-sensitive?

By default, Excel’s IF, Power Query, and related logic functions are not case-sensitive. However, if needed, you can use the UPPER or LOWER functions to enforce case sensitivity in comparisons.

Can I use Conditional Formatting to turn Yes into 1?

No. Conditional formatting only changes how a cell looks, not its actual content. You can use it to highlight “Yes” or “No“, but not to transform them into numeric values like 1 or 0. Switch to other options like using formulas or PowerQuery.

Wrapping Up

In this tutorial, we explored many different methods to convert Yes/No to 1/0 in Excel starting from built-in tools and formulas for simplicity to Power Query or Office Scripts for automation, and VBA for advanced tasks.Whether you’re building formulas for live dashboards, cleaning static datasets, or automating transformations at scale, there’s a method here that fits. Feel free to download the practice file and share your feedback.