To build a dashboard with Copilot in Excel, open your sales file from OneDrive and ask Copilot in plain words. The paid Copilot seat gives the full Copilot in Excel, and some Copilot Chat (free) accounts get standard access. No sales file? Use our free practice file.
These steps follow Microsoft's help pages as of November 2026. Copilot in Excel changes often, so the guide links to each help page it uses.
Before you start
You need 4 things:
- Copilot in Excel on your account. At work, your IT admin assigns the licence. The next section shows how to see which licence you have.
- Excel, kept up to date. Excel on Windows, on a Mac or on the web all work.
- A file saved in OneDrive or SharePoint, as a normal .xlsx file. AutoSave can be on or off.
- Sales data with a single header row, one kind of data in each column, and no merged cells. A table or a plain range both work.
Microsoft's tutorial for Copilot in Excel sets out these needs.
Our practice sales file is ready to use. It holds one year of made-up sales for a Singapore coffee and food supplier. In the practice file, the sheet and the table are both called Sales.
The guide has 6 steps. You open Copilot, total sales by month, build a PivotTable, add a chart, ask what changed, and write a summary.
Check which Copilot licence you have
Go to m365.cloud.microsoft and scroll to the bottom of the home page. A label shows under your name. Microsoft's licence page explains each one.
- "M365 Copilot (Premium)" means the paid Copilot seat and the full Copilot in Excel.
- "M365 Copilot (Basic)" means Copilot Chat (free) with standard access to Copilot in Excel.
- "Copilot Chat (Basic)" means you do not have Copilot in Excel.
Step 1: Open Copilot and let it edit the file
Copilot can change your workbook directly, so work on a copy of your file. In Excel, select File > Save a copy, and save the copy to OneDrive. In Excel for the web, select File > Save As > Save a Copy.
- In OneDrive, open the copy in Excel.
- Select the Copilot icon in the lower-right corner of Excel. The Copilot pane opens. Microsoft's getting started page shows the icon and the modes.
- In the Copilot pane, check that the mode is Allow editing. This is the default. Plan shows you the steps before Copilot changes anything. Chat only answers without changing the file.
If the Copilot icon is missing, save the file to OneDrive or SharePoint as .xlsx, then check your licence label. If you do not see Allow editing, your account may not let Copilot change the file. Ask your IT admin.
Step 2: Ask Copilot for the total of each month
Put the dashboard on its own sheet, so the raw data stays as it is. The prompt leaves the top rows empty for the summary in step 6.
Prompt
Add a new sheet called Dashboard. Starting in cell A5, show the total of [Amount (SGD)] for each month of [2025] from the Sales table, with the months in order.
Change [Amount (SGD)] and [2025] to your own column and year. Excel highlights the cells Copilot changed. Select Done to keep the changes, or Undo to reverse them.
If Copilot answers but does not change the file, select Formulas > Calculation Options > Automatic. Copilot can only edit when Excel calculates automatically.
Check one month yourself before you go on:
- On the Sales sheet, filter the Date column to that month.
- Select the Amount (SGD) cells.
- Read the Sum at the bottom of the Excel window.
If the two totals do not match, tell Copilot which month is wrong and ask it to fix the total.
Step 3: Build a PivotTable by region and customer type
A PivotTable shows the same sales split two ways at once.
Prompt
On the Dashboard sheet, starting in cell E5, create a PivotTable from the Sales table with the sum of [Amount (SGD)], [Region] in rows and [Customer type] in columns.
For your own file, change [Amount (SGD)], [Region] and [Customer type] to your own columns, such as Branch or Product.
To see what Copilot will do before it does it, switch the mode to Plan first. Copilot shows the steps, and you confirm them.
Step 4: Add a chart that shows the year
A line chart makes a rise or a drop easy to see.
Prompt
On the Dashboard sheet, under the monthly totals, create a line chart of the monthly totals. Show data labels and gridlines.
Copilot uses Excel's own chart tools, and the chart links to the data.
Step 5: Ask Copilot what changed during the year
Switch the mode to Chat only for this step. Copilot then answers in the pane and leaves the file alone.
Prompt
Look at the Sales table. What changed the most in 2025, by month, region and customer type? List the 2 biggest changes, with the numbers.
The practice file has 2 changes built in, so you can check the answer. One customer type drops for 3 months in the middle of the year. One region grows from about S$47,000 in January to about S$112,000 in December. Copilot may also name a region that drops mid-year, because that region sells to the same customer type. If the answer misses one, ask a narrower question:
Prompt
Compare the monthly sales of each customer type. Which one changed the most, and in which months?
Check each change Copilot names. Filter the Sales table by that region or customer type and month, as in step 2, and read the Sum. Copilot can misread a column, so trust the numbers you can see in the sheet.
Step 6: Write a summary for the boss
Switch the mode back to Allow editing, then ask for the short note your boss would read.
Prompt
Write a one-paragraph summary of [2025] sales for the company owner, in plain English. Include the total, the best and worst months, and the 2 biggest changes. Put it in cell A1 of the Dashboard sheet.
Change [2025] to your own year.
Read the paragraph before you send it. Check that each number in it matches the Dashboard sheet.
The paragraph Copilot writes for the owner goes here. It names the total for 2025, the best and worst months, and the 2 biggest changes.
| Month | Amount |
|---|---|
| Jan | 180,783 |
| Feb | 161,887 |
| Mar | 177,803 |
| Apr | 185,453 |
| May | 186,955 |
| Jun | 192,995 |
| Jul | 165,772 |
| Aug | 170,490 |
| Sep | 175,102 |
| Oct | 212,635 |
| Nov | 218,226 |
| Dec | 249,589 |
| Total | 2,277,690 |
| Region | Cafe | ManufacturerManuf. | RestaurantRest. | Trade | Total |
|---|---|---|---|---|---|
| Central | 91k | 360k | 72k | 45k | 567k |
| East | 332k | 251k | 154k | 112k | 849k |
| North | 86k | 42k | 77k | 206k | |
| North-East | 107k | 141k | 69k | 317k | |
| West | 78k | 117k | 69k | 76k | 339k |
| Total | 694k | 868k | 405k | 311k | 2,278k |
Check 3 things before you share the dashboard
A dashboard goes to people who will act on it, so check it first:
- The numbers match the Sales table, as you checked in steps 2 and 6.
- The chart and the PivotTable cover the full year, January to December, with no month missing.
- The file holds every sales line, not only the dashboard. Anyone you send it to can see each customer and price. To share only the dashboard, save the Dashboard sheet as a PDF. In Excel on Windows, select File > Save a copy and choose PDF as the file type. Then select Options and choose to save only the active sheet. Microsoft's PDF page has the details.
Fix a missing Copilot icon, or a Copilot that does not edit
Microsoft's tutorial for Copilot in Excel lists these causes. We suggest you check them in this order.
- No licence for Copilot in Excel. Check your label at m365.cloud.microsoft.
- The file is not in OneDrive or SharePoint. Save it there as .xlsx. Copilot does not work with the Strict Open XML format.
- Calculation is set to Manual. Select Formulas > Calculation Options > Automatic.
- The file is checked out in SharePoint. Check it back in, or open it in Excel for the web.
- Connected experiences are off. On Windows, select File > Account > Account Privacy > Manage Settings, and turn them on. If your company turned them off, ask your IT admin.
- Your browser blocks third-party cookies for Excel for the web. Allow them for Microsoft 365 in your browser settings.
- Your company uses the Semi-Annual Enterprise Channel for updates. That channel does not support Copilot. Ask your IT admin.
Getting from personal AI to company AI takes 3 steps
You built a dashboard from one file, on your own. Next month, someone has to export the data and ask again. An agent connected to Xero or your POS can do that part every week. Our finance and operations page shows how.
- Personal AIOne person uses Copilot or Claude for their own emails, files and meetings.
- AI set up for the teamCompany accounts that keep data safe, a staff AI policy, training so everyone uses it the same way, and shared agents. See the Copilot course.
- AI that does a team's jobAn agent connected to your systems, such as Xero, WhatsApp or your stock system. It does a shared job and asks before it acts. See Glimmer.
Someone has to pick the plans, write the staff rules, train the team and connect AI to the company's systems. If nobody in your company has the time, Glints does that setup with you. Book a call about setup.
Questions people also ask
Where is Copilot in Excel?
Select the Copilot icon in the lower-right corner of Excel, on Windows, Mac or the web. If the icon is not there, your account may not include Copilot in Excel. Your company may also have turned it off.
Why is the Copilot button greyed out?
Microsoft lists several causes, set out in the section on a missing Copilot icon. The licence may be missing. The file may not be saved in OneDrive or SharePoint as .xlsx. Your company may have turned Copilot off.
Can Copilot update the dashboard every week?
Not on its own. Someone still has to add the new sales to the Sales table. Then, on the PivotTable Analyze tab, select the Refresh arrow and choose Refresh All. In Excel for the web, it is on the PivotTable tab. Microsoft's PivotTable page has the details. You can save the steps as a custom skill in your OneDrive. Next month, type @ and the skill's name to run it. The skill builder, which writes a skill for you, may not be on every account yet. Microsoft's skills page shows how to set one up.
Is my sales data safe with Copilot?
On a work account, Microsoft does not use your prompts, Copilot's answers or your files to train its AI models. Microsoft's privacy page says so. To check which AI plans are safe for company data, use our free safe AI check.
Where to start
This week, export last month's sales to Excel and save the file in OneDrive. Then run steps 2 and 4 on it. The Copilot comparison shows where an agent fits beside Copilot, and Claude vs Copilot compares the two assistants.
To get your managers using Copilot well, register your interest in our Copilot course. To talk about a dashboard that updates itself from your systems, book a call.