How to Create a Sales Dashboard in Excel Using ChatGPT

Coding Liquids tutorial cover featuring Sagnik Bhattacharya for How to Create a Sales Dashboard in Excel Using ChatGPT.
Coding Liquids tutorial cover featuring Sagnik Bhattacharya for How to Create a Sales Dashboard in Excel Using ChatGPT.

To make a sales dashboard with ChatGPT, install the ChatGPT for Excel add-in (Home, then Add-ins, search for ChatGPT), paste last month's export into a sheet formatted as a table, and ask it to build a separate Dashboard sheet with formula-driven KPI cells and two or three charts. Then check every KPI total against the raw sheet.

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

The add-in works inside your open workbook and asks permission before it changes anything. It is on every ChatGPT plan, limited on Free and Go, fuller on Plus at $20 a month and expanded on Pro. If you would rather not install anything, ChatGPT Work can build the workbook from an uploaded export instead, though you then check a file you did not watch being made. Either way, the add-in cannot use pivot tables, slicers, Power Query or macros, so this dashboard runs on plain formulas, which is also why next month's refresh takes minutes rather than an afternoon.

The numbers here come from an illustrative courier firm with 11 vans, whose accounts system exports one row per invoiced job, with the date, customer account, service (same-day, next-day or timed), parcels and net amount. October's export has 2,431 rows: 2,418 jobs and 13 credit notes. Net revenue for the month is $96,412.50. Every figure below comes from that sample month.

If your export needs tidying before it can be charted (merged headers, subtotal rows, text where numbers should be), do that first; cleaning and summarising a monthly sales export with ChatGPT for Excel covers it. From a clean export, the first build takes about an hour, and every month after that is a ten-minute refresh.

Which route is better: the Excel add-in or ChatGPT Work?

For a monthly dashboard the ChatGPT for Excel add-in is the better route, because you build in the workbook you will keep refreshing and see each change as it is made.

ChatGPT for Excel add-inChatGPT Work
Where it worksInside your open workbook, in Excel on the desktop or the webIn ChatGPT: you upload the export and it produces a spreadsheet file
PlansFree, Go, Plus, Pro, Business, Enterprise, Edu (limited on Free and Go; off by default for Enterprise until an admin enables it)All plans on the desktop app (limited on Free and Go); web and mobile on Plus, Pro, Business, Enterprise and Edu
How you see changesIt asks permission before changing cells, so you watch each stepYou review a finished file
Known limitsNo pivot tables or Data Model, Power Query, Office Scripts, slicers, timelines, data validation or VBA macros; chart types limited to a supported setOpenAI advises naming the formulas and charts you want and checking formulas before relying on the file
Best forA workbook you will keep and refresh every monthA one-off, or a computer where you cannot install add-ins

For a monthly dashboard, the add-in wins: you build in the workbook you will keep using, and the formulas are visible as they appear. To install it, open Excel, go to Home, then Add-ins, search for ChatGPT, add it and sign in with your ChatGPT account. Two things to know before you start. Spreadsheet chats are kept separately from your main ChatGPT history and do not use ChatGPT memory, so save your build prompt somewhere you will find it next month (a Notes sheet in the workbook works well). And larger workbooks and more complex requests use more of your plan's agentic allowance, so build the dashboard in a workbook holding only the sales data, not in the 40-tab master file. If the add-in will not appear or sign in, fixes for ChatGPT for Excel not working covers the usual causes.

How should you set up the raw sheet?

Set up the raw sheet as a named table, so that next month is a paste, not a rebuild. OpenAI's own advice for important spreadsheet work is to duplicate the file first so you can go back if needed. Do that, then set up the data side carefully, because every refresh depends on it.

  1. Paste the export into a sheet called Raw. One header row, no blank rows, dates as real Excel dates, amounts as numbers. Keep the credit notes; they are part of net revenue, and the dashboard will handle them explicitly.
  2. Turn it into a table. Select the data and choose Insert, then Table, with "My table has headers" ticked.
  3. Name the table Sales. On the Table Design tab (Table Tools, then Design, in older versions) type Sales in the Table Name box. Names cannot contain spaces. Formulas that say Sales[Net] are far easier to check than formulas that say Raw!E2:E2432, and they grow with the table.
  4. Keep months cumulative. Next month, paste the new rows into the leftmost cell directly below the last row of the table; Microsoft's documentation confirms the table expands to include them. Keeping September's rows is also what makes a "versus last month" KPI possible.

The courier's table had five columns: Date, Account, Service, Parcels and Net. Account holds account codes rather than customer names, which keeps the workbook less sensitive if it is emailed around and changes nothing about the KPIs.

If tables and formulas are the slow part, try The Complete Excel Course with AI Integration on Coding Liquids.

Which KPIs should a one-page sales dashboard show?

A one-page sales dashboard should show a short KPI list, decided before the prompt is written and kept to what fits on one screen. A dashboard prompt without a KPI list produces twelve tiles of whatever the model thinks is interesting. Write down why each one is there. The courier's list, filled in:

KPIWhy the owner watches itOctober (illustrative)
Net revenueThe headline, after credit notes$96,412.50
JobsVolume, separate from price2,418
Average net revenue per jobShows discounting or a shift to cheaper services$39.87
Active accountsAccounts with at least one job this month64
Revenue versus last monthDirection of travel+5.7%
Same-day share of revenueSame-day jobs earn the most per van hour40.4%
Top-5 account share of revenueConcentration risk if a big account leaves35.6%
Credit notes (count and value)Damaged or late deliveries cost money twice13 / -$1,285.00

Each KPI earns its place by prompting a question the owner can act on. When the finished page showed the five largest accounts producing 35.6% of revenue, the owner checked when each of their contracts came up for renewal. When the 13 credit notes came to $1,285.00, the next question was which van and which service they came from, a filter on the Raw sheet rather than another KPI tile. A KPI that never leads to a question like that is a candidate for removal next month.

Below the KPI cells sit three blocks that feed the charts and the top-customer list: revenue for days 1-7, 8-14, 15-21, 22-28 and 29 to the month end; revenue by service; and the five largest accounts. Weekly blocks keyed to the day of the month avoid the arguments about where a week starts, and the fifth block catches the two or three days left over at the end of a month.

What prompt makes ChatGPT build the dashboard?

The prompt that builds the page asks for a plan before big edits, says exactly what to preserve and what to change, and @-mentions the sheets you mean. OpenAI's help article recommends these three habits. The courier's build prompt used all three:

@Raw @Dashboard
Before changing anything, outline exactly which sheets and ranges you will modify.

Build a one-page KPI dashboard on the sheet Dashboard from the table Sales on Raw.
Rules:
- Formulas only, never typed values. Refer to the table by name
  (Sales[Date], Sales[Account], Sales[Service], Sales[Net]).
- Cell B2 holds the first day of the month being reported. Every KPI and
  block must use B2, so changing B2 updates the whole page.
- Credit notes are rows with Net below 0: include them in revenue,
  exclude them from job counts and active accounts.
- No pivot tables, slicers or macros. Do not edit the Raw sheet.
KPIs in B5:B12, labels in A5:A12, a one-line plain-English description of
each formula in C5:C12:
Net revenue; Jobs; Average net revenue per job; Active accounts;
Revenue vs previous month (%); Same-day share of revenue;
Top-5 account share of revenue; Credit notes (count, value).
Blocks:
- Revenue for days 1-7, 8-14, 15-21, 22-28, 29-month end in A16:C20
  (start date, end date, revenue).
- Revenue by service (Same-day, Next-day, Timed) in E16:F18.
- Top 5 accounts by revenue in E22:F26.
Charts: a column chart of the weekly block and a bar chart of the service block.
Checks in H5:J9: compare totals with independent calculations from Raw
and show OK or CHECK.

The plan that came back read roughly like this (illustrative):

1. Dashboard!A5:C12: KPI labels, formulas and descriptions, all driven by B2. 2. Raw: add a helper column F, IsCredit, set to TRUE where Net is below 0, to simplify the job counts. 3. Dashboard!A16:C20 and E16:F18: weekly and service blocks. 4. Dashboard!E22:F26: top five accounts. 5. Two charts placed to the right of the blocks. 6. Dashboard!H5:J9: checks.

Step 2 needed changing before approval. A helper column breaks the "do not edit Raw" rule, and it would have to be filled in again for every month's pasted rows, which is exactly the manual step the setup is meant to remove. The owner replied "No helper columns on Raw; use conditions on Sales[Net] instead" and approved the rest. Reading the plan took two minutes and saved a dashboard that would have quietly broken in November.

The KPI formulas, and what each returned

These are the formulas the build produced, tidied for reading, with October's results. Check that yours follow the same logic, even if the wording differs.

KPIFormulaOctober
Net revenue=SUMIFS(Sales[Net],Sales[Date],">="&$B$2,Sales[Date],"<="&EOMONTH($B$2,0))$96,412.50
Jobs=COUNTIFS(Sales[Date],">="&$B$2,Sales[Date],"<="&EOMONTH($B$2,0),Sales[Net],">0")2,418
Average per job=B5/B6$39.87
Active accounts=ROWS(UNIQUE(FILTER(Sales[Account],(Sales[Date]>=$B$2)*(Sales[Date]<=EOMONTH($B$2,0))*(Sales[Net]>0))))64
Vs previous month=B5/SUMIFS(Sales[Net],Sales[Date],">="&EDATE($B$2,-1),Sales[Date],"<="&EOMONTH($B$2,-1))-1+5.7%
Same-day share=F16/B5 (F16 is the Same-day revenue in the service block)40.4%
Top-5 share=SUM(F22:F26)/B535.6%
Credit notes=COUNTIFS(...,Sales[Net],"<0") and the matching SUMIFS13 / -$1,285.00

September's net revenue was $91,180.00, which is where the +5.7% comes from: $5,232.50 more on a slightly busier month (2,418 jobs against 2,302). The service block split October into Same-day $38,920.00 from 612 jobs, Next-day $41,650.50 from 1,511 jobs and Timed $15,842.00 from 295 jobs, each net of its credit notes.

Two formula details are worth knowing. UNIQUE, FILTER and SORTBY need Microsoft 365, Excel 2024 or Excel 2021. The top-5 block used TAKE with SORTBY, and TAKE needs Microsoft 365 or Excel 2024, so if a colleague opens the file in Excel 2021 that block shows an error; tell ChatGPT your oldest version in the prompt and it can write the block without TAKE.

How do you check every dashboard total?

You check every dashboard total with a five-row check block, where each row compares a dashboard figure with a calculation made a different way, so a mistake in one method does not hide in the other. A dashboard that looks right is the dangerous kind.

  • Revenue, recalculated differently. =SUMPRODUCT((Sales[Date]>=$B$2)*(Sales[Date]<=EOMONTH($B$2,0))*Sales[Net]) against B5.
  • Service split adds up. =SUM(F16:F18) against B5.
  • Weekly blocks add up. =SUM(C16:C20) against B5.
  • Every row is accounted for. Jobs plus credit notes (B6 plus the credit-note count) against =COUNTIFS(Sales[Date],">="&$B$2,Sales[Date],"<="&EOMONTH($B$2,0)).
  • Agreement with the accounts system. Type the month's net sales from your accounts system's own sales report into H9 by hand and compare it with B5. This is the only check that catches a bad export.

Each result cell uses =IF(ABS(H5-I5)<0.005,"OK","CHECK"), so a rounding difference of half a cent passes and anything else is flagged. Here is how the courier's first build fared:

CheckDashboardIndependentFirst buildCause and fix
Revenue$96,412.50$96,412.50OKNone
Service split$96,412.50$96,412.50OKNone
Weekly blocks$89,805.00$96,412.50CHECKOnly four week rows built; the 29-31 block ($6,607.50) was missing. Added the fifth row and extended the chart.
Rows accounted for2,4442,431CHECKJobs formula counted credit notes (2,431 instead of 2,418). Added the Net above 0 condition.
Accounts system$96,412.50$96,412.50OKNone

Both faults would have gone out unnoticed in a quick visual check. The weekly chart looked perfectly normal with four bars, and 2,431 jobs is a believable number for a busy month. Only the arithmetic exposed them. With the fixes made, the five weeks read $21,870.00, $22,640.00, $23,105.00, $22,190.00 and $6,607.50, which add back to the month's $96,412.50.

The typed number hiding among the formulas

One more fault passes every total check: a KPI cell holding a typed value instead of a formula. In the courier's first build, Active accounts showed 64, correct for October and permanently 64, because it had been entered as a value during the build. It would have stayed at 64 in November whatever happened. The check takes a minute: select the KPI range, go to Home, then Find & Select, then Go To Special, choose Formulas and click OK. Excel highlights the cells containing formulas, and any KPI cell left out is a typed number. In the desktop app, Formulas, then Show Formulas (or Ctrl+`) displays every formula on the sheet at once, which makes a quick visual pass easier. The fix was a one-line follow-up asking for the FILTER-based formula shown in the table above.

For a broader set of checks that apply to any AI-built dashboard, not only this one, see six checks before you trust an AI-generated dashboard.

How do you keep the charts working next month?

Charts keep working next month when they take their data from the dashboard blocks, never from the raw table. The weekly block always has five rows and the service block always has three, so the charts never need re-pointing when the raw table grows by 2,400 rows.

  • Weekly revenue, column chart. Five bars, and the short fifth bar is expected, not a bad week. Label it "29-31" (or "29-30") so nobody reads it as a slump.
  • Revenue by service, bar chart. Three bars compare more clearly than three pie slices, and the same-day bar is the one the owner watches.

OpenAI describes the add-in's charting as limited to a current supported set of chart types. If it cannot make the chart you want, select the block yourself and use Insert, then Recommended Charts, and pick from the suggestions or the All Charts tab. The chart still updates with the block, because the block is formula-driven.

How do you refresh the dashboard next month?

You refresh the dashboard by pasting the new month's rows under the table, changing one date cell and reading the five check cells, which takes about ten minutes. This is the part that makes the setup worth the first hour. The courier's November refresh:

  1. Export November from the accounts system.
  2. Paste the rows into the leftmost cell directly below the last row of the Sales table. The table grows to include them.
  3. Change B2 on the Dashboard to 1 November.
  4. Look at the five check cells. Type November's net sales from the accounts system into H9.
  5. If everything reads OK, save the workbook and send the Dashboard sheet.

Ten minutes, and no ChatGPT needed at all unless something reads CHECK. In November something did. The service-split check flagged a gap of $2,140.00, because the accounts team had started labelling a new morning service "Timed AM", which matched none of the three service names in the block. The check did exactly its job: without it, $2,140 of revenue would have vanished from the service chart while the headline revenue stayed correct. The fix was a fourth row in the service block, added with a one-line request to the add-in.

For a courier, the natural next step is an operational view (on-time rate, failed deliveries, parcels per van) alongside the sales page; a logistics dashboard built from shipment spreadsheets shows that side.

How do you build the dashboard on the ChatGPT Work route?

On the ChatGPT Work route the instructions are the same, but the checking burden is higher, because you did not watch the cells being written. Upload the export and be explicit about the file you want:

Create an Excel workbook (.xlsx) from the attached export with two sheets.
Raw: the export as an Excel table named Sales, unchanged.
Dashboard: cell B2 = first day of the month; KPIs in B5:B12 calculated with
formulas from the Sales table (not typed values): [paste the KPI list and
blocks from the add-in prompt above]. Add a column chart of the weekly
block and a bar chart of the service block. Add the five check rows.
Tell me any formula you could not create and any assumption you made.

OpenAI's guidance for Work file creation is to specify the output format and what must stay unchanged, name the formulas and charts that matter, and review the file before relying on it. When the file arrives, open it in Excel and run the same routine as above: the five check cells, Go To Special for typed values, and a comparison with the accounts system's figure. Files created in the cloud may be saved to Library or Space where your plan has them, so download your copy and keep it with your other monthly reports. Next month, you can refresh the downloaded workbook exactly as the add-in version, by pasting rows and changing B2; there is no need to go back to Work.

How long does the first dashboard build take?

The first build takes about an hour, and each month after that about ten minutes. A quick sum, on Plus at $20 a month: deciding the KPI list took 20 minutes, the build prompt and plan approval about 15, the build itself a few minutes of watching and approving, and the checks and two fixes about 25. Call it an hour for the first month and ten minutes for each month after, against the half-day the office manager used to spend rebuilding a summary by hand from the export each month (the owner's estimate, in this illustration). For more ways to use ChatGPT inside spreadsheets beyond dashboards, the older ChatGPT and Excel guide is a good companion.

If you want help fitting a monthly dashboard into how your business actually runs, I offer one-to-one AI consultations for small businesses.

Questions about building Excel dashboards with ChatGPT

Can the ChatGPT add-in build a pivot table dashboard instead of formulas?

No. OpenAI lists pivot tables and the Data Model among the Excel features ChatGPT for Excel does not support, along with Power Query, slicers, timelines, data validation, Office Scripts and macros. A formula-driven page does the same job for a one-page monthly dashboard, and it refreshes by changing one month cell, which is simpler to hand over than a pivot table that someone has to remember to refresh.

Which Excel versions do these dashboard formulas need?

Any current Excel for SUMIFS, COUNTIFS, EOMONTH and EDATE. UNIQUE, FILTER and SORTBY need Microsoft 365, Excel 2024 or Excel 2021. TAKE needs Microsoft 365 or Excel 2024. If anyone opening the file uses an older version, tell ChatGPT the version in the prompt so it writes formulas that work there.

Is my sales data safe in the ChatGPT for Excel add-in?

It depends on your plan. OpenAI says data shared through it is not used to improve its models by default on Business, Enterprise, Edu and Teachers plans, and that some logs may be kept for 30 days for safety. Microsoft may also be able to read workbook content under its add-in marketplace terms. If customer names are sensitive, export account codes instead of names; the dashboard works the same.

Can ChatGPT build an Excel dashboard?

Yes, in two ways. The ChatGPT for Excel add-in builds a Dashboard sheet inside your open workbook and asks permission before it changes cells, and ChatGPT Work can create the workbook from an uploaded export. Either way, check every KPI total against the raw sheet before anyone sees the dashboard.

How long does a ChatGPT Excel dashboard take to build?

About an hour for the first month, then about ten minutes a month. In the courier example, deciding the KPI list took 20 minutes, the build prompt and plan approval about 15, and the checks and two fixes about 25, plus a few minutes of watching the build. That is one illustration, not a benchmark.

Further reads

Sources: OpenAI's ChatGPT for Excel and Google Sheets page and help article, the ChatGPT Work file-creation help article and the ChatGPT pricing page; Microsoft support pages for Excel tables, UNIQUE, FILTER, SORTBY, TAKE, charts and Go To Special, all checked 17 November 2026.

Rather not work this out alone?

An AI implementation consultation is a one-to-one call with me, not an agency or a sales team. You leave knowing what AI you can use in your business and where to start.

Book a 1:1 call with me