Copilot Analyst Agent: Find Margin Leaks in a Sales Spreadsheet

Coding Liquids tutorial cover featuring Sagnik Bhattacharya for Copilot Analyst Agent: Find Margin Leaks in a Sales Spreadsheet.
Coding Liquids tutorial cover featuring Sagnik Bhattacharya for Copilot Analyst Agent: Find Margin Leaks in a Sales Spreadsheet.

To find margin leaks with the Copilot Analyst agent, attach a clean sales workbook that has price, discount, cost and refund columns, ask Analyst to calculate margin per line and rank leaks by dollars lost, then check its method and recompute three figures yourself. Analyst comes with a Microsoft 365 Copilot licence, not free Copilot Chat.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

A margin leak is money that leaves between the list price and the profit: discounts above what you meant to give, costs that rose while prices stayed, refunds booked somewhere else, and delivery you never charged for. A sales sheet usually holds all four, and they hide because each is small on its own line. Analyst is useful here because it works across thousands of rows and returns a plain-language report with tables and charts rather than a formula to build.

What is the Copilot Analyst agent and who can use it?

The Copilot Analyst agent is Microsoft's data-analysis assistant inside Microsoft Copilot, available to people with a Microsoft 365 Copilot licence. Microsoft's support page says it can calculate statistics, identify trends and show outliers from a plain-language question, working across thousands of rows and several files such as Excel spreadsheets, CSV files and databases.

Three points about access, from Microsoft's pages read on 22 November 2026:

  • Where it sits. Microsoft's admin documentation says Researcher and Analyst are first-party tools available in Copilot Chat under Tools, and that they are part of the core chat experience and not covered by agent-related settings.
  • Who can use it. Microsoft's Copilot Business page lists Researcher, Analyst and Facilitator among the pre-built agents that come with a Microsoft 365 Copilot licence, and its support page comparing Copilot Chat with and without a licence lists advanced agents such as Analyst only on the licensed side. If a licensed person still cannot find it, Microsoft says the administrator may not have enabled it.
  • How it works. When Analyst became generally available in June 2025, Microsoft said it runs Python for complex data questions and that you can view the code it is running in real time to check its work. The interface has changed since, so confirm what your version shows.

Our tutorial on what Copilot in Excel can and cannot do covers the other route, working inside an open workbook, and the table below shows how to choose.

JobBetter fitWhy
Multi-step review of a whole quarter of salesAnalystReasons through several calculations and returns a report
Fix one formula or format one columnCopilot in ExcelWorks on the open sheet
Repeated monthly dashboardPower BI or a pivot-table templateRefreshes without asking again
Quick question on a sheet you don't want to uploadExcel formulas or a pivot tableNothing leaves your workbook

How do you get a sales sheet ready for Analyst?

Get a sales sheet ready for Analyst by putting the data in one tidy table with a header row, one row per invoice line, numbers stored as numbers, dates as dates, and consistent product names. Analyst can only reason about columns it can read, so ten minutes of tidying decides the quality of the answers.

Use this checklist before you attach anything.

  1. One table, one header row. No merged cells, no blank rows, no totals row inside the data.
  2. Columns that expose margin. Date, invoice number, customer, salesperson, product, quantity, list price, discount percentage, net price, unit cost, refund amount, delivery cost.
  3. Numbers as numbers. A price typed as "$62.00" text will break sums. Convert text to numbers before attaching.
  4. Same names everywhere. "Alu panel", "Aluminium panel" and "ALU PANEL" are three products to a computer. Fix them with find and replace.
  5. Refunds in their own column. A refund row mixed into sales with a negative number invites double counting.
  6. Remove personal data you do not need. Customer names can be replaced with an account code if the analysis does not need them.
  7. Save a copy. Attach the copy, so you keep the original untouched.

Microsoft's file-format page for Microsoft Copilot lists .xlsx, .xls, .xlsm, .csv and .tsv for data analysis and gives no upload size limit. Its troubleshooting advice is to split a very long file or a spreadsheet with many formulas into smaller pieces, and to export a complex macro-enabled workbook to a plain .xlsx first, both sensible for a quarter of invoice lines.

How do you attach the file and ask the first question?

Open Microsoft Copilot, choose Insights then Analyze data from Home, type your question, select the plus icon, choose Attach content, and pick the workbook from your device or OneDrive. Microsoft's support page describes that route, and adds that Analyst returns an easy-to-read report that can include charts and tables.

  1. Sign in to Microsoft Copilot with your work account.
  2. From Home in the chat box, select Insights > Analyze data. Microsoft says the new Home experience is still rolling out, so if you do not see it, select Analyst under Agents in the left pane, as Microsoft's other support page describes, or look under Tools in Copilot Chat.
  3. Select the + icon, then Attach content, and choose your file.
  4. Type a specific question, such as the first prompt below.
  5. Read the report and open any Python or steps Analyst shows.
  6. Ask follow-ups in the same chat, one at a time.

Start with a prompt that tells Analyst how you define margin. The word means different things to different people, and an unstated definition is the commonest cause of a wrong answer.

I have attached a sales sheet for one quarter. One row is one invoice line.
Definitions: net price = list price x (1 - discount %). Line margin = (net price - unit cost) x quantity - delivery cost - refund amount.
Margin % = line margin / (net price x quantity).
Task: calculate margin and margin % for every line, then show total revenue, total margin and overall margin %.
List the columns you used. If any column is missing or looks wrong, tell me before calculating.

A worked example: $20,735 of leaks in a signage company's quarter

An illustrative signage company records 250 invoice lines over a quarter, totalling $412,000 of revenue. After the first prompt gives the overall margin, the owner asks four targeted questions. Each one finds a different leak, and the dollar figures below are illustrative but the arithmetic is real.

LeakPrompt (shortened)What Analyst found (illustrative)Dollars lost
1. Discounts above norm"Compare average discount by salesperson. List lines with discount over 15% and the extra discount versus the team average of 11%."One salesperson averages 22% on 38 lines worth $61,000 net$8,603
2. Cost rise without a price rise"For each product, compare unit cost in month 1 with month 3. Where cost rose and net price did not, show the lost margin."Aluminium sign panels: cost rose from $41.00 to $46.50 mid-quarter; price stayed $62; 1,000 units sold after the change$5,500
3. Refunds outside margin"Total refunds by product and by installer crew. Show what margin would be after refunds."$4,200 of refunds on 9 invoices, all from one crew's rework$4,200
4. Free delivery"List orders under $300 where delivery cost was not charged, and total the delivery cost."64 orders at an average $38 delivery cost$2,432
Total$20,735

The sums behind each row are simple enough to check by hand. For leak 1, net sales of $61,000 at a 22% discount imply list sales of $78,205 (61,000 divided by 0.78); at the team's 11% discount those lines would have brought $69,603, a gap of $8,603. For leak 2, margin per panel fell from $21.00 to $15.50 on a $62 sale, which is 33.9% down to 25.0%, and 1,000 panels at $5.50 is $5,500. Total leaks of $20,735 are 5.0% of the quarter's revenue.

What matters is the decision each leak leads to, not the total. The owner sets a discount approval line at 15%, adds a surcharge clause to quotes for aluminium, asks why one crew needed rework nine times, and starts charging delivery on orders under $300. Each action is small and specific, and the owner can measure it next quarter by running the same four prompts.

If you want help fitting a margin review into how your signage business runs, I offer one-to-one AI consultations for small businesses.

How do you verify Analyst's numbers before acting?

Recompute at least three headline figures in Excel, read the steps or code Analyst shows, and ask it to list the rows behind each total. Do this before any price change, because a model can read a column wrongly and still sound sure.

  1. Total revenue. Add net price times quantity with a SUMPRODUCT formula or a pivot table. It should match to the cent.
  2. One leak. Filter the sheet to the 38 lines for the high-discount salesperson and check the net total and average discount.
  3. One product. Build a pivot table of unit cost by month for the product and compare with Analyst's claim.
  4. The steps. Open the reasoning or code Analyst displays, and look for filters or definitions that differ from yours, such as excluding rows with blank costs.
  5. The row list. Ask: "List the invoice numbers behind leak 3." Open three of them in the source.
Show your working for the high-discount finding: the filter you applied, the number of rows,
the net revenue, the average discount, and the 10 invoice numbers with the largest discount.
Then state any rows you excluded and why.

Microsoft said at launch that you can watch the Python code Analyst runs, in real time, and check its work. Whether or not your version shows code, the principle holds: you are checking the method and a sample of the output, not trusting a total. If a number cannot be reproduced in Excel in ten minutes, do not act on it.

What are the best prompts for margin analysis?

The best Analyst prompts for margin analysis state the margin definition, the columns, the output shape and what to do with gaps. Here are six that cover most margin questions, each tied to a different kind of business using the example sectors of a mortgage broker, an interior designer and a security installer.

BusinessQuestionPrompt core
Interior designerWhich suppliers' items sell with the lowest margin?"Group by supplier. Show revenue, cost, margin %, and rank from lowest. Flag suppliers under 25%."
Interior designerAre we giving trade discounts we did not mean to?"List lines where discount exceeds the client's agreed discount in the Clients tab."
Security installerWhich installation types lose money after call-backs?"Add call-back cost to each job. Show margin by job type before and after call-backs."
Security installerIs equipment cost creeping up?"For each product code, show unit cost by month and flag rises over 5%."
Mortgage brokerWhich lead sources cost more than they earn?"Match marketing spend by lead source with lender commission and client fees by source. Show net per source."
Signage companyWhich jobs under-recover on installation hours?"Compare quoted hours with timesheet hours by job; value the difference at $55 an hour."

The mortgage-broker row shows the point that margin analysis is not only about goods: any business with a cost and a price per customer has leaks. In each case the sheet has to contain both sides of the equation. If your costs live in a separate system, export them and join them by invoice or product code in Excel before attaching.

What does a before-and-after of a messy sales sheet look like?

A messy sales sheet spells one product three ways, stores prices as text, mixes discount formats and books refunds as separate rows; the cleaned sheet has one name, real numbers and a refund column. A messy sheet gives Analyst ambiguity to resolve, and it resolves it silently. Here are five fields from the signage example, before and after fifteen minutes of cleaning.

FieldBeforeAfter
Product"Alu panel 600", "alu panel 600 ", "ALU PANEL600""Aluminium panel 600" on every row
List price"$62.00" stored as text62 stored as a number, formatted as currency
Discount"15%" in some rows, 0.15 in others, "15" in a few0.15 everywhere, formatted as a percentage
RefundA separate row with quantity -2 and the same invoice numberA Refund column on the original line with the dollar amount
Date"3rd Oct", "03/10", "2026-10-03"Real dates, all in the same format

Run three quick checks in Excel after cleaning: a pivot table of products (the list should have no near-duplicates), a COUNT of numeric cells against the row count in each number column (they should match), and a sort on the date column (nothing should land at the bottom as text). These take about five minutes and prevent most of the wrong reports.

If tidying columns like these is the slow part, try The Complete Excel Course with AI Integration on Coding Liquids.

What does an Analyst report look like, and what would you fix in it?

An Analyst report is a plain-language write-up of the answer to your question, and Microsoft says it can include charts and tables; your job is to check its definitions, denominators and exclusions. On a sheet like this, expect the definitions it used, a summary table and the biggest findings. Here is an illustrative opening of a report on the signage sheet.

Summary (250 lines, 1 Jul to 30 Sep)
Revenue (net): $412,000. Total line margin after delivery and refunds: $118,400. Overall margin: 28.7%.
Largest negative drivers:
1. Discounts above 15%: 38 lines, average 22%.
2. Unit cost increase on Aluminium panel 600 from mid-August.
3. Refunds concentrated in nine invoices.
Note: 6 lines had blank unit cost and were excluded from margin.

What to fix in a report like this. First, the exclusion note: six lines with blank cost were left out, which flatters the margin, so ask for them to be listed and fill in the costs. Second, the margin of $118,400 on revenue of $412,000 is 28.7%, which you can confirm in a calculator, but also check that delivery cost and refunds were subtracted as your definition said. Third, ask Analyst for the dollar value of each driver, not just the ranking, because "largest" without dollars cannot guide a priority. Fourth, ask for a chart only after the numbers are confirmed, since a good-looking chart of a wrong number is harder to doubt.

A second worked example: trade discounts in an interior design practice

An illustrative interior designer buys furniture at trade prices and sells to clients with agreed discounts, recorded on a Clients tab (client, agreed discount). The sales tab records list price, discount given and cost. The question: where did we give more discount than agreed?

Use the Sales tab and the Clients tab. Match on client code.
For each line, compare discount given with the agreed discount.
Show lines where discount given is higher, with the extra discount in dollars.
Total the extra discount by salesperson. List clients with no agreed discount in the Clients tab.

In an illustrative run, 29 lines exceed the agreed discount, together $3,180 more than the clients were owed, and 11 of them belong to one salesperson. Four clients have no agreed discount recorded, so any discount to them is unchecked. The designer does not blame the salesperson; she asks why the Clients tab is out of date, updates it, and sets a rule that any discount without an agreed rate needs her sign-off. Next quarter's run shows the leak falls to $410, a drop of $2,770 that she attributes to the sign-off rule and the up-to-date tab.

How do you ask what-if questions after finding the leaks?

After you confirm the leaks, ask what-if questions with explicit assumptions, and compute the key figure yourself. Analyst can model a scenario quickly, but you decide which assumptions are realistic, because a customer may not accept the new price.

Assume we raise the price of Aluminium panel 600 from $62 to $66 and volume falls by 8%.
Using last quarter's 1,800 units and unit cost of $46.50 after the cost rise, show revenue, margin in dollars and margin % before and after.
State each assumption. Do not use any data not in the sheet.

The sum checks by hand. Before: 1,800 units at ($62 minus $46.50) is $27,900 of margin. After: 8% fewer units is 1,656; margin per unit is $66 minus $46.50, or $19.50; total is $32,292. The price rise adds $4,392 of margin even with fewer units, but only if the 8% volume loss holds. Ask a second version at 20% loss: 1,440 units at $19.50 is $28,080, barely above the original. That tells you the break-even volume loss is just over 20%, which is a useful number to hold in mind when a customer pushes back. For a deeper look at how reliable AI price suggestions are, see our tutorial on whether AI can price a job accurately.

What can go wrong with Analyst on a sales sheet?

The usual Analyst failures on a sales sheet are text-formatted numbers, an unstated margin definition, double-counted refunds, mixed units and a model that fills a gap instead of reporting it. All five are preventable with the preparation checklist and a verification step.

Text numbers. In an illustrative run, Analyst reports total revenue of $0 because the price column was text. The fix is to convert the column to numbers and attach again. Ask Analyst at the start to report columns it could not read as numbers.

The unstated definition. One person means margin as revenue minus cost, another as markup on cost. A report that says "margin 38%" could mean either. State the formula in the prompt, and ask Analyst to repeat it back.

Double-counted refunds. If refunds appear both as negative sales lines and in a refund column, leak 3 is counted twice. Keep one source for each fact, and ask Analyst "Is any refund counted in more than one column?"

Mixed units. A product sold per square metre on some lines and per sheet on others makes unit costs meaningless. Add a unit column, and ask Analyst to flag products with more than one unit.

The helpful guess. Where unit cost is blank, Analyst might treat it as zero and report a 100% margin. Add "treat blank costs as unknown and list them" to the prompt.

Our tutorial on checking margins product by product with AI covers the underlying logic if you want to build the same checks with formulas instead.

What limits and privacy points should you know?

Analyst runs inside the Microsoft 365 commercial data processing boundary, according to Microsoft's admin documentation, and its use may be capped. When it became generally available in June 2025, Microsoft allowed each licensed user up to 25 combined Researcher and Analyst queries a month; ask your admin what limit applies now rather than assuming unlimited use.

  • Data handling. Microsoft's admin page says Researcher and Analyst operate entirely within the Microsoft 365 commercial data processing boundary and inherit existing security, privacy and compliance commitments.
  • Personal data. Strip names and contact details if the analysis does not need them.
  • Query cap. The launch limit was shared with Researcher, so a month of research questions can use up your margin review. Do not waste queries on vague prompts; plan the four or five questions first.
  • Availability. If Analyst is missing, ask the admin whether it has been enabled for you.
  • Not a forecast. Analyst describes what is in the sheet. It does not know about next quarter's supplier quote unless you give it.

How do you turn the findings into a monthly habit?

Turn margin findings into a monthly habit by keeping the four prompts in a note, rerunning them on each month's sheet, pasting the dollar results into a one-line tracker, and reviewing the trend. A repeatable checklist is worth more than one brilliant analysis, because leaks reappear when attention moves on.

MonthHigh-discount leakCost-rise leakRefundsFree deliveryTotal
Quarter just reviewed$8,603$5,500$4,200$2,432$20,735
Next quarter (target after actions)Below $3,000Below $1,000Below $1,500Below $500Below $6,000

The targets in that last row are illustrative, set by the owner after deciding on the actions above. If next quarter's total is higher than the target, the prompts show which action failed. Add a column for what you changed, so the tracker doubles as a log. And if the figures start to matter for pricing decisions across many products, consider whether a dashboard in Power BI would serve better; our tutorial on Power BI versus Excel with Copilot weighs the two for a small business.

Questions about the Copilot Analyst agent

Do I need a Microsoft 365 Copilot licence to use the Analyst agent?

Yes. Microsoft's Copilot Business page lists Analyst among the pre-built agents that come with a Microsoft 365 Copilot licence, and its support page on Copilot Chat with and without a licence puts advanced agents such as Analyst on the licensed side only. If a licensed person still cannot find it, Microsoft says the administrator may not have enabled it.

Where do I find the Analyst agent?

Microsoft's newest support page says to open Microsoft Copilot, go to Home in the chat box and choose Insights, then Analyze data, though Home is still rolling out. Another Microsoft support page says to select Analyst under Agents in the left navigation pane, and admin documentation places it under Tools in Copilot Chat. Try those three places in turn.

Can I see how Analyst reached its numbers?

Yes. When Analyst became generally available in June 2025, Microsoft said it runs Python for complex data questions and that you can view the code it is running in real time to check its work. Even so, recompute at least three headline figures in Excel with a pivot table or SUMIFS before you act on them.

What file types can I attach?

Excel and CSV files are the usual choice. Microsoft's Analyst page names Excel spreadsheets, CSV files and databases, and its file-format page for Microsoft Copilot lists .xlsx, .xls, .xlsm, .csv and .tsv for data analysis. Neither page gives a size limit, so start with one workbook and one tidy table of a few thousand rows, and split very large files.

Is Analyst the same as Copilot in Excel?

No. Analyst is a separate data-analysis experience in Microsoft Copilot that reasons through your data and returns a report, while Copilot in Excel works inside an open workbook. For a one-off question on a sheet you are already editing, Copilot in Excel may be quicker; for a multi-step review, Analyst fits.

Further reads

Show 2 more related tutorials

Sources: Microsoft Support pages Get started with Analyst in Microsoft Copilot, Use Analyst for deep data analysis, How Copilot Chat works with and without a Microsoft Copilot license and File formats supported by Microsoft Copilot; Microsoft Learn page Manage agents in the Microsoft 365 admin center; Microsoft's Copilot Business page; Microsoft's June 2025 announcement that Researcher and Analyst are generally available (all read 22 November 2026).

Want a second pair of eyes on your setup?

Bring the process you're trying to fix. On a 1:1 call we'll work out which AI tools should handle it, which parts stay manual, and how to roll it out without disrupting the team.

Book a 1:1 call with me