Write an Excel formula to categorize records automatically
Builds the formula and lookup table that auto-assigns categories, bands, or tiers to each row based on your rules.
The prompt
Write an Excel formula that automatically assigns a category to each row in my data.
What I want to categorize: [e.g. "customers by revenue tier", "expenses by type based on the description", "tickets by priority", "invoices by days overdue"]
Columns available: [PASTE headers and 10-20 sample rows]
My rules: [e.g. "over $50k = Tier 1, $10-50k = Tier 2, under $10k = Tier 3" or "if description contains 'Uber' or 'taxi' = Travel"]
My tool: [Excel 365 / Excel 2016-2019 / Google Sheets]
Do this:
1. Restate my rules as a clear decision table and point out gaps or overlaps (values that fit two categories or none, boundary values like exactly $10k).
2. Choose the best approach and say why:
- Number bands: a small lookup table with XLOOKUP approximate match (or VLOOKUP TRUE), not long nested IFs.
- Text keywords: a keyword table with SEARCH and ISNUMBER, or a FILTER/XLOOKUP pattern.
- Multiple conditions: IFS with AND/OR, ordered from most to least specific.
3. Keep thresholds and keywords in a separate table, not hard-coded, so I can change them without editing formulas.
Return:
1. The decision table.
2. The lookup table layout to create (sheet name, columns).
3. The exact formula for the first data row, ready to fill down.
4. A test: 5 sample rows with the expected category, including edge cases.
5. How to catch rows that don't match any rule (e.g. return "Review").
6. A Google Sheets version if it differs.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- PASTE headers and 10-20 sample rows
- Excel 365 / Excel 2016-2019 / Google Sheets
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Write an Excel formula to extract text, numbers, or dates from messy cellsPulls the piece you need out of combined or messy cells, like codes, names, domains, or dates, with a formula that handles exceptions.
- Write a monthly IT and security report for leadershipTurns ticket, uptime, and security metrics into a short report showing trends, risks, and decisions needed.
- Write an analytics event tracking plan for a featureAn engineer-ready event spec with names, triggers, properties, and the questions each event answers.
- Write commentary for a dashboard or KPI reportWrites the 'so what' narrative executives need alongside a dashboard — for sales, marketing, HR, finance, ops, or support.