Standardize Yes/No Variants

Excel Formulas › Data Cleaning

All versionsIF

Survey and form data mixes "Y", "yes", "TRUE", "1" for the same answer. Map every affirmative variant to a single clean "Yes" (and the rest to "No") so the column is consistent.

Quick formula: normalize an affirmative answer to Yes:
=IF(OR(UPPER(TRIM(A2))={"Y","YES","TRUE","1"}), "Yes", "No")
Uppercase and trim the cell, then test it against the list of affirmatives; matches become Yes.

Functions used (tap for the full reference guide):

IFOR

The example

y / YES / True / 1 → Yes.

AB
1RawClean
2yYes
3nopeNo

The formula

The formula:

=IF(OR(UPPER(TRIM(A2))={"Y","YES","TRUE","1"}), "Yes", "No") // map variants to Yes/No

How it works

How it works:

  1. UPPER(TRIM(A2)) normalizes case and spaces so “y” and “ YES ” match.
  2. OR(… = {array}) tests the value against a list of affirmatives in one expression.
  3. Anything not in the list falls through to "No" — adjust the list to your data.
  4. A lookup table of raw→clean values scales better when there are many variants.

A mapping table beats a long IF. When inputs sprawl (Y/Yes/yep/affirmative/✓/1/true…), a two-column table of raw→standard values and a VLOOKUP is cleaner and easier to extend than nesting more conditions. Add a default for unmatched values so nothing silently disappears.

Try it: interactive demo

Live demo

Type any yes/no-ish answer.

Standardized:

Variations

Lookup table

Many variants:

=VLOOKUP(UPPER(TRIM(A2)), map_table, 2, FALSE)

To 1/0

Numeric flag:

=IF(OR(UPPER(TRIM(A2))={"Y","YES","TRUE","1"}), 1, 0)

Three-way

Yes/No/Unknown:

=IFS(yes_test,"Yes", no_test,"No", TRUE,"Unknown")

Pitfalls & errors

Normalize first. UPPER and TRIM so case and spaces don’t cause misses.

List every variant. Missing one sends it to "No" silently — check your data.

Array OR needs Ctrl+Shift+Enter in older Excel; 365 handles it natively.

Practice workbook

📊
Download the free Standardize Yes/No Variants practice workbook
A yes/no sheet with the lookup, numeric, and three-way variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I standardize Yes/No answers in Excel?
Normalize and test against a list: =IF(OR(UPPER(TRIM(A2))={"Y","YES","TRUE","1"}), "Yes", "No").
What if there are many variants?
Use a raw→standard mapping table with VLOOKUP, which is easier to extend than a long IF.
How do I convert to 1/0 instead?
Return numbers: =IF(OR(UPPER(TRIM(A2))={"Y","YES","TRUE","1"}), 1, 0).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Yes/no from a test · IF and OR · Normalize match key

Function references: IFOR