How to Convert True/False to Yes/No in Excel: 6 Easy Methods

Are looking for ways to convert True or False boolean values in an Excel table or dataset into Yes or No for better readability? Here are the effortless and proven methods you can try.

Microsoft Excel is the gold standard software for data visualization and analysis. However, some logical tests like boolean values such as True and False can make your data tables ambiguous for the common audience.

To overcome such technical terms on an Excel worksheet, you can replace these using Yes and No. Yes and No reads well and easily conveys the message you want to express. There’s no command group button or direct formula to simply convert all True and False values of your spreadsheet into Yes and No.

You must rely on your Excel knowledge and creativity to create such functions. Find below some popular functions and workarounds to translate True/False into Yes/No in Excel.

Reasons to Convert True/False to Yes/No in Excel

Here’s why you may need to change True and False values into Yes and No in Excel:

• Everyone can understand a Yes and No value rather than True and False.
• Most business reports and presentations use the Yes and No values to represent positive and negative results.
• Using Yes and No values in cells makes it easier to apply various Excel Data Validation rules when comparing with True and False values.
• When sending Excel worksheets as input data to databases, you may need to convert all True and False values into Yes and No values.
• When sorting data in large worksheets, it’s easier to sort using Yes and No rather than True and False.      

Now that you know why it helps to convert True and False into Yes and No in an Excel worksheet, find below several methods and functions to accomplish this task:

1. Use This IF Function

1. Select the cell where you need to populate Yes for True and No for False.
2. Now, copy and paste the following formula inside the selected cell:

=IF(B1>A1,"Yes","No")

3. In the above example, I got True/False values in column C for a logical test of B1>A1.
4. I want Yes and No in place of True and False under column D.
5. The results are obvious. I just pasted the above formula across D1 to D7. 

You may need to modify the above formula based on your spreadsheet. Inside the IF function, you must place the exact logical test that gives rise to True and False in a cell from reference cells.

2. Use This Nested IF Function

Suppose, there are no logical tests in the cell where True or False show up. You need to convert these best on the text display of the cell. Then, use these steps:

1. Click the cell where you want the conversion to take place from other reference datasets.
2. Hit the F2 key and paste the following formula:

=IF(C1=TRUE,"Yes",IF(C1=FALSE,"No"))

3. If the references match the formula, then you’ll see Yes for True and No for False.
4. When facing any error code, change C1 with the cell address where you expect the True and False values to appear.
5. If the formula works in one cell, copy and paste it across the entire or a selected range of the column.

3. Use This CHOOSE Function

You can also use the CHOOSE function of Excel to convert True/False values to Yes/No. CHOOSE function lets you specify a set of values for a cell based on the index number you add at the beginning of the function. Here’s how it works:

1. Select a cell where you want to transform True into Yes and False into No.
2. Copy the following formula and paste it into the selected cell:

=CHOOSE(C1+1,"No","Yes")

3. Don’t forget to replace the cell address C1 with an appropriate cell where True and False values exist.
4. Hit Enter, and you should see that the existing value True turns into Yes under column D.
5. If working as intended, copy the same formula down the column to convert the rest of the True and False into Yes and No.

4. Use This SUBSTITUTE Function

Using the SUBSTITUTE function you can replace the logical values with text values in the selected cell. This also changes the underlying cell values. Here’s how it’s done:

1. Move the cell selection cursor where you want to get converted values.
2. Copy and paste the following nested SUBSTITUTE function:

=SUBSTITUTE(SUBSTITUTE(C1,TRUE,"Yes"),FALSE,"No")

3. You’ll need to change the cell address C1 to an appropriate address where either True or False values are present.
4. Hit Enter to see True converting to Yes in a flash under column D.
5. Copy and paste the same formula across the column to convert a large list of True and False values.

5. Using This VBA Code

Here’s how you can automate the True/False values to Yes/No in an Excel worksheet for a large column or dataset in a single click. The converted values are texts, not formula values.

Create a VBA Macro

1. On the Excel worksheet, where you want to create the macro, hit Alt + F11 to open the VBA script editor.
2. Click the Insert button on the top toolbar and select Module.
3. A new script module will open. There, copy and paste this VBA script:

Sub ConvertTrueFalseToYesNo()
    Dim cell As Range
    
    For Each cell In Selection
        If cell.Value = True Then
            cell.Value = "Yes"
        ElseIf cell.Value = False Then
            cell.Value = "No"
        End If
    Next cell
End Sub

4. On the VBA editor, click the Save button on the top menu bar.

5. If your Excel workbook isn’t a Macro-enabled file, Excel will ask you to save it as a Macro-enabled file.

6. On the Save as dialog box, click the Save as type drop-down list to find and select the Excel Macro-Enabled Workbook option.
7. Then hit the Save button.

Execute the VBA Macro

1. Now, simply select the cells where you’ve got True or False values.
2. Hit the Alt + F8 keys together to open the Macro dialog box.
3. There, select the ConvertTrueFalseToYesNo Macro and hit the Run button.

4. The True and False values of the selected cells will convert to Yes and No in one click.

In the image above, you can see the changes yourself. Column D shows the #N/A errors because it contained the FORMULATEXT function for the cell ranges from C1 to C7. Since the VBA Macro replaced all True/False with Yes/No texts, hence you see the errors.

6. Using the Flash Fill

Flash Fill is a feature in Excel that automatically fills in values based on patterns it detects in the adjacent columns. You can use it to quickly translate True and False values into Yes and No by showing it the pattern. Here’s how you can train the Excel Flash Fill to convert True/False into Yes/No: 

1. Select the adjacent cell to the column where True/Fales values exist.

2. Type Yes for True and No for False for a couple of cells to create a pattern.
3. The Flash Fill tool will learn from your actions.

4. After you’ve done converting a few cells manually, select the manually converted cell ranges from top to bottom.

5. Then drag the Flash Fill selection until the end of the column up to which you want to convert values.
6. Excel will automatically convert True/False values into Yes/No.

Conclusion

So these are the most popular six methods to convert True and False values into Yes and No values on an Excel worksheet. You can choose the method that suits your need for the conversion. For example, if you’re looking for maximum automation and compatibility, it’s best to use the VBA code. Else, IF and Nested IF functions are also good for basic requirements.

Comment below to share your experience while using the above method on your Excel worksheet. If you’ve discovered any better method to do the same, don’t hesitate to let me know.