Do you want to convert 1 and 0 values to Yes and No values in Excel?

Imagine you’ve gathered user inputs to attend a webinar through your mobile app and website. However, when you access the database, all you see are a series of bewildering 1s and 0s, where you expect clear “Yes” and “No” responses.

To ensure that anyone working with this data in the future won’t be left scratching their heads, it’s essential to make this conversion.

In this comprehensive Excel tutorial, I’ll show the most efficient and user-friendly techniques to transform those cryptic 1s and 0s into the much more intuitive “Yes” and “No”.

By following this step-by-step guide, you’ll be able to achieve this conversion with minimal effort and time, making your data instantly more accessible and understandable.

Reasons to Convert 1/0 to Yes/No in Excel

Data readability is the primary driving factor behind converting 1s and 0s to “Yes” and “No” in Excel. Apart from a technical audience, the general users won’t be able to understand what these 1s and 0s mean in your data.

They would simply perceive these numbers as scores, which might give a wrong signal to your audience. Instead, if you show “Yes” and “No” in place of these binary numbers, your dataset becomes highly readable.

Other major reasons could be as mentioned below:

• “Yes” and “No” are human-readable values, and are more user-friendly than numerical values.
• Also, “Yes” and “No” are better suited for reports, dashboards, and presentations than 1s and 0s.
• This conversion aids in more straightforward data analysis and interpretation.
• When you convert 1s and 0s to “Yes” and “No” in Excel, you reduce the chances of misinterpreting or mishandling data.
• “Yes” and “No” labels make documentation and data sharing easier.
• Users typically expect binary choices like “Yes” and “No” rather than numerical values.

Now that you know when and why you must change 1s and 0s to “Yes” and “No” in your Excel worksheet, find below the best methods you must exercise:

Use Find and Replace to Convert 1/0 to Yes/No in Excel

The Find and Replace tool is the easiest solution to change all 1s and 0s in a dataset quickly to “Yes” and “No” without creating any complex functions or writing a single line of code. Find below the instructions to follow:

1. Launch Microsoft Excel and open the spreadsheet that contains the data you want to convert from 1s and 0s to “Yes” and “No”.
2. Click and drag to select the range of cells that contain the 1s and 0s that you want to convert.
3. Alternatively, you can apply the Find and Replace action to the entire worksheet. In this case, don’t select any specific columns, rows, or cell ranges.
4. Press Ctrl + F on your keyboard to open the Find and Replace dialog box.
5. Navigate to the Replace tab.
6. In the Find what field, you must enter what you want to find in the whole Excel worksheet or the selected range. In this tutorial, it’s the numerical 1.
7. Similarly, in the Replace with field, you must enter the values that will replace 1s in the worksheet. In this case, it’s “Yes”.
8. Click Find All to populate a drop-down list in the Find and Replace tool that shows which entries will be replaced.
9. If you’re happy with the content of the list, click Replace All.

Now you can follow the same process mentioned above to replace the occurrences of 0s with “No” in the whole worksheet or in a selected cell range.

Convert 1/0 to Yes/No in Excel Using the IF Function

The Find and Replace tool is only suitable in this conversion process when you’ve got a dataset already. Suppose, you want to create calculated ranges where if someone types 1s and 0s in a specific column the adjacent column will show “Yes” and “No”.

You can do this programmatically by using the IF function. Find below the simple steps you need to follow along:

1. Create a column for inputs in 1s and 0s.
2. Then, select the first cell (B2, considering column A is for input values) of the adjacent column.
3. Enter the following formula inside the selected cell:

=IF(A2=1,"Yes",IF(A2=0,"No","N/A"))

4. Hit Enter to see the results in the selected cell.
5. Now, use the Auto Fill drag handle to copy and paste the same formula down the column.

That’s it! If you enter the numerical 1 in the left side column, you should see “Yes” in the adjacent column, relative to the selected row. Also, if you enter the numeric 0, you should see “No” in the adjacent column but in the same row.

1 to Yes and 0 to No Using a Custom Cell Formatting Code

Suppose, you want to create a data entry column where the data operators will enter 1s or 0s for fast data entry and Excel will automatically convert the inputs to “Yes” and “No”.

Also, the conversion process will take place on the same column, no need to create a helper column like the one I created in the IF function-based method. Let’s get started with these steps:

1. Choose a column for the data entry and name it Input or whatever you prefer.
2. Highlight a few hundred or more cells below the column header.
3. Press Ctrl + 1 to bring up the Format Cells dialog box.
4. Click on the Number tab if not selected by default.
5. On the Category panel, click on the Custom category.
6. Click on the field below the Type section.
7. Copy and paste the following custom formatting code into the field:

[=1]"Yes";[=0]"No"

8. Click OK to apply the changes you’ve made.

Now, you can share the spreadsheet with your data entry operator or you can test yourself as well. When they or you type 1s and 0s in the Input column, Excel changes those entries instantly to “Yes” and “No”.

Convert 1/0 to Yes/No in Excel Using Power Query

The IF function in the M code of the Power Query Editor tool allows you to change 1s and 0s to “Yes” and “No” in Excel.

This method is particularly suitable for you if you’re importing a large dataset containing 1s and 0s and you need to do the conversion quickly when importing the dataset. You can also apply this method to a dataset already available in your Excel worksheet or workbook.

Here’s a step-by-step guide you can follow:

1. Go to the Data tab on your worksheet and click the Get Data command inside the Get & Transform Data block.
2. On the Get Data context menu, hover your cursor over the data source you want to use, like From Database.
3. In the overflow menu, choose the final data source, like From SQL Server Database.
4. Follow the onscreen instructions to import data and the Power Query Editor interface will open.

5. To import a dataset from the current worksheet, select the dataset, and click From Table/Range in the Data tab.
6. Click OK on the Create Table dialog box.

So far, you imported data to Power Query. Find how to transform it below:

1. Select the imported column on the Power Query Editor.
2. Now, go to the Add Column tab.
3. In the General block of the Add Column tab, click on the Custom Column button inside the General block.
4. The Custom Column wizard will open.
5. Click on the New column name field and type a name for the new column.
6. Inside the Custom column formula, copy and paste this formula:

if [Inputs]= 1 then "Yes" else "No"

7. Click OK to save the changes you made.
8. A new column with the converted values will pop up within the Power Query Editor.

In the formula mentioned above, replace [Inputs] with the column name of your own dataset.

You’ve successfully converted the 1s and 0s to “Yes” and “No” in Power Query. Now, find below the steps to export the dataset to your Excel worksheet:

1. Click the File tab and select the Close & Load To option.

2. On the Import Data dialog box, click the Existing worksheet option.
3. Now, choose a cell range on the worksheet.
4. Click OK.

The columns of Power Query will show up in your worksheet. Now, you can delete the existing columns of 1s and 0s from your worksheet.

Use Excel VBA to Convert 1/0 to Yes/No in Excel

This method is my favorite. Using this, I just need to click a few buttons and I can convert thousands of 1s and 0s in a column to “Yes” and “No” in an adjacent column.

Creating a VBA macro using the following script and instructions will take only a few minutes:

1. Press Alt + F11 to bring up the Excel VBA tool.
2. There, you must create a blank module by clicking Insert > Module.
3. In the new module, copy and paste the following script:

Sub ConvertOneZeroToYesNo()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    
    ' Set the worksheet containing the data
    Set ws = ThisWorkbook.Sheets("Sheet4") ' Change "Sheet4" to the name of your sheet
    
    ' Set the range in column A where you have 1s and 0s
    Set rng = ws.Range("A2:A7")
    
    ' Loop through each cell in the range
    For Each cell In rng
        ' Check if the cell value is 1
        If cell.Value = 1 Then
            ' If it's 1, write "Yes" in the adjacent cell in Column B
            cell.Offset(0, 1).Value = "Yes"
        Else
            ' If it's not 1, write "No" in the adjacent cell in Column B
            cell.Offset(0, 1).Value = "No"
        End If
    Next cell
End Sub

4. Click the Save button and save the Excel file as a Macro Enabled Workbook or XLSM file.
5. Now, close the Excel VBA Editor.

Here’s how you can modify the above script to make it work on your own dataset:

• In the ThisWorkbook.Sheets("Sheet4") code element, change Sheet4 to the actual worksheet name of your workbook.
• Also, the cell range A2:A7 should be replaced with another cell range that contains 1s and 0s in your worksheet. However, the script won’t work if the cell range reference covers more than one column like A2:B7, B2:C7, and so on.
• Excel will automatically populate the “Yes” and “No” values in the adjacent column so you don’t need to designate any destination.

Now, to use the macro, simply press Alt + F8 on your keyboard. Click on the ConvertOneZeroToYesNo macro on the dialog box and hit the Run button.

📝 Note: Once you make changes in your worksheet by using an Excel VBA script, you can’t undo the action. So, create a second copy of your workbook before trying this method.

Convert 1/0 to Yes/No in Excel Using Office Scripts

Office Scripts is the latest scripting tool for Excel so you can develop advanced automation by linking Excel to Power Automate.

Find below an Office Scripts code that you can use to convert 1s and 0s to “Yes” and “No” in Excel. I’m also sharing the steps to use the script.

1. Get the Office Scripts Code Editor panel by clicking Automate > New Script.
2. There, copy and paste this code:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range B2 on selectedSheet
	selectedSheet.getRange("B2").setFormulaLocal("=IF(A2=1,\"Yes\",IF(A2=0,\"No\",\"N/A\"))");
	// Auto fill range
	selectedSheet.getRange("B2").autoFill("B2:B6", ExcelScript.AutoFillType.fillDefault);
}

3. Click the Save script button.
4. Now, click on the Run button to execute the script.

If you can run the above code appropriately, Excel will automatically transform 1s and 0s in A2:A7 to “Yes” and “No” in B2:B7.

Now, if your dataset is in another column, say column C, replace all the occurrences of B2 and B6 in the above script with D2 and D6. If the dataset is more than 5 rows long in your worksheet, change the cell range reference in the code element autoFill("B2:B6" accordingly, like autoFill("D2:D100".

📝 Note: Office Scripts is only available when you buy a Microsoft 365 Business Standard or higher subscription. You need to install the Excel for the Microsoft 365 desktop app or access the Excel for the web app to get the Automate tab.

Conclusions

These are all possible ways you can try out to convert 1s and 0s in your Excel worksheet to “Yes” and “No” in Excel.

Try out the method you like the most and share your experience in the comments. Do you know another great method to accomplish the same result, don’t forget to mention that in your comment.