Guides

Month-end close with Copilot in Excel

Reconciliation, variance commentary and the review pack, using Copilot inside the workbook you already have and nothing leaving your tenant.

Hands Off Team · Editorial

Platform
Excel + Copilot
Time
Saves an afternoon per close
Level
Intermediate

This guide runs a month-end close inside Excel with Copilot: turn ranges into tables, reconcile two exports, confirm the breaks by hand, draft variance commentary, check every number against the table, build the review pack and save the prompts in the workbook. Nothing leaves your Microsoft 365 tenant.

New guides go to subscribers first. Get the next one by email →

What you need

Steps

  1. Turn every range into a table

    Copilot in Excel works on tables, not cell ranges. Select each dataset, press Ctrl+T, and give the table a real name in Table Design. Copilot will refer to tables by name, so 'Table3' costs you later.

  2. Reconcile the two exports

    Put both exports on separate sheets as named tables. Open Copilot and run Prompt #012, naming the tables and the match fields. Ask it to write the three result tables to a new sheet so you can audit the formulas it used.

    Prompt #012 · In the Library
    Attached are two files: [system A export] and [system B export]. Match the rows on [reference field] and amount. Give me three tables: matched, in A but not B, in B but not A. For each unmatched row, suggest the most likely reason (timing, rounding, duplicate, missing) in a few words.

  3. Check the breaks by hand

    Go through the 'in A not B' and 'in B not A' lists yourself. Copilot's suggested reason is a starting point, not a finding. Mark each break with the real cause in a new column; that column becomes your reconciliation note.

  4. Write the variance commentary

    Select the actual vs budget table and run Prompt #013. Paste one bullet from last month's commentary so it matches the tone your reviewers expect. Set the review threshold to whatever your materiality is.

    Prompt #013 · In the Library
    Write variance commentary for this actual-versus-budget table. Five bullets, biggest variances first, each with the amount, the percentage and the likely driver. Flag anything over [threshold]% for review. Match this tone: [paste one bullet from last month].

  5. Check every number in the commentary against the table

    Read each bullet with the table beside it. Amounts and percentages must match exactly. If one is off, don't fix the bullet; rerun the prompt and see whether the error repeats, because that tells you if the table is misread.

    Paste this
    For each bullet, show me the cells you used for the amount and the percentage.

  6. Build the review pack

    Open Claude or Copilot in Word, attach the commentary and the reconciliation note, and run Prompt #014. The first line should be the decision you need from the reviewer: sign-off, or a question about a specific break.

    Prompt #014 · In the Library
    Turn the attached analysis into a one-page memo. Put the decision you need from the reader in the first line. Then: context in three sentences, the numbers that matter in one table, risks in three bullets, and the recommendation. Keep everything that's in my notes and add nothing that isn't.

  7. Save the prompts in the workbook

    Add a sheet called Prompts with the exact text you used and the table names. Next month is copy, paste, rerun. The month after, hand it to someone else.

Before you rely on it

Used in this guide

Compared in

Frequently asked questions

Can Copilot in Excel do a month-end reconciliation?
It can match two exports and list the breaks, if every range is first turned into a named table. You still confirm the cause of each break yourself; the guide treats Copilot's explanation as a lead, not a finding.
Does using Copilot for the close send data outside the company?
Copilot in Excel runs inside your Microsoft 365 tenant, which is why this guide uses it rather than a consumer chatbot. One of the final checks is that nothing was pasted into a tool outside the tenant.
How do I keep AI-written variance commentary accurate?
Read each bullet with the table beside it; amounts and percentages must match exactly. If one is off, rerun the prompt rather than editing the bullet, so you learn whether the table is being misread.
How much time does this save per close?
The guide is written to save an afternoon per close once the tables and prompts are set up. The second month is copy, paste, rerun; the third can be handed to someone else.

Get the next guide first

Hands Off is a free newsletter on AI tools for traders and finance professionals, twice a week. New guides, prompts and shortlists go to subscribers before they go anywhere else.

This guide is an editorial walkthrough, not a tested configuration and not financial advice. Review anything a model produces before you act on it.

← All guides