Use Copilot in Excel to tie out planning software values against statements
For Paraplanners ·
What This Does
You have an export of account values from the planning software and a second list you typed from the latest statements. Copilot in Excel can compare the two tabs and tell you which account labels differ or are missing. It points you at the lines to check. It does not decide which number is right.
Before You Start
- You have Excel for Microsoft 365 open and can see a Copilot icon in the lower-right corner of the Excel window. Microsoft's help page says Copilot may not appear if it is not included with your subscription or your organization's settings turn it off. Ask your IT admin if it is missing, and check Microsoft's own pricing and licensing pages for what your firm has.
- Your chief compliance officer (CCO) has approved Microsoft 365 Copilot for client data. If nobody has told you it is approved, ask before you paste anything.
- You use a household code and account labels, never client names. Leave out account numbers, Social Security numbers, dates of birth and addresses. A label such as "Client A Rollover IRA" is enough to find the line later.
- You have the statement PDFs open in another window. Every flag gets checked against them.
Steps
1. Set up two tabs
Name the first tab Export. Put the account label in column A and the value from the planning software in column B, with a header row. Name the second tab Statement. Put the account label in column A, the statement date in column B and the value you typed from the statement in column C.
Type the statement values by hand from the PDF. Do not let an AI tool read the PDF for you in this guide. The point is an independent second source.
2. Open Copilot and pick a mode
Select the Copilot icon in the lower-right corner of Excel. Microsoft's help page says Copilot in Excel opens in edit mode by default, and that it also offers plan mode and chat only mode. Chat only mode analyzes your workbook and does not change it. For a tie-out, look in the Copilot pane for the mode choice and pick chat only so nothing in your workbook is edited. If you cannot find the mode choice, work on a copy of the file.
3. Ask for the differences
Type something like this:
The Export tab has account labels in column A and values in column B. The Statement tab has account labels in column A, statement dates in column B and values in column C. List every account label whose value differs between the two tabs, showing both values and the difference. Then list every label that appears on one tab and not the other. Do not correct or recalculate anything.
4. Run your own check next to it
Copilot is the second pair of eyes. Your formula is the first. In cell D2 of the Statement tab, enter:
=IFERROR(C2-XLOOKUP(A2,Export!A:A,Export!B:B),"Missing from Export")
Fill it down. A zero means the values match. A number is the statement value minus the export value. The text means the label is not on the Export tab. Compare this column with Copilot's list. If they disagree, trust neither until you look at the statement.
5. Check every flag against the statement
For each flagged line, open the statement PDF and read the value yourself. Confirm the date as well, because an export taken on a different day than the statement explains many small differences. Only after that do you correct the planning software or tell the advisor. Never change a record because Copilot said so.
Real Example
Scenario: You are tying out Household 112 before a review meeting. All names and figures below are invented, and the values have no currency symbol.
| Account label | Export value | Statement value (dated 2026-09-30, invented) |
|---|---|---|
| Joint brokerage | 180,000 | 180,000 |
| Client A Roth IRA | 95,000 | 95,000 |
| Client B Roth IRA | 60,000 | 60,000 |
| Client A Rollover IRA | 240,000 | 204,000 |
| Client B Traditional IRA | 310,000 | 310,000 |
| Family trust account | 150,000 | 151,500 |
| Client A 401(k) | 275,000 | 275,000 |
| Client B HSA | not on Export tab | 12,000 |
What you get: Copilot lists three items. Client A Rollover IRA differs, and your column D shows -36,000. Family trust account differs, and column D shows 1,500. Client B HSA is missing from the Export tab. You open the statements. The Rollover IRA difference looks like two digits swapped, 240,000 against 204,000, so you check the statement line again and note it for the planning software. The trust difference may be a timing issue, so you check the export date. The HSA needs to be added to the plan data, and you ask the advisor whether it belongs in the plan.
Tips
- Keep the account labels identical on both tabs. A stray space makes XLOOKUP report "Missing from Export" for an account that is really there.
- Ask for a count first ("How many labels are on each tab?"). If Copilot's count does not match your own, fix the tab before trusting the comparison.
- Keep the file in the firm's approved storage. A tie-out workbook with balances in it is a client record.
Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area. Source for the Copilot steps: support.microsoft.com, "Get started with Copilot in Excel", read 2026-10-06.