Use Excel's Copilot to Reconcile Position Data

What This Does

Copilot in Excel reads your two data sets — custodian export and internal system records — and automatically finds mismatches, generates comparison formulas, and highlights discrepancies, turning a 45-minute manual reconciliation into a 5-minute review.

Before You Start

• You have Microsoft Excel open (desktop app, not browser)
• You're signed into a Microsoft 365 account (Business Standard or above includes Copilot)
• You have your two data sets in the same workbook — one per sheet or side by side
• Your data has clear column headers (e.g., "Account Number," "Custodian Position," "Internal Position")

Steps

1. Find the Copilot feature

Open your Excel workbook. Look for the Copilot button on the right side of the Home ribbon — it looks like a small sparkle/star icon next to "Autosum." Click it. A chat panel opens on the right side of the screen.

2. Describe what you want to compare

In the Copilot chat box, type a plain English description of your task. Be specific about column names.

What to type: "Compare the values in the 'Custodian Qty' column against the 'Internal Qty' column. Add a new column called 'Status' that shows MATCH if they're equal, or DISCREPANCY if they differ. Also add a 'Difference' column showing the numeric difference."

3. Review the suggested formula

Copilot will show you the formula it plans to use and ask if you want to apply it. Review the formula briefly — it should reference the right columns. Click Insert column to apply it.

4. Review and filter discrepancies

Once the Status column is populated, click the dropdown arrow on the Status column header and filter for "DISCREPANCY" to see only the problem rows. You can then ask Copilot: "Highlight all DISCREPANCY rows in red" to make them visually obvious.

Real Example

Scenario: You have 300 rows of positions — Column A is account number, Column B is the custodian's reported quantity, Column C is your internal system's quantity. They should match. You need to find which accounts don't.

What you type: "Compare column B (Custodian Qty) to column C (Internal Qty). Add a column D called Status showing MATCH or DISCREPANCY, and column E showing the difference (B minus C)."

What you get: Copilot generates an IF formula in D and a subtraction formula in E, applies them to all 300 rows. You filter column D for DISCREPANCY and see 7 rows need investigation — in 3 minutes instead of 45.

Tips

• Always have column headers — Copilot uses them to understand your data structure
• If Copilot generates the wrong formula, describe more specifically which columns to compare ("Column B, header is Custodian Qty, rows 2 through 301")
• You can ask follow-up questions in the same chat: "Now sort by the Difference column, largest to smallest"

Tool interfaces change — if the Copilot button has moved, look for it under Home or in the View ribbon.