Yes. In its default Allow editing mode, Copilot in Excel can build a one-page sales dashboard from a table: KPI cells, charts and PivotTables on a new sheet. The mistakes to check for are about meaning, not formatting: double-counted totals, averages per line instead of per invoice, text months sorted alphabetically, and PivotTables that don't refresh when new rows arrive.
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 nowMicrosoft is candid about this. Its Copilot in Excel FAQ says results can be inaccurate and advises against using Copilot for decisions in sensitive areas such as finance. A sales dashboard feeds exactly those decisions, so treat Copilot's version as a fast first draft and reconcile every headline number before anyone acts on it. The good news is that the checks are mechanical, take about 20 minutes, and once the fixes are in place the dashboard keeps working without Copilot touching it again.
How should you prepare sales data so Copilot does not misread it?
Prepare sales data by formatting it as an Excel table named Sales and deleting any typed total rows, because most dashboard errors are planted before the prompt. Copilot works with what is on the sheet, and a sales export rarely arrives tidy. Ten minutes here prevents most of the problems later:
| Prepare | Why it matters |
|---|---|
| Format the data as an Excel table and name it Sales | Formulas using structured references adjust automatically as the table grows, and typing in the row below the last row extends the table |
| Delete any typed total or subtotal rows inside the data | A "Total" row inside the range gets summed with everything else |
| Keep one row per invoice line, with an Invoice column | Lets you count invoices separately from lines |
| Make sure Date holds real dates, not text | Text dates cannot be grouped by month and sort alphabetically |
| Use one currency and say whether amounts are net or gross | A mixed column produces a total that means nothing |
| Set Calculation Options to Automatic | Microsoft says Copilot editing only works with automatic calculation |
| Save as a normal .xlsx workbook | Microsoft lists Strict Open XML files and checked-out SharePoint files as causes of an "Unsupported file state" message |
If the export is messy (duplicate customers, stray spaces, numbers stored as text), clean it before building anything; cleaning customer data in Excel with Copilot covers the duplicate and format fixes step by step.
A veterinary practice's prompt and its flawed first draft
Consider a three-vet practice with a quarter's sales exported from its practice software: 3,600 invoice lines from 1,450 invoices between July and September, with columns Date, Invoice, Vet, Category (Consultations, Vaccinations, Procedures, Products), Item, Qty and Net. The true net total for the quarter is $214,380 (all figures illustrative). The practice manager opens Copilot from the icon in the lower-right corner of Excel, leaves it in Allow editing mode and types:
Using the Sales table, build a one-page sales dashboard on a new sheet
called Dashboard. Top row: KPI cells for total net sales, number of
invoices, average invoice value and best-selling category. Below: a line
chart of net sales by month, a bar chart of net sales by category, and a
PivotTable of net sales by vet by month. Add slicers for Vet and Category.
Use formulas that reference the Sales table, not typed values.
Copilot plans the steps, builds the sheet and shows its reasoning in the pane as it works. The result looks finished: four KPI tiles, two charts, a PivotTable and two slicers. Checking the numbers against what the manager already knew turned up four problems in this illustrative first draft:
- Total net sales showed $428,760, exactly double. The export had a typed "Quarter total" row at the bottom, inside the table, so it was summed along with every line.
- Average invoice value showed $59.55. That is $214,380 divided by 3,600 lines. The practice's average invoice is $214,380 divided by 1,450 invoices, which is $147.85. The formula averaged the Net column, which answers a different question.
- The monthly chart ran August, July, September. The export's Month column was text, and text sorts alphabetically.
- The following Monday, the PivotTable still showed September as the last month after the manager pasted in the first week of October. The KPI formulas had updated; the PivotTable had not.
None of these is exotic. Each comes from a reasonable reading of an ambiguous instruction or an untidy sheet, and each would have gone into a practice meeting looking authoritative. The fixes took about 15 minutes, and the checking routine later in this tutorial would have caught all four.
What errors does Copilot make in a sales dashboard, and how do you fix them?
Copilot's sales dashboards most often go wrong in six ways: total rows counted as data, averages per line instead of per invoice, text months, typed numbers in KPI cells, chart or PivotTable sources fixed to old rows, and PivotTables that were not refreshed.
| Error | How it shows up | Quick check | Fix |
|---|---|---|---|
| Total or subtotal rows counted as data | Totals exactly double, or inflated by one month's subtotal | Filter the table for blank Invoice or for "Total" | Delete the rows; never keep totals inside the table |
| Average per line instead of per invoice or customer | Average looks implausibly low | Divide the total by the invoice count yourself | Use total divided by a count of unique invoices |
| Text months or text dates | Months out of order; grouping fails | Check whether dates align right and change with date formats | Add a Month column with a real date (first of the month) |
| KPI cells holding typed numbers | KPIs never change when data changes | Click each KPI: the formula bar should show a formula | Replace with formulas on the Sales table |
| Chart or PivotTable source fixed to old rows | New rows missing from one visual only | Chart: right-click, Select Data. PivotTable: Change Data Source | Point the source at the Sales table, then test with a new row |
| PivotTable not refreshed | PivotTable disagrees with KPI cells | Compare its grand total with the total KPI | Refresh All, or tick refresh on open |
The same averaging error turns up outside sales. A care agency asking Copilot for "average visit length per client" can get the average across all visits, which overweights clients with many short visits. A music school asking for "average fee per student" can get the average per invoice line, which counts a student with three instruments three times. Whenever a KPI contains the word "per", write down what one unit is (invoice, client, student) and check the denominator counts that unit.
How do you fix a Copilot dashboard without starting again?
Fix a Copilot dashboard with narrow follow-up prompts, one problem per message, so you can see exactly what each one changed. These are the prompts the practice manager used, in order:
1. Delete the row in the Sales table where Invoice is blank and Item says
"Quarter total". Then tell me the new total of Sales[Net].
2. Change the Average invoice value KPI to total Net divided by the number
of unique values in Sales[Invoice]. Show me the formula you used.
3. Add a column to the Sales table called Month holding the first day of
each row's month as a date, formatted mmm yyyy. Rebuild the monthly
chart and the PivotTable on that column.
4. List every chart and PivotTable on the Dashboard sheet with its source
range. Flag any that do not use the Sales table.
Asking Copilot to report a number or a formula back after each change turns every fix into a check. After prompt 1 it should state $214,380; after prompt 2 the formula should contain UNIQUE. Prompt 4 is the most useful of the four, because it audits every visual at once, but verify its answer by clicking two of the charts yourself: a list is only as good as the reading behind it. If a fix goes wrong, undo it straight away rather than piling another prompt on top.
The same build in a childcare nursery: refunds and denominators
Different businesses hide different traps in the same dashboard. A childcare nursery building a fee-income view from its billing export (say 2,300 lines a term for 85 children, illustrative) meets two the vet practice did not.
First, credit notes. Refunds for closure days or sibling discounts appear as negative lines. A total of the Net column correctly includes them, but a "best category" KPI or a chart of top fee types can rank categories on gross invoiced amounts if the formula filters out negatives, and a count of invoices will include each credit note as if it were a sale. Decide whether the dashboard shows invoiced, credited and net figures separately, and give credit notes their own Type value so formulas can include or exclude them on purpose.
Second, the denominator again. "Average monthly fee per child" needs the count of unique children billed in the month, not the count of invoice lines (a child with extra sessions and a lunch charge has three lines). The check is the same as for the vet: divide the total by the number of children on the register for that month and compare with the KPI. If the two disagree, the formula is counting lines.
The nursery also wants fees collected, not just invoiced, which needs a Paid date or Status column in the export. Copilot cannot infer payment from an invoice list; if the column is missing, ask your billing system for a payments export and add it as a second table, rather than letting a KPI labelled "collected" quietly report invoiced amounts.
Why ask Copilot for KPI formulas instead of values?
Ask Copilot for formulas rather than values, because a formula can be read and checked, which makes it the single most useful line in the prompt. These are the KPI formulas the practice ended up with after the fixes, all referencing the Sales table so they extend automatically as rows are added:
Total net sales: =SUM(Sales[Net])
Number of invoices: =COUNTA(UNIQUE(Sales[Invoice]))
Average invoice value: =SUM(Sales[Net]) / COUNTA(UNIQUE(Sales[Invoice]))
Best category: =INDEX(SORTBY(UNIQUE(Sales[Category]),
SUMIFS(Sales[Net], Sales[Category], UNIQUE(Sales[Category])), -1), 1)
Month column (in the table, formatted mmm yyyy):
=EOMONTH([@Date], -1) + 1
Before and after, for the average: Copilot's first version was =AVERAGE(Sales[Net]), returning $59.55; the corrected formula returns $147.85. Both are valid Excel. Only one answers "how much does a typical client spend per visit", which is what the practice wanted.
One more trap if your table has a total row switched on. Microsoft's table overview notes that the total row uses SUBTOTAL, which ignores rows hidden by a filter by default. So while a slicer or filter is active, the table's total row shows the filtered total, while a KPI cell using SUM shows everything. Neither is wrong, but a dashboard that shows both side by side will confuse whoever reads it. Decide which you want and label it ("All quarter" or "Current filter").
If you want to go further with formulas than a dashboard needs, having Copilot write and check sales analysis formulas covers margin, growth and per-customer measures with the same audit-first approach.
If you want to write and audit KPI formulas like these yourself, try The Complete Excel Course with AI Integration on Coding Liquids.
What updates by itself in an Excel dashboard, and what must you refresh?
Different parts of an Excel dashboard update in different ways: the Sales table and KPI formulas extend as rows are added, while PivotTables and charts with fixed sources may not. The fourth error in the vet example is the one that bites weeks later, because the dashboard looks right on the day it is built.
| Part | Updates when you add rows? | What to do |
|---|---|---|
| The Sales table | Yes, if you type or paste in the row directly below it | Paste new weeks at the bottom, not lower down the sheet |
| KPI formulas using Sales[...] references | Yes, with calculation set to Automatic | Nothing |
| PivotTables | Only where your Excel has PivotTable Auto Refresh (Insider builds, per Microsoft) | Data, Refresh All after each paste, or tick Refresh data when opening the file in PivotTable Options, Data tab |
| Charts built on a PivotTable | They follow the PivotTable | Refresh the PivotTable |
| Charts built on a fixed range | Only inside that range | Check Select Data; point the series at the table columns |
| Cells using the COPILOT function | No: they return #NAME? when recalculated | Replace them (see below) |
Microsoft's Refresh PivotTable data page adds a nuance: newer versions of Excel can refresh PivotTables automatically when they are built on data in the same workbook, but it says this Auto Refresh is currently available to participants in the Microsoft 365 Insider program. Unless you are on such a build, assume a manual refresh is needed, and tick the refresh-on-open option so a forgotten click is fixed the next time anyone opens the file.
The COPILOT worksheet function deserves a warning for anyone inheriting an older dashboard. Microsoft's support page says the function stopped being available on 14 September 2026. Results already calculated stay as cached values, but when such a cell recalculates it shows #NAME?. If your dashboard had a "commentary" cell written that way, it will break the first time the sheet recalculates. What to use now the COPILOT function is retired covers the replacements.
How do you check a Copilot dashboard before anyone sees the numbers?
Check a Copilot dashboard in about 20 minutes by comparing its KPIs with your source system's own report and filtering one category by hand. Run these checks after Copilot builds the dashboard, and again whenever it rebuilds part of it. The practice's results are filled in as an illustration:
| Check | Compare with | Practice result |
|---|---|---|
| Total net sales KPI | The quarter's total in the practice software's own sales report | $214,380 both, after removing the total row |
| Invoice count KPI | Number of invoices in the software for the same dates | 1,450 both |
| One category by hand | Filter the table to Vaccinations and read the status-bar sum | $31,920 in table and chart |
| Month boundaries | Sales on 1 July and 30 September appear in the right months | Correct after adding the date-based Month column |
| Planted test row | Add a $1,000 line dated today, refresh, watch every visual change, then delete it | KPIs and PivotTable moved; the bar chart did not until its source was fixed |
| PivotTable grand total | Total net sales KPI | Matched after Refresh All |
The planted test row is the check most people skip and the one that catches the most: any visual that does not move when a $1,000 sale appears is reading the wrong range. Delete the test row afterwards and refresh again. A longer list of checks for AI-built dashboards in general is in six checks before you trust the numbers.
What are Copilot workbook rules in Excel?
Workbook rules are a worksheet titled .Rules, with one rule per cell in column A, that Copilot follows whenever it works in that workbook. You create it from the Add menu in the Copilot prompt field (Create workbook rules), or by adding the sheet yourself. Microsoft notes the sheet must stay visible to be used, and that rules may behave differently across models and over time. For a sales dashboard, the practice's rules sheet looked like this:
## DATA
The source data is the Sales table. Never type numbers into KPI cells.
Each row is one invoice line. Count invoices with COUNTA(UNIQUE(Sales[Invoice])).
Average invoice value = total Net / number of unique invoices.
Net is in USD, before tax.
## DATES
Group by the Month column (first day of each month), never by month names as text.
## VISUALS
Charts and PivotTables must use the Sales table as their source, not a fixed range.
Turn on "Refresh data when opening the file" for every PivotTable.
## CHECKS
After any change, state the total of Sales[Net] in the chat so I can compare it.
The last rule is a small habit that pays off: Copilot reports a total you can compare with the practice software every time it touches the workbook. Because the rules travel with the file, a colleague who asks Copilot to add a chart next month gets the same conventions without knowing they exist.
If you want help fitting Copilot into how your business reports its sales, I offer one-to-one AI consultations for small businesses.
Which Copilot mode should you use for a first dashboard build?
Start a first dashboard build in Plan mode, then switch to Allow editing to carry it out. Copilot in Excel has three modes. Allow editing, the default, changes the workbook directly. Plan produces a step-by-step plan for you to review and confirm before anything changes. Chat only analyses and answers without editing. Use the Stop button beside the prompt field if it heads somewhere you did not intend.
Two cautions from Microsoft's FAQ apply to shared workbooks. When you save Copilot's changes, anyone with access to the file sees them, including people co-authoring at that moment, so build the dashboard in a copy if the sales workbook is in daily use. And if a change goes wrong, undo it or restore a previous version of the file rather than asking Copilot to reverse its own edit.
Mode support varies by device. Microsoft lists all three modes on Windows, Mac and the web; on iPad, chat and plan modes, with edit mode rolling out; on iPhone and Android, chat mode. Build on a computer and use the phone to read the finished dashboard.
Which licence gives you Copilot in Excel?
Copilot in Excel comes with a Copilot Chat-eligible Microsoft 365 business subscription at standard access, and the paid Microsoft 365 Copilot add-on adds priority access. Microsoft's FAQ lists four routes in all: a Microsoft 365 Personal or Family subscription with an AI credits plan, a Microsoft 365 Premium subscription, a commercial Microsoft Copilot subscription, or a Copilot Chat-eligible Microsoft 365 business or enterprise subscription. In the app, users without the paid add-on see M365 Copilot (Basic), meaning standard access; those with it see M365 Copilot (Premium), with priority access, and the model switcher between Claude and GPT models needs a commercial Copilot subscription or Microsoft 365 Premium.
On Microsoft's business pricing page in November 2026, the Microsoft 365 Copilot Business add-on is $21 per user per month paid yearly, shown at $18 under a first-year discount for purchases between 1 July and 31 December 2026, with usage-based billing for select advanced AI features on top. Bundles are $23.50 per user per month (Business Standard with Copilot) and $32 (Business Premium with Copilot), both paid yearly. For a three-person practice where only the manager builds dashboards, one add-on seat at $21 is $252 a year; try the build on standard access first and buy only if you hit its limits. The licence options are compared in full in which Copilot in Excel licence you need and what it costs, and if you are weighing Excel against a dedicated tool, Power BI vs Excel with Copilot for a small business dashboard sets out when each makes sense. Microsoft's own walkthrough is on its Get started with Copilot in Excel page.
Copilot dashboard questions from small business owners
Can Copilot build a sales dashboard in Excel?
Yes. In its default Allow editing mode, Copilot can build KPI cells, charts and PivotTables on a new sheet from a table. Treat the result as a first draft: check for double-counted totals, averages per line instead of per invoice, text months sorted alphabetically and PivotTables that do not refresh, and reconcile headline numbers before anyone acts on them.
Do I need the paid Copilot add-on to build a dashboard in Excel?
Not necessarily. Microsoft's FAQ lists a Copilot Chat-eligible Microsoft 365 business subscription as qualifying for Copilot in Excel with standard access, labelled M365 Copilot (Basic). The Microsoft 365 Copilot Business add-on gives priority access and lets you switch between AI models. Try the build on the access you already have before paying for seats.
Can Copilot update the dashboard automatically every week?
No, and you do not need it to. The dashboard stays current through ordinary Excel behaviour: formulas on the table recalculate, and PivotTables refresh when you click Refresh All or open the file with the refresh-on-open option ticked. Paste each week's rows into the bottom of the table and those two things do the rest.
Why does my dashboard show #NAME? in cells that used to have AI text?
Because those cells probably used the COPILOT worksheet function. Microsoft says it stopped being available on 14 September 2026; earlier results stay as cached values until the cell recalculates, then it shows #NAME?. Replace those cells with ordinary formulas, or ask Copilot in the side pane to write the commentary as plain text once.
Is it safe to put customer names in the sales table Copilot reads?
It is safer without them. With a work or school account, Microsoft points to its Data, Privacy and Security for Microsoft Copilot documentation, and Copilot works on the workbook you have open. For a sales dashboard you rarely need names at all: use customer or account IDs, which also makes totals by customer more reliable when two customers share a name.
Further reads
- How to Create a Sales Dashboard in Excel Using ChatGPT — The same one-page sales dashboard built with ChatGPT instead.
- Copilot in Excel: What It Can and Can't Do With Your Numbers — A wider look at what Copilot can and cannot do with your numbers.
- How to Build a Retail KPI Dashboard in Excel From Till Data — A weekly KPI sheet built from till data, for shops.
- How Much Does Power BI Copilot Cost a Small Business? — Power BI Copilot needs paid Fabric capacity on top of Pro licences. The monthly sums, the on-and-off options and a checklist before you buy.
- Analyse Survey Responses in Excel With Copilot: Themes, Counts, Charts — Copilot in Excel themes 312 open-text survey answers, with a codebook, a 40-row accuracy check, PivotTable counts, charts and a Julius comparison.
Browse all AI-for-business tutorials
Sources: Microsoft Support pages Get started with Copilot in Excel, Frequently asked questions about Copilot in Excel, the page on creating charts and PivotTables with Copilot in Excel, Copilot in Excel rules, Refresh PivotTable data, Resize a table, Using structured references with Excel tables, Overview of Excel tables and the COPILOT function notice; Microsoft Copilot business pricing page. All read 17 November 2026.
