How to Use Copilot to Build a Reviewable Category Map for Messy Excel Text

Customer reasons such as late shipment, delivery delayed, and parcel still not here can describe the same problem. In a PivotTable or SUMIFS, however, they remain separate until you standardize them.

A Copilot category map gives you a faster first pass without trusting AI-generated labels blindly. Use Copilot to suggest themes, then approve every mapping in Excel tables that colleagues can inspect, correct, and reuse.

Quick Fix: use Copilot for suggestions and Excel for control

1. Store raw descriptions in an Excel Table named tblTickets.
2. Create a cleaned lookup key so stray spaces do not cause missed matches.
3. Ask Copilot to suggest themes and draft categories from a representative sample.
4. Create tblCategories, a separate maintained list of allowed reporting categories.
5. Create tblMap, where each cleaned raw description has one human-approved category.
6. Use XLOOKUP to return the approved category; send unknown descriptions to Needs review.
7. Use Data Validation on the mapping table to help prevent inconsistent typed labels.

This is the most reliable approach when categories affect totals, service metrics, or decisions that need an audit trail. Microsoft says Copilot can identify themes in text and add analysis to a workbook, but users should review and verify generated output. See Get data insights with Copilot in Excel.

Why messy text breaks reports

Excel treats text literally. A PivotTable sees refund request and requesting a refund as different items. SUMIFS does the same unless both rows have a shared reporting category.

Do not overwrite the original wording. Keep it as evidence, add a cleaned lookup key, and return an approved category in a separate column. This is safer for imported data that needs ongoing standardization; Microsoft also recommends cleaning inconsistent data before analysis in its guide to cleaning Excel data.

Set up the sample workbook

Put the source data in an Excel Table named tblTickets. Select the data, press Ctrl+T, then use Table Design > Table Name.

Ticket ID	Raw reason	Amount
T-1041	Package has not arrived	85
T-1042	charged twice on card	120
T-1043	delivery delayed again	60
T-1044	need refund for duplicate payment	120
T-1045	where is my order?	40

Your goal is to return controlled labels such as Delivery delay and Duplicate charge, while leaving unclear rows visible for review.

Clean the lookup text before categorizing it

Add a column named Lookup key to tblTickets. This removes normal and nonbreaking spaces, removes nonprinting characters, and trims leading and trailing spaces.

=TRIM(CLEAN(SUBSTITUTE([@[Raw reason]],CHAR(160)," ")))

For T-1043, the lookup key becomes delivery delayed again even if the imported value ends with an extra space. Keep Raw reason unchanged; use Lookup key only for matching.

Ask Copilot for category suggestions, not final decisions

Select a representative sample that includes common wording and awkward edge cases. Ask Copilot for a draft that you can compare with the source text:

Review the values in the Raw reason column.
Suggest 4 to 6 short reporting categories.
For each distinct raw reason, propose one category.
Keep the original raw reason beside the proposed category.
Flag ambiguous reasons as Needs review.

Do not ask Copilot to replace the source data. Its useful role is finding possible patterns quickly. Microsoft notes in its Copilot in Excel FAQ that Copilot can make mistakes or misinterpret information, so review, edit, and verify every proposed mapping.

Before accepting a label, ask: Would two people reading this description reasonably choose the same category? If not, choose Needs review or document a business rule.

Create a separate approved category list

On a separate sheet, create this small table and name it tblCategories. This is the maintained source of allowed labels; it must not be generated from the mapping table.

Approved category
Delivery delay
Duplicate charge
Needs review

When the business approves a genuinely new category, add it to tblCategories first. Then it becomes available for selection in the mapping table.

Create the reviewable mapping table

Create a second table named tblMap. Add each distinct raw description that needs a decision. Use Copilot’s result only as a draft for the approved category.

Raw reason	Lookup key	Approved category	Review note
Package has not arrived	Package has not arrived	Delivery delay	Approved
delivery delayed again	delivery delayed again	Delivery delay	Approved
where is my order?	where is my order?	Needs review	Could be tracking or delay
charged twice on card	charged twice on card	Duplicate charge	Approved
need refund for duplicate payment	need refund for duplicate payment	Duplicate charge	Approved

In tblMap[Lookup key], use the same cleanup formula:

=TRIM(CLEAN(SUBSTITUTE([@[Raw reason]],CHAR(160)," ")))

Keep one row per lookup key. The map is your control point: it records exactly how source wording becomes a reporting category. For more on table formulas, see Excel structured references.

Apply approved categories with XLOOKUP

Add an Approved category column to tblTickets, then enter:

=XLOOKUP([@[Lookup key]],tblMap[Lookup key],tblMap[Approved category],"Needs review")

Excel finds the cleaned text in tblMap and returns the approved label. A new or unmatched description returns Needs review, making the gap visible instead of silently assigning a category.

For this example, T-1041 and T-1043 return Delivery delay; T-1042 and T-1044 return Duplicate charge; T-1045 remains Needs review.

Older Excel alternative: INDEX and MATCH

XLOOKUP is not available in Excel 2016 or Excel 2019. In those versions, use this formula instead:

=IFNA(INDEX(tblMap[Approved category],MATCH([@[Lookup key]],tblMap[Lookup key],0)),"Needs review")

MATCH finds the lookup key and INDEX returns its approved category. IFNA changes only a no-match result to Needs review. For the modern function and its arguments, see XLOOKUP basics.

Use Data Validation for approved labels

Data Validation helps prevent people from typing variations such as Delivery Delays, delivery delay, and Late delivery into the mapping table.

1. Open Formulas > Name Manager > New.
2. Enter CategoryList as the name.
3. In Refers to, enter:

=tblCategories[Approved category]

4. Select tblMap[Approved category].
5. Choose Data > Data Validation.
6. Set Allow to List.
7. Set Source to =CategoryList, then select OK.

A named range tied to the separate category table is more maintainable than using a spilled list from the same column being validated. Add new allowed labels to tblCategories, not directly to the validation source.

Important: Data Validation helps with typed entries, but some paste operations can bypass it. Restrict editing where appropriate, review pasted mapping changes, and check the mapping table before refreshing reports. Do not apply validation to the raw source column.

For a deeper review process, use this checklist for auditing Excel Data Validation.

How to handle a new raw description

Data Validation does not classify new source text automatically. When a new description appears:

1. Filter tblTickets[Approved category] for Needs review.
2. Add the raw description to tblMap[Raw reason].
3. Let the mapping table calculate its Lookup key.
4. Select an existing approved category, or first add a newly approved category to tblCategories.
5. Add a short review note, then confirm the ticket now returns the expected result.

This gives every new classification a visible decision trail.

Common mistakes with a Copilot category map

Issue	Cause	Solution
Different categories appear for the same issue	Copilot suggestions were accepted without consolidation.	Use a short controlled list in tblCategories and review the map.
XLOOKUP returns Needs review	The cleaned description is missing from tblMap.	Add a mapping row and choose an approved category after review.
A match fails despite similar wording	Extra spaces or nonprinting characters remain in imported text.	Use the cleanup formula in both tables and compare the lookup keys.
One reason returns an unexpected category	tblMap contains duplicate lookup keys; the first match is returned.	Remove duplicates and keep one approved mapping per lookup key.
An inconsistent category appears after pasting	A paste operation bypassed Data Validation.	Review pasted values, reapply the approved label, and protect the mapping area if needed.

When to use a different method

• Use manual review only when the dataset is small or a wrong category has high consequences.
• Use Power Query cleaning steps when imported text has predictable case, spacing, or formatting problems.
• Use a broader rule table only when keywords and exceptions are documented and tested.

AI can reduce the time needed to find patterns, but the approved map remains the workbook’s source of truth. That separation supports safe AI use with Excel data: draft with AI, verify with people, and preserve the logic in the workbook.

Final checklist

• Keep raw text separate from cleaned lookup keys and reporting categories.
• Ask Copilot for candidate themes from a representative sample.
• Maintain allowed labels in a separate tblCategories table.
• Approve each raw-description mapping in tblMap.
• Use XLOOKUP, or INDEX/MATCH in Excel 2016 or 2019.
• Send unmatched values to Needs review.
• Review pasted changes and duplicate lookup keys before refreshing reports.

FAQ

Can Copilot categorize every new row automatically?

Copilot can suggest themes, but a new raw description still needs a reviewed mapping row before it becomes approved reporting data.

Why not use Find and Replace?

Find and Replace can help with one-off cleanup, but it changes source values and is harder to audit. A mapping table preserves the original wording and the approved category.

Should I replace Needs review with a blank?

No. A visible exception tells you exactly which rows still need a decision.

Sources

• Microsoft: Get data insights with Copilot in Excel
• Microsoft: Frequently asked questions about Copilot in Excel
• Microsoft: Top ten ways to clean your data