Automatically Categorize Free-Text Columns in Excel

You have a column of free-text in Excel: support tickets, survey responses, product reviews, expense descriptions, or job titles. You need each row tagged with a category so you can pivot, filter, or chart it. Manually reading thousands of rows is not an option, and Excel's built-in formulas only work if you can write exact rules for every variation of wording.

That is the gap AI fills. A language model can read the full sentence, understand intent, and assign a category even when the text is messy, misspelled, or phrased five different ways. You paste your column, give the model a category list, and get a clean labeled column back that you can drop into a PivotTable.

This page shows the exact prompt to use, four model outputs side by side so you can pick the one that fits your data, and the patterns that make auto-categorization reliable at scale. If you just want a working recipe, scroll to the prompt. If you want to understand why certain phrasings fail, read on.

When to use this

Use AI categorization when your text is unstructured, your categories require understanding context, and rule-based formulas like IF/SEARCH would need dozens of nested conditions. It works best when you have a defined category list, or want the model to propose one, and your row count is in the hundreds to low tens of thousands.

• Tagging support tickets into Billing, Bug, Feature Request, How-To, Churn Risk
• Classifying survey open-ends into themes like Pricing, Onboarding, Performance
• Sorting expense line items into GL accounts based on vendor and description
• Grouping job titles into standard roles (Engineer, Designer, Manager, Sales)
• Mapping product reviews to sentiment plus a topic such as Shipping or Quality

When this format breaks down

• Regulated classifications where every label needs a human audit trail, like medical coding or legal case type
• Columns with fewer than 20 rows where manual tagging is faster than prompt setup
• Highly ambiguous text where even a human cannot agree on a single category without more context
• Datasets containing PII or confidential data that cannot leave your environment without approval

What makes these work

1. 01
   Give a fixed category list

   Free-form categorization produces inconsistent labels like 'Billing', 'Payment', 'Invoice Issue' for the same concept. Always supply the exact list of allowed labels in the prompt and tell the model to pick only from that list. This makes PivotTables clean on the first try.

2. 02
   Include an Other bucket

   Force-fitting every row into your list creates bad data. Add 'Other' or 'Unclear' as an allowed label and instruct the model to use it when confidence is low. Review the Other rows to discover missing categories you should add.

3. 03
   Number your input rows

   Prefixing each row with a number (1., 2., 3.) gives the model an anchor so outputs stay aligned with inputs. When you paste 500 rows and get 498 labels back, numbering makes the mismatch obvious and recoverable.

4. 04
   Batch in chunks of 50 to 200

   Very long inputs cause models to drift, truncate, or hallucinate labels. Split your column into batches of 50 to 200 rows, run each batch, and concatenate the results. This also keeps you under token limits on most models.

More example scenarios

1. Cannot log in after password reset
2. How do I export my invoices to CSV
3. Charged twice for October subscription
4. Dashboard loads forever on Safari
5. Can you add dark mode to the mobile app

1. Bug
2. How-To
3. Billing
4. Bug
5. Feature Request

Each label maps to the provided category list. Rows 1 and 4 describe broken functionality, row 2 is a usage question, row 3 is a payment issue, row 5 is a product suggestion.

1. Uber to client meeting SFO
2. Dinner with prospect at Quince
3. Zoom Pro annual renewal
4. United flight DEN to ORD
5. Staples printer paper and toner

1. Ground Transportation
2. Client Entertainment
3. Software Subscriptions
4. Air Travel
5. Office Supplies

Labels follow a standard expense chart of accounts. Vendor names like Uber and United drive travel categories, while context words like prospect and client disambiguate meals.

1. Arrived two weeks late and box was crushed
2. Fabric feels cheap and the seams came apart after one wash
3. Love the color but the size runs small
4. Customer service ignored my three emails
5. Exactly as described, fast shipping

1. Shipping
2. Quality
3. Fit/Sizing
4. Customer Service
5. Shipping (Positive)

Each review is tagged with the dominant topic. Row 5 is flagged positive because multi-topic reviews would otherwise muddle a negative-skewed report.

1. Sr. Full-Stack Developer II
2. VP of People & Culture
3. Growth Marketing Lead
4. Chief of Staff to the CEO
5. Account Executive, Mid-Market

1. Engineering
2. HR
3. Marketing
4. Operations
5. Sales

Messy title variants collapse into five standard functions suitable for headcount reporting. Seniority and specialty are stripped because the category list asked only for function.

1. Onboarding took forever and nobody followed up
2. Best tool we have bought this year
3. Pricing jumped 40 percent at renewal with no warning
4. Integration with Salesforce saved my team hours
5. UI is confusing for new hires

1. Onboarding | Negative
2. Overall | Positive
3. Pricing | Negative
4. Integrations | Positive
5. Usability | Negative

Each response gets a theme and a sentiment separated by a pipe so you can split into two Excel columns with Text to Columns.

Common mistakes to avoid

• Vague category definitions

  Labels like 'Issue' or 'Question' mean different things to different models. Define each category in one sentence inside the prompt, for example 'Bug: something that used to work is now broken'. Ambiguity is the single biggest source of mislabeling.

• Mixing tone and topic in one label

  Asking for 'Negative Billing' as a single label collapses two dimensions. Split sentiment and topic into two output columns so you can slice either independently in Excel.

• Skipping a spot check

  Models can be 95 percent accurate and still produce 50 wrong labels in a 1000-row file. Always sample 20 to 30 rows manually before trusting the whole column, especially on the first run with a new category list.

• Pasting results without validation

  If the output has more or fewer rows than the input, alignment breaks silently and every downstream pivot is wrong. Use a COUNTA check on the output column before running analysis.

• Forgetting edge cases in the prompt

  Empty cells, non-English text, and single-word entries like 'ok' often get miscategorized. Tell the model explicitly how to handle these: return 'Blank', 'Non-English', or 'Insufficient Info' instead of guessing.

Related queries