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 or Excel on the web)
• You're signed into a Microsoft 365 account with Copilot access (most organizations buy the Microsoft 365 Copilot add-on, smaller ones may have free basic access; a personal subscription includes Copilot too, ask IT if you're not sure)
• 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 icon in the lower-right corner of the Excel window, it looks like a small sparkle icon. Microsoft moved Copilot off the Home ribbon in 2026, so this corner button is now the entry point. Click it. A chat panel opens on the right side of the screen. Keyboard: F6 jumps to the button anywhere; Alt + C (Windows) and Cmd + Ctrl + I (Mac) are rolling out. If you prefer the old placement, right-click the corner button and choose Move to ribbon.

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, press F6 to cycle to it or look for a similar AI option in the same corner area.