Copilot for Excel: Microsoft Copilot for Business Analysis

Chat mode answers questions about your workbook without changing a single cell, which makes it the right starting point for understanding an unfamiliar file or questioning a number. In this lesson you'll learn when to choose Chat over the other modes, how to set up the Copilot pane, and how threaded conversations let follow-up questions build on earlier ones. You'll then work through a workbook inherited from a departing colleague, using Chat alone to find out what it contains, how it's calculated and where the risks are, without touching the file.

Plan mode sits between Chat and Edit. Copilot proposes an ordered sequence of actions and waits for your approval before changing anything. In this lesson you'll learn when a task is worth planning first and what to check in a proposed plan: the inputs it will use, what it leaves out, how it joins sheets together and where the output will land. You'll then take a three-sheet sales workbook, review Copilot's plan, spot the assumptions that would have produced the wrong answer and amend the plan before you approve it.

Edit mode lets Copilot change the workbook directly, trading the upfront review step for speed. This lesson explains when that trade is worth making and how to stay in control while a task runs. You'll see how to follow each step as it happens and stop a task partway through, and why stopping is not the same as undoing. Working in a practice workbook, you'll commit changes directly, check what Copilot decided by inspecting the result, and back out changes you don't want to keep.

Copilot fills gaps in a request by guessing, and an unrequested guess is the most common cause of a wrong result. This lesson shows how to close those gaps: name the sheet, table and rows in scope, refer to columns by their exact headings, and state the shape of the answer and exactly where it should go so it never lands on live data. You'll put this into practice by rebuilding a quarterly bookings summary, starting from a vague prompt and ending with a specific one that gets the result right first time.

Copilot's first answer is a draft, not a finished piece of work. This lesson shows you how to refine it. You'll learn to start broad so you can see what Copilot understood about your data, then narrow the result with focused follow-ups instead of starting over. Using an engagement billing dataset, you'll build a client realisation report in three rounds: first totals by client, then a calculated realisation percentage, then the finishing touches that make it ready to share.

Ask Copilot for a calculation and it writes a formula, not a pasted number, so the result stays live and can be audited. In this lesson you'll learn how to phrase single-cell and whole-column requests by naming the inputs, the operation and where the output goes. You'll then build a renewals calculation on a book of accounts where ARR has moved in both directions, and check Copilot's formula against the data to make sure it handles every case correctly.

Copilot also helps before you've written a prompt. With Formula Completion, typing an equals sign prompts Copilot to read the column headings and nearby data and suggest a formula. It works best inside a formatted table. With Formula by Example, you fill in two or three answers by hand and Excel spots the pattern and completes the rest. In this lesson you'll learn when each one fits, then use both to complete two columns of an engagement billing sheet without writing a formula yourself.

Structural editing reshapes a workbook rather than calculating within it. It covers adding, renaming, reordering, splitting and tidying sheets and ranges. In this lesson you'll see how one clear request can replace a long string of manual steps, and why sheet-level operations are the quickest wins. You'll then take a consolidated quarterly sheet and have Copilot split it into one worksheet per region, with headers and the matching rows copied across, before checking that nothing was lost or duplicated along the way.

Sorting, filtering and highlighting all answer the question "where should I look first?", but each one changes the file in a different way. In this lesson you'll learn which to use when and why sorting needs the most care. You'll also learn how to describe the rule you want instead of working through dialog boxes, whether that's multi-level sorts, custom orders such as Enterprise, Mid-Market and SMB, or conditional highlighting. You'll then triage a renewals book by prompt, ready to walk a VP through it.

Prompting is never free. It takes time to describe the task, to wait while it runs and to check the result. This lesson introduces the two-click test: if you already know the ribbon command and it takes two or three clicks, just do it yourself. It also explains why you should ask Copilot for a formula rather than a figure, because a generated number is a snapshot that won't update when the data changes. You'll then sort five real tasks on an order dataset into those worth prompting and those that aren't.

Modern Excel formulas can return a whole result from one cell and spill it into the cells around it. This lesson covers the idea that everything else in the section rests on. Only the top-left cell holds the formula, the result resizes itself as the data changes, and the hash reference (such as A4#) always means the whole current result. You'll also see why a spill needs empty space and what #SPILL! really means. You'll then build a live client coverage list, then block it on purpose and watch it refuse.

FILTER returns every row that meets a condition as one live result that refreshes when the source changes. In this lesson you'll learn the three parts of a FILTER formula and how Copilot turns a plain-English business condition into one. You'll see how multiplying conditions gives AND logic, how adding them gives OR, and how to handle an empty result gracefully. You'll then have Copilot build a live at-risk renewal watchlist driven by threshold cells you can change, such as minimum ARR and health score.

Although FILTER tells you which rows. It still returns every column in source order. This lesson shows how Copilot layers CHOOSECOLS, SORT and TAKE on top to control which columns appear, in what order, and how many results come back. You'll learn why the order matters (filter the rows, choose the columns, sort, then take the top N) and why each layer should be its own prompt so you can check it before moving on. You'll turn a fourteen-column extract into a five-row board exhibit.

Operational data often arrives split across sheets, such as twelve monthly exports with identical columns. This lesson shows how Copilot uses VSTACK to stack them into one spilled list, and HSTACK to join arrays side by side. HSTACK is especially useful for adding a derived column such as a period label or source name. You'll then build a live quarterly order list from separate monthly sheets that updates automatically whenever the source sheets change, with no copy and paste.

When an array formula comes back wrong, the error usually tells you exactly why. This lesson explains what #SPILL! and #CALC! actually mean. #SPILL! means the logic is fine but something is in the way. #CALC! often means the formula ran and legitimately found nothing. You'll learn to ask Copilot for a diagnosis rather than a rewrite, and to tell it not to change anything yet so you understand the problem first. You'll then diagnose four broken array formulas and compare Copilot's explanation with your own.

Real business rules are a sequence of steps, not a single calculation. Ask for the whole rule in one formula and, if it's wrong, you have nowhere to look. This lesson shows you how to prompt Copilot to solve a rule with helper columns, one per stage, and to explain the business rule behind each one. You'll then stage a sales commission scheme step by step and prove it against a sample of deals Finance has already calculated by hand.

Long formulas aren't the real problem. Repetition inside them is. When the same sub-expression appears again and again, the formula becomes hard to read, risky to change and slow to calculate. This lesson shows how the LET function names each step inside a single formula, and how Copilot can restructure an existing formula for you. You'll take a hard-to-read partner rebate formula and have Copilot rewrite it with LET so each stage is a named variable, then check that the results haven't changed.

A LET formula solves one problem in one place. A LAMBDA turns the same logic into your own Excel function that you can use anywhere in the workbook. This lesson explains what LAMBDA adds, which is named arguments, and why converting a formula into one is mechanical work Copilot does well. Naming the function is still your job, in Name Manager. You'll publish a tiered commission rule as a named function, use it on a new dataset and then track down and fix the flaws in its definition.

Business rules are usually described row by row, but you don't need a column of copied-down formulas to apply them. This lesson shows how Copilot uses BYROW to apply one rule to every row and return a single spilled result, and how BYCOL does the same for columns, which suits driver dashboards. It also covers MAP and the readability trade-off these functions bring. You'll then score an entire renewal portfolio with a single formula that produces a risk score for each contract.

GROUPBY turns a raw transaction list into a finished summary table from a single cell. The grouping, totals, sorting and exclusions all live in one formula that updates as the data changes. In this lesson you'll learn the three things you need to state (what to group by, what to aggregate and how) and how the later arguments control headers, sort order and totals. You'll then have Copilot build a board-ready regional summary, starting simple and refining it until it's ready to present.

GROUPBY answers "by what?" one dimension at a time. PIVOTBY answers "by what, and across what?", giving you a full cross-tab from a single formula that recalculates as the data changes. In this lesson you'll learn how Copilot builds PIVOTBY with rows down and periods across, and how totals and subtotals are arguments you control rather than afterthoughts. You'll then build a self-rebuilding quarterly grid and extend it so it reports only on the segment chosen in an input cell.

Dynamic array formulas haven't made PivotTables obsolete. This lesson gives an honest comparison of the two. PivotTables are still better for drill-down, slicers, timelines and letting readers explore the data themselves. Formulas win when the output feeds another calculation, a threshold check or a report that has to rebuild itself. You'll build the same report both ways, test the real differences between them, and finish with a choice you can defend to colleagues.

A total answers "how much?", but a management report also has to answer "how much of the whole?", "where does it rank?" and "who are the top performers?" In this lesson you'll have Copilot add percentage of total, ranking and a dynamic top-N list to a spilled summary, all as formulas. You'll then build a one-page quarterly customer report where changing a single input cell rebuilds the whole page for the chosen quarter.

No Python experience is needed for this lesson. You'll learn that a =PY() cell is still a cell. It runs in the Microsoft cloud, is committed with Ctrl+Enter and returns its result to the grid. You'll also see why Python can't see your workbook until you pass data in with the xl() function, and how to reference a range, a table column or a whole table with headers. You'll build four Python cells, including one with a deliberate mistake, and then ask Copilot to explain the error in plain English.

You don't need to learn Python syntax to use Python in Excel. You need to describe the outcome precisely. This lesson shows you how to write a prompt that names four things: the data, the calculation, the grouping and the form of the result. It also covers how to sign off on code you didn't write by asking Copilot to annotate it line by line and checking which rows were included or dropped. You'll work through a full cycle: prompt, annotate, break and diagnose.

Python's real value in Excel comes from its libraries: tested toolkits that solve whole classes of problem for you. This lesson shows how to find the right one by asking Copilot, starting from the business question rather than the technique. "Is this difference real or just noise?" gets a better answer than naming a statistical test. You'll audit four business questions from a customer contract book against the Python toolkit in Excel and find out which ones are now within reach.

Some reshaping jobs defeat even dynamic array formulas. pandas, the data-wrangling library bundled with Python in Excel, handles them easily. This lesson explains what pandas adds, including unpivoting, joining and cleaning whole tables, and how a DataFrame differs from an Excel range. You'll then take a typical FP&A file laid out with twelve month columns and have Copilot convert it into one row per period, producing a decision-ready table, then check the result before relying on it.

Excel's chart gallery is built for comparing summarised values, and a bar chart of averages can hide the real story. Two categories with the same average can carry very different risk. In this lesson you'll use Copilot to build distribution charts with Matplotlib and Seaborn that show the spread behind the average. You'll see how a Python plot lives in a cell and redraws when the data changes, then restyle the charts to a clean corporate standard ready for a report.

Python in Excel brings machine learning within reach, with Copilot writing the scikit-learn code. This lesson covers two questions a model can answer. Regression answers "how much?" by learning the relationship between drivers and an outcome so you can forecast. Clustering answers "who belongs together?" by finding natural customer segments. You'll also learn to read the output honestly, including why a high R² on your own history is easy to achieve and easy to over-trust.