Use Copilot in Excel to Clean Up a Data Migration Mapping Workbook

What This Does

Copilot in Excel reads your worksheet and finds duplicate rows, inconsistent formatting, and formula errors on request, and it can fix them directly in the sheet once you approve the change. For a data migration mapping workbook that grows across dozens of tabs and modules over the course of a project, this catches the kind of drift a manual scan misses.

Before You Start

• Your firm or the client has purchased the Microsoft 365 Copilot add-on. In-app Copilot inside Excel, Word, PowerPoint, and OneNote now runs on a paid license layered on top of a qualifying Microsoft 365 plan (available on some Microsoft 365 business plans; check with your firm's IT or Microsoft's pricing page). Look for a Copilot button on the Home tab. If it is missing, the license has not been assigned to your account.
• You are working in the copy of the workbook that lives in the tenant governing this engagement's client data policy (your firm's or the client's own Microsoft 365 tenant), not a personal Microsoft account.
• The mapping workbook contains structural information (legacy field names, target field names, data types) rather than real customer, vendor, or employee records, since Copilot in Excel processes whatever is in the open workbook.

Steps

1. Open the Copilot pane

On the Home tab, click the Copilot button to open the Copilot pane along the right side of the window. Switch it from chat mode to Allow editing mode if you want Copilot to make changes directly in the sheet rather than only describe them.

2. Ask it to find duplicates and formatting problems

Type a prompt describing what to check, and send it. Ask it to flag every row where the target field name does not match anything on your data dictionary tab, or to find and remove exact duplicate mapping rows.

3. Review proposed changes before accepting them

Copilot in edit mode shows proposed changes for you to accept or reject rather than applying them silently. Check a sample of the flagged rows against the actual data dictionary tab before accepting a bulk change. Copilot can misjudge a near-match as a mismatch.

4. Ask for a lookup formula instead of a one-time fix

For a check you will run again as new fields get added, ask Copilot to build a formula (an XLOOKUP or a nested IF, for example) rather than a one-time cleanup. Ask it to explain what each part of the formula does so you can maintain it yourself later.

Real Example

Scenario: Your NetSuite data migration workbook has grown to nine tabs, one per module, and the target field names on the newer tabs do not consistently match the naming convention used on the data dictionary tab.

What you type: "Check the Target Field column on every tab against the Field Name column on the Data Dictionary tab. Flag any row where there is no exact match, and suggest the closest likely match for each one."

What you get: Copilot returns a flagged list of mismatches across all nine tabs, several of which are legitimate typos and a few of which are near-matches your data dictionary is simply missing. You accept the typo fixes and add the missing entries to the dictionary yourself.

Tips

• Ask for an explanation of any formula Copilot writes rather than pasting it in blind. Explain Formula breaks down what each part does, which matters if a teammate has to maintain the sheet after you move to the next module.
• Run the duplicate check again after every major batch of new fields rather than waiting until the workbook is nearly done.
• If a suggested fix looks wrong, reject it and rephrase the request with a more specific rule instead of manually undoing a bulk edit.

Tool interfaces change. If a button has moved, look for similar AI/Copilot options on the Home tab.