Use Excel Copilot to Build Reconciliation Formulas

What This Does

Excel Copilot lets you describe what you want a spreadsheet to do in plain English, and it writes the formula or creates the analysis for you. No formula syntax knowledge required.

Before You Start

• You have Microsoft 365 (personal or work subscription with Copilot access)
• You're using Excel desktop app or Excel online (office.com)
• Your spreadsheet has data in named columns (date, description, amount, etc.)
• Time needed: 10-15 minutes for first use
• Cost: Included in Microsoft 365 Personal ($70/year) or Business plans with Copilot

Steps

1. Open the Copilot panel

With your spreadsheet open, look for the Copilot button in the Home ribbon (a star/sparkle icon). Click it. A chat panel opens on the right side of your screen.

What you should see: A Copilot sidebar with a chat input box at the bottom and some suggested prompts at the top.

Troubleshooting: If you don't see the Copilot button, your Microsoft 365 subscription may not include it. Check under Help → About Excel to confirm your subscription level.

2. Ask Copilot to write a formula in plain English

Click in the Copilot chat box and type what you need:

Example request: "Write a formula that checks if the amount in column C matches any amount in the 'Bank Statement' sheet column B, and puts MATCHED or UNMATCHED in column D."

What you should see: Copilot writes the formula and shows a preview. It usually includes a brief explanation of what the formula does.

3. Insert the formula

Copilot will offer an Insert button or tell you exactly which cell to paste the formula into. Click Insert (or copy-paste it manually) into the correct cell.

What you should see: The formula appears in your spreadsheet and starts working immediately.

Troubleshooting: If you get a #SPILL! error, there are values in the cells below where the formula is trying to output results — clear those cells first.

4. Ask Copilot to explain or modify the formula

If you want to understand what the formula does (so you can fix it later if needed), ask: "Explain this formula in plain English." Copilot will break it down step by step.

To modify it, just tell Copilot: "Change the formula to also check if the dates match within 3 days of each other."

5. Use Copilot for data analysis too

Beyond formulas, you can ask Copilot to analyze your data:

• "Highlight all transactions over $500"
• "Create a pivot table showing total spending by expense category"
• "Find any duplicate transactions based on date and amount"
• "Sort the data by amount, largest to smallest, and add a running total column"

What you should see: Copilot applies the changes directly to your spreadsheet (with your confirmation first).

Real Example

Scenario: You're doing a bank reconciliation in Excel. You have your QuickBooks export in Sheet1 and your bank statement in Sheet2. You need to find which transactions match and which don't.

What you type in Copilot: "I have two sheets of transaction data. Sheet1 has QuickBooks records (date in A, description in B, amount in C). Sheet2 has bank statement records (date in A, description in B, amount in C). Write formulas in Sheet1 column D to show MATCHED if the transaction appears in Sheet2, or UNMATCHED if it doesn't. Match on date and amount."

What you get: Copilot writes the VLOOKUP or INDEX/MATCH formula needed, inserts it, and your reconciliation workpaper is done in 5 minutes instead of 30.

Tips

• The more specific your request, the better. Include column letters, sheet names, and what you want in the output
• If Copilot's first attempt isn't quite right, keep refining in the chat — say "That's close but I need it to also..." and it iterates
• Use Copilot to spot-check your own formulas: paste a formula and ask "Does this formula correctly [describe what it should do]?" Copilot will find errors you missed

Tool interfaces change — if Copilot has moved, look for AI features in the Home ribbon or under the Help menu.