Cleaning up form responses

TL;DR:

• Never edit the raw tab. Copy it, then clean the copy so you can always go back.

• Standardize first (spaces, capitals), then categorize with a small lookup table instead of retyping.

• Count with COUNTIF on the cleaned column and send anything the table does not recognize to a Review pile.

A form asked, "Which team are you on?" with a blank text box. Now the column reads Marketing, marketing, marketing (with a trailing space), Mktg and MKTG. A report that counts teams will show five groups for one. The fix is a short, repeatable cleaning pass that you can run again whenever new responses arrive.

Why messy answers happen

People type quickly and differently. Extra spaces, capital letters, abbreviations and typos are all normal. If you can change the form, a multiple choice question prevents most of this. If the form is already out there, you clean afterwards. Either way, plan the cleaning before you collect, as described in from form to spreadsheet.

Keep originals, work on a copy

Before you change a single cell, duplicate the responses tab, or build the cleaned columns on a new tab that reads from the raw one. The raw tab is your evidence: if someone asks why a number is what it is, you can show what people actually typed. Never overwrite it.

The cleaning pass, in order

1. Standardize spacing and capitals: In a new column next to the answers, use =PROPER(TRIM(B2)). TRIM removes leading, trailing and doubled spaces, and PROPER capitalizes each word. Fill it down the column.

2. Look at what is left: Sort the cleaned column A to Z, or list the distinct values, and read them. You will see "Marketing", "Mktg" and "Markting" sitting together.

3. Build a lookup table: On a tab called Map, put every variant you found in column A and the category you want in column B, for example Mktg in A2 and Marketing in B2.

4. Categorize with a lookup: In the next column use =IFERROR(VLOOKUP(C2,Map!A:B,2,FALSE),"Review"). Rows whose variant is not in the table show Review, so nothing silently slips through.

5. Handle the Review pile: Read the Review rows, add each new variant to the Map tab, and watch the Review count fall to zero or to the few you want to decide by hand.

Worked example: fixing free-text answers for a report

Say ten people answered the team question and the raw column holds: Marketing, marketing , MKTG, Sales, sales, Sles, Mktg, Support, support team, Sales. After the PROPER and TRIM step you have Marketing, Marketing, Mktg, Sales, Sales, Sles, Mktg, Support, Support Team, Sales. Notice that MKTG became Mktg, which is why standardizing capitals first makes the Map table shorter: you only list Mktg once.

Now the report counts. If the category column is D and the data sits in rows 2 to 11, =COUNTIF(D2:D11,"Marketing") returns 4: the answers Marketing, marketing and MKTG, plus Mktg. Sales is also 4 (Sales, sales, Sles, Sales), and Support is 2 (Support and support team). The totals 4 + 4 + 2 = 10 match the ten responses, and that match is your check that nothing was dropped.

If the three counts do not add up to the number of responses, some row is in Review or has an empty cell. Fix that before you chart anything.

Duplicates and blanks

To find duplicate submissions, sort by name and timestamp and read neighbouring rows. Decide a rule, such as keeping the latest answer from each person, and write it in your notes. For blanks in an optional question, report them as "No answer" rather than ignoring them, so percentages use the right total.

Write down what you did

Add a tab called Notes and record three things: the date you cleaned, the rules you used (for example, keep the latest of two duplicate submissions), and how many rows ended in Review. A reader who sees a total of 10 responses and a total of 10 categorized answers will trust the report more when they can also see how you got there.

It also helps future you. In a month, when twenty more responses arrive, your Notes tab tells you which formulas to fill down and which Map entries to extend, so the second pass takes minutes.

Dates and numbers need the same care. A date typed as 3/4 can mean March 4 or 3 April depending on who typed it, and a number typed as "about 20" is text, not a number. If a column must be numeric or a date, set a validation rule on the form question next time, and for now convert on the copy and put anything unclear into Review.

Mistakes to avoid

• Fixing a typo directly in the raw tab and losing the original.

• Categorizing by eye and retyping, which cannot be repeated when 20 new responses arrive.

• Leaving Review rows out of the count and reporting totals that quietly miss people.

• Merging categories so aggressively that a real difference disappears.

Doing it faster

Once the Map tab exists, new responses cost you almost nothing: fill the formulas down and read the Review rows. The same pattern then feeds a pivot table that counts by category in a few clicks.

Frequently asked questions

Can I do this without formulas?

For a handful of rows, yes: sort and fix by hand on the copy. Formulas pay off when responses keep arriving, because the cleaning repeats itself.

What about answers that fit two categories?

Decide a rule first, such as primary team only, and apply it to everyone. Write the rule next to your results.