Hands-On Training Labs — From Data Prep to Month-End Close
Each lab is collapsible — click the title to expand or collapse. Every prompt has a Copy button so you can paste it straight into Excel Copilot. Work through Lab 0 first for setup, then complete the labs in order — they build on each other using the same company scenario.
By the end of this training, you will be able to:
Edit with Copilot is an AI assistant built into Excel. Instead of writing formulas or building charts yourself, you describe what you want in plain English — and Copilot makes the change directly on the sheet. You stay in control: every change can be reviewed, kept, or undone with Ctrl+Z.
Key idea: Copilot doesn't just tell you what to do — it does it for you. And every formula it writes is a real Excel formula you can inspect and edit.
Ctrl+T to convert a data range into a table. Copilot performs significantly better with tables — it understands column names and data boundaries automatically.COPILOT() function, which is currently in Preview. You'll need to be on the right Microsoft 365 channel (Beta / Current Channel Preview) for that lab to work. Labs 0–3 and 5 work in standard channels.Download the two Excel workbooks used in this training. Save them to your OneDrive before opening.
Before the session: verify both files open correctly, confirm Copilot is available in the Excel ribbon, and pre-load the first workbook to minimize setup time. Walk through the Copilot pane orientation (prompt box, suggestions, Apply/Insert Column buttons) before Lab 1.
Every finance team's month starts the same way: a raw data pull from the ERP that needs cleanup before anyone can analyze it. Dates are inconsistent, account codes need mapping to reporting categories, and descriptions are cluttered. This is the kind of repetitive prep work where Copilot saves hours — and where most people don't realize AI can help.
Emphasize that Copilot writes real Excel formulas. After each exercise, have participants click into the new column and check the formula bar. They'll see functions like YEAR(), IF(), and TEXTAFTER() — formulas they can inspect, edit, and learn from.
Your company's fiscal year starts July 1. You need fiscal period columns for reporting.
→ Click "Insert Column" to add it.
Now add the quarter:
→ Click "Insert Column"
Leadership doesn't read 4-digit account codes — they need categories like "Revenue" and "Operating Expenses."
→ Click "Insert Column"
IF() or IFS() — formulas that would take most people several minutes to write and test manually. This is a great example of Copilot handling tedious-but-necessary logic.The Description field contains valuable information buried in free text. Let's pull out the vendor or counterparty name.
→ Click "Insert Column"
Your team's policy requires review of any journal entry over $10,000. Let's flag them automatically.
→ Click "Insert Column"
Now make it visual:
Budget-vs-actuals variance analysis is the single most common finance reporting workflow. Every department head, every controller, every CFO sees a version of this report every month. The mechanics — calculating variances, flagging outliers, building summaries — are straightforward but time-consuming. Copilot handles the mechanics so you can focus on the story the numbers tell.
First, let's add the core variance calculations.
→ Click "Insert Column" for each.
Roll up the detail into a leadership-ready department summary.
→ Click "Add to a new Data Sheet"
The CFO always asks: "What's driving the miss?" Let's answer that proactively.
Your finance policy requires explanation for any line item with variance exceeding ±10% of budget.
A chart makes the variance story immediately clear in presentations.
This is a good moment to pause and ask participants: "How long would this entire sequence — variance formulas, department summary, top 5 analysis, conditional formatting, and chart — take you manually?" Most finance teams say 45–60 minutes. With Copilot, the class just did it in under 10.
Cash is oxygen. AR aging reports drive collections strategy, reserve calculations, and cash flow forecasting. Building one requires date math, bucket logic, and prioritization — exactly the kind of multi-step work where Copilot shines. This lab takes you from raw invoice data to a collections-ready report with priorities.
First, determine how much is still owed and how overdue each invoice is.
→ Click "Insert Column"
→ Click "Insert Column"
Standard aging buckets are the foundation of any AR report.
→ Click "Insert Column"
IF() or IFS() — check the formula bar→ Click "Add to a new Data Sheet"
The collections team needs to know where to focus. Large, old invoices get priority.
Until now you've used Copilot through the side pane. The new COPILOT() function puts AI directly inside a cell — chainable with PIVOTBY, TEXTJOIN, FORMULATEXT, and everything else in Excel. For finance, this is transformative: imagine generating variance commentary, categorizing transactions, or rating risk — all as live formulas that update when data changes.
COPILOT() is currently in Preview. You need a Microsoft 365 Copilot license and the right update channel (Beta / Current Channel Preview). If you get #NAME?, the function isn't available in your Excel build yet.Start with a high-level narrative summary of the entire P&L. Click into an empty cell below or to the right of the data.
Every month, finance teams write commentary explaining why each major line item is over or under budget. This is the single biggest time-sink in the close process. Let Copilot draft it.
For the monthly finance package, you need organized commentary by section. Combine COPILOT() with TEXTJOIN() for clean formatting.
Now let's get Copilot to assess which variances pose the most risk — useful for triage in close meetings.
This is a powerful learning technique — ask Copilot to explain what a formula does.
Go to the "VarianceReport" sheet. Click on any cell that has a Variance % formula. Note its cell reference (e.g., F4). Then in an empty cell:
The COPILOT() function represents a fundamental shift: AI is no longer a sidebar tool — it's part of the spreadsheet fabric. For finance teams, this means variance commentary, risk ratings, and even draft audit notes can be generated as formulas that stay live and update with the data. Emphasize: this is the future of the monthly close.
Bank reconciliation is the gatekeeper of the monthly close. Until the bank rec is clean, you can't close the books. It's also one of the most manual, error-prone tasks in accounting — matching hundreds of transactions between two sources, hunting for timing differences, and chasing down discrepancies. Copilot can accelerate the matching and surface exceptions that need human investigation.
The first step in any bank rec is finding what doesn't match between the two sides.
Sometimes transactions exist on both sides but with slightly different amounts (data entry errors, transpositions).
Duplicates in either the bank statement or the general ledger create reconciliation nightmares.
Switch to the "CloseChecklist" sheet. Let's use Copilot to analyze close readiness.
This lab demonstrates Copilot's ability to compare datasets — a fundamental accounting skill. While Copilot won't replace your reconciliation tool (Blackline, Trintech, etc.), it can serve as a quick first-pass analysis or work on ad hoc reconciliations where those tools aren't configured. The key message: Copilot handles the comparison mechanics; you bring the accounting judgment.
| Issue | Solution |
|---|---|
| Copilot button doesn't appear in Excel | Ensure the file is saved to OneDrive or SharePoint (not stored locally). Copilot requires cloud storage. Also verify your Copilot license is active and the file is in a supported format (.xlsx). |
| Copilot says "I can't work with this data" | The data must be in a formatted Excel Table (not just raw cells). Select the data range and press Ctrl+T to convert it to a table first. |
| "Insert Column" or "Apply" buttons don't appear | Wait for Copilot to fully process the request. If the buttons are missing, try rephrasing the prompt to be more explicit about the action (e.g., "Add a column called X" instead of "Calculate X"). |
COPILOT() function returns #NAME? |
The COPILOT() function is in Preview. You need to be on the Beta or Current Channel Preview update channel. Check File → Account → Update Channel. Labs 0–3 and 5 work without it. |
| Variance calculations seem wrong (revenue shows as unfavorable when over plan) | Variance direction depends on the formula. For revenue: positive variance is favorable. For expenses: negative variance (under budget) is favorable. Verify by checking the formula Copilot generated. |
| Aging buckets are incorrect for paid invoices | Rephrase your prompt to explicitly handle paid invoices: "If Amount Paid equals Invoice Amount, show 'Paid' — otherwise, calculate the aging bucket based on Days Outstanding." |
| Copilot generated commentary cites wrong numbers | Verify the cell reference range in your COPILOT() formula covers all the data. If the range is too narrow, Copilot only sees a subset. Expand the range and re-enter the formula. |
| Bank rec matching is imprecise | Copilot matches on heuristics (amount, date proximity, description similarity). For production reconciliations, use dedicated recon tools. Copilot is best for quick ad hoc analysis and first-pass matching. |
Use these for group debriefs in instructor-led sessions, or reflect on them individually:
COPILOT() function generated variance commentary. What review steps would you add before including AI-generated commentary in an official finance package?Every column Copilot adds uses real Excel formulas you can inspect and edit. It's a productivity accelerator, not a black box.
Specific prompts with column names, thresholds, and output formats get dramatically better results than vague requests.
AI as a native Excel formula means commentary, categorization, and risk ratings that stay live and update with your data.
Copilot handles the mechanics. You bring the accounting judgment. Always review AI output before it enters official records.
One prompt saves minutes. A full workflow (GL prep → variance → aging → commentary) saves hours every close cycle.
The best way to learn Copilot is to apply it to your actual data. Start with the task you dread most each month.