My yes/no column has five different spellings

A column that only ever means yes or no contains several spellings of each. Filtering for “Yes” misses rows, and a count of the yeses is lower than it should be.

Why this happens

  • Nothing constrained the column. It was filled in by different people, or merged from sources that each had their own convention, and every one of Y, y, yes, YES, TRUE, 1, x, and a tick character means the same thing to a reader.
  • Filters and lookups compare exact strings, so each spelling is its own value. A count of “Yes” is a count of one spelling, and it will be quietly and consistently too low rather than obviously wrong.
  • An empty cell is the genuinely ambiguous one. It might mean no, or it might mean nobody has answered yet, and those are different facts that get flattened together the moment anything treats blank as false.

What to do right now

  • List the distinct values in the column before deciding anything. It is usually a short list and it is usually longer than you expected.
  • 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. If it means unanswered, it must not be counted as a no, and the column probably needs to be required at the point of collection instead.

Stopping it on the next file

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. Anything else, “maybe” included, is refused on its own row rather than guessed at, which is the one thing you cannot recover from later. If a column like this lands as text, the review offers to retype it once the whole column reads as yes and no rather than only the first row or two.

The new hires template is a sheet with those rules already set, if you want to see the shape of one before building your own.

Other things that go wrong

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. Widening the column shows the whole line, commas or semicolons and all.VLOOKUP fails on values that look identicalA lookup or a join returns nothing for rows that plainly should match. Copying both values into a cell and comparing them returns false, even though they look the same on screen.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. Loading it as-is produces totals roughly double the truth.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. Anything that tries to use them as links works for some rows and not others.

Define the sheet once

Name your fields, and every file after that is matched to them and checked cell by cell before a row is allowed to land.

Start freeAll fixes