How to clean inconsistent category names in survey data

You build the "Responses by department" bar chart and it has twenty-nine bars. The client's company has eight departments. Somewhere in the survey, the "Department" question was a free-text box — or a dropdown that let people type — and now Marketing exists as Marketing, marketing, Mktg, Marketing Team, Mrkting, and marketing with a trailing space. Every one of those is its own bar, its own row in the cross-tab, its own slice of a pie that should have eight slices. The chart isn't wrong. The categories underneath it were never standardized.

Why survey categories fracture and totals stay hidden

Open-text and permissive dropdowns capture what people type, not what they mean, so a single real category splinters into casing differences, abbreviations, typos, and stray spaces. The damage is worse than a messy chart: because each variant holds a slice of the responses, no single "Marketing" number is complete, and any percentage you calculate — "18% of respondents are in Marketing" — is wrong and unfalsifiable until you group the variants. A cross-tab with thirty rows where there should be eight is the tell.

The example: one department, six spellings

Here's the distinct list from a real Department column, with counts:

Raw value        Count
Marketing        41
marketing        12
Mktg              9
Marketing Team    6
Mrkting           2
marketing         3   <- trailing space

Six rows, one department, 73 real responses scattered so that the biggest "Marketing" bar shows only 41. Start by collapsing the free wins — casing and whitespace — with a normalize pass, =PROPER(TRIM(A2)), which folds marketing, MARKETING, and the trailing-space version into one Marketing. That alone drops you from six variants to three. The rest — Mktg, Mrkting, Marketing Team — are judgment calls a formula can't make, so they go into a mapping table where you decide, once, that all three mean Marketing. Resolve every response through it with =XLOOKUP(TRIM(A2), map[raw], map[clean], "REVIEW"), and anything you haven't classified returns REVIEW instead of quietly becoming a twenty-ninth bar.

Standardize survey categories in five steps

1. List distinct values with counts. A quick pivot on the category column shows you the real size of the fracture and which variants are worth merging.
2. Normalize the easy wins — trim and fix casing so pure formatting differences disappear before you make any judgment calls.
3. Build a mapping table for the genuine variants (Mktg → Marketing), and keep the client's real category list as the only allowed right-hand side.
4. Flag the ambiguous ones. Is "Sales & Marketing" one department or two? Don't guess — send the client the short list of unclear values and let them rule.
5. Lock the clean list. Once mapped, validate that every response resolves to one of the eight approved departments and nothing returns REVIEW.

The output is a chart with eight bars that sum to your response total, and a mapping table that documents exactly how you got there.

The merges are the client's call, not yours

The technical part is easy; the risk is silently merging categories that shouldn't be merged. If you fold "Marketing" and "Growth" together because they look related, you may have erased a distinction the client's whole survey was designed to measure. Standardizing survey categories is really about surfacing every ambiguous variant and letting the client decide, then recording those decisions so the same survey next quarter is cleaned the same way. Your job is to make the choices visible, not to make them alone.

Write those decisions down where the client can see them. "Merged Mktg, Mrkting and Marketing Team into Marketing; kept Growth separate at your request" is one line that turns an invisible judgment call into documented work. Documented judgment is exactly what a client cannot get from a raw survey export, and it's the difference between a chart they half-trust and a chart they can present to their own boss — which is why that line belongs on the invoice, not buried in your working file.