My yes/no column has five different spellings
A column that only ever means yes or no contains several spellings of each.
Why it happens
- Nothing constrained the column.
- Filters and lookups compare exact strings, so each spelling is its own value.
- An empty cell is the genuinely ambiguous one.
What to do now
- List the distinct values in the column before deciding anything.
- Map each spelling to one of two values deliberately, rather than filtering for the spelling you happen to have thought of.
- Decide what blank means and record that decision.
Next time
A boolean field accepts the spellings people actually write, case-insensitively: true, yes, y, 1, x, on, and checked all arrive as yes, and false, no, n, 0, off, and unchecked all arrive as no.
Start from the new hires template.
Related problems
My CSV opens with everything in one columnThe file opened, but every row sits in a single column with the separators still visible in the text.VLOOKUP fails on values that look identicalA lookup or a join returns nothing for rows that plainly should match.My export has subtotal rows mixed in with the dataThe file has real rows, but also rows that are section headings with one cell filled, subtotal rows with a figure and no product, and a grand total at the bottom.My website column will not work as linksA column of company websites contains a mixture of forms: some with https://, some starting www, some bare domains, and a few entries that are not addresses at all.