LightWork
← All guides

Guide 02 · Data → Insight

Build your first sales dashboard from the data you already have

Use this when the sales numbers exist in your accounting or point-of-sale system, but seeing how the business is doing means exporting, filtering and building the same summary by hand each month. You will finish with a dashboard that checks its own data and updates when you paste in next month’s export.

Time90–120 minutes, then about 10 minutes a month
CostFree route: no paid software. AI workspace route: Claude Pro or ChatGPT Plus
Finish withA checked, refreshable sales dashboard
Last verified26 September 2026

Before you start

What “working” looks like

  • Your sales export sits in one tab, exactly as your system produced it.
  • A checks panel tells you when something is wrong before you trust the numbers.
  • The dashboard total matches your accounting system’s sales report for the same months.
  • You can see monthly sales, the latest month at a glance, top customers and sales by category.
  • Next month, you import the new export and everything updates. Nothing is rebuilt.

What you need

  • A sales export at invoice-line level for the last 12 complete months, as CSV or Excel (step 02 explains which report).
  • Your system’s total sales excluding VAT for the same months, to check against.
  • A Google account the business controls (see guide 01 for why).
  • For the free route: a free ChatGPT account at chatgpt.com.
  • For the AI workspace route: Claude Pro or ChatGPT Plus, with that company’s desktop app on Mac or Windows.

No export to hand yet? Practise with our invented sample export (CSV): 12 months of sales for a fictional office-supplies business. Every name and number in it is made up. The expected results in this guide are for that file.

01

Decide what the dashboard must answer

A dashboard that answers everything answers nothing. This guide builds one that answers three questions most owners ask every month:

  1. Are sales growing, and how did the latest month compare with the one before?
  2. Which product categories bring in the money?
  3. Which customers matter most, and has any of them gone quiet?

If your questions are different, ask ChatGPT for help before you start. It needs no data for this:

Copy this prompt

I run a [type of business] with about [number] customers. Each month I want a one-page sales dashboard built in Google Sheets from my accounting export. Suggest the five measures that would most help me decide where to focus next month, and explain in one sentence why each matters. Don’t suggest anything that needs data beyond invoice date, customer, product, category and amount.

02

Export the right report

Every system names this report differently. Look for a sales report that lists one row per invoice line, often called “sales by item”, “sales detail”, “invoice line detail” or “item sales”. A monthly summary report is not enough.

  1. Choose the last 12 complete months. Leave out the current, unfinished month.
  2. Make sure the report includes: invoice date, invoice or credit-note number, customer, product, category (or product group) and the amount excluding VAT.
  3. Include credit notes. They reduce sales and belong in the total.
  4. Export as CSV if you can; Excel also works.
  5. Run your system’s normal sales summary for the same months and write down the total excluding VAT. You will check against it in step 04.
Keep personal information out: you need a customer’s name or code, not their phone number, email, ID number or address. Remove those columns before importing, or untick them when exporting.

Choose your route

Two ways to build the dashboard

Free route: Google Sheets with ChatGPT as a helper

You build the workbook yourself with the formulas below. ChatGPT adapts them to your columns and explains errors. Your sales data never leaves your spreadsheet.

Continue with the free route ↓

AI workspace route: Claude Cowork or ChatGPT Work

For readers with Claude Pro or ChatGPT Plus. The AI builds the whole workbook to the same specification, and your job is to check its work. This route sends your sales data to the AI company.

Continue with the AI workspace route ↓

03 · Free route

Set up the spreadsheet and import the export

  1. In Google Drive, create a folder named Sales dashboard – live and a new Google Sheet inside it named Sales dashboard.
  2. Rename the first tab Data. Add two more tabs named Checks and Dashboard.
  3. With the Data tab open, select File → Import → Upload and choose your export.
  4. For Import location, choose Replace current sheet. Leave the separator on Detect automatically. Select Import data.
  5. Select View → Freeze → 1 row so the headings stay visible.

This guide assumes the sample file’s column order: A Invoice date, B Invoice number, C Document type, D Customer code, E Customer name, F Sales rep, G Product code, H Product description, I Category, J Quantity, K Unit price excl VAT, L Amount excl VAT, M VAT, N Amount incl VAT. If yours is different, step 05 shows how to adapt the formulas.

Never edit the Data tab by hand: it should always be an exact copy of what your system exported. Fix mistakes in the system, then export again. That way next month’s import can safely replace it.

04 · Free route

Check the data before you build anything

In the Checks tab, enter these labels in column A and formulas in column B. The last column shows what the sample export should give.

LabelFormulaSample result
Rows of data=COUNTA(Data!A2:A)1004
Total sales excl VAT=SUM(Data!L2:L)R2 210 729
First date=MIN(Data!A2:A)1 Sep 2025
Last date=MAX(Data!A2:A)30 Aug 2026
Dates not recognised=COUNTA(Data!A2:A)-COUNT(Data!A2:A)0
Credit notes=COUNTIF(Data!C2:C,"Credit note")5
Credit note value=SUMIF(Data!C2:C,"Credit note",Data!L2:L)−R1 225
Lines with no category=COUNTIFS(Data!A2:A,"<>",Data!I2:I,"")3
Lines that appear more than once=SUMPRODUCT((Data!B2:B<>"")*(COUNTIFS(Data!B2:B,Data!B2:B,Data!G2:G,Data!G2:G,Data!L2:L,Data!L2:L)>1))2

Then work through these before going further:

  • Total sales excl VAT must match the total you wrote down in step 02. If it doesn’t, the export is missing something, or includes VAT or extra months.
  • First and last date should cover your 12 complete months and nothing else.
  • Dates not recognised must be 0. If not, see “Dates show as text” under common fixes.
  • Lines with no category will show as a blank group in the category view. Fix the product setup in your system when you can.
  • Lines that appear more than once counts both copies. In the sample, one line was exported twice. Genuine repeats (the same product twice on one invoice) are possible; check a couple before assuming an error.
Why this comes first: a dashboard makes numbers look certain. Five minutes here stops you making a decision on a total that was wrong from the start.

05 · Free route

Match the formulas to your export

Using the sample file? Skip to step 06. If your export’s columns are different, let ChatGPT rewrite the formulas. Give it the column headings and a few invented rows, never the export itself.

  1. Copy your header row from the Data tab.
  2. Write two or three example rows by hand, with invented customer names and rounded amounts, so ChatGPT can see the date and number formats.
  3. Open chatgpt.com, start a new chat and paste the prompt below with your details filled in.

Copy this prompt

I’m building a sales dashboard in Google Sheets. My sales export is in a tab called Data. These are its column headings, in order, starting in column A: [paste header row]. Here are three example rows with invented names: [paste rows].

The guide I’m following expects these columns: A invoice date, B invoice number, C document type (Invoice or Credit note), E customer name, G product code, I category, L amount excluding VAT.

1. Tell me which of my columns holds each of these, and flag anything missing or ambiguous instead of guessing. 2. Rewrite each of these formulas for my columns, one per line: [paste the formulas from steps 04 and 06–08].

Why only headings and invented rows: ChatGPT needs the structure, not your customers. Keeping real sales data out of the chat keeps it inside your business and out of any AI service.

06 · Free route

Build the monthly view

  1. In the Dashboard tab, type Month in A4, Sales excl VAT in B4 and Invoices in C4.
  2. In A5, enter =EDATE(EOMONTH(MAX(Data!A2:A),-1)+1,-11). This finds the month 11 months before your latest one.
  3. In A6, enter =EDATE(A5,1) and copy it down to A16. You now have 12 months ending with the latest.
  4. In B5, enter =SUMIFS(Data!L2:L,Data!A2:A,">="&A5,Data!A2:A,"<"&EDATE(A5,1)) and copy it down to B16.
  5. In C5, enter =COUNTUNIQUEIFS(Data!B2:B,Data!C2:C,"Invoice",Data!A2:A,">="&A5,Data!A2:A,"<"&EDATE(A5,1)) and copy it down to C16.
  6. Select A5:A16 and choose Format → Number → Custom date and time, then a month-and-year format such as “Aug 2026”.
  7. Select A4:B16 and choose Insert → Chart. In the chart editor, set the chart type to Column chart.

With the sample, B16 (August 2026) shows R212 939 and B15 (July) shows R202 035. The 12 months in column B add up to the total in your checks panel.

07 · Free route

Show the latest month at a glance

In the Dashboard tab, put these labels in E4:E8 and formulas in F4:F8:

LabelFormulaSample result
Latest month=A16Aug 2026
Sales excl VAT=B16R212 939
Change on previous month=B16/B15-15.4%
Invoices=C1643
Average sales per invoice=B16/C16R4 952.07

Format F4 as month and year, F6 as a percentage, and F5 and F8 as currency. “Average sales per invoice” is net of credit notes, so a month with many returns will show a lower figure.

08 · Free route

Add top customers and categories

Top 10 customers, and who has gone quiet

  1. In H4, enter =QUERY(Data!A1:N,"select E, sum(L) where E is not null group by E order by sum(L) desc limit 10 label E 'Customer', sum(L) 'Sales excl VAT'",1) . It fills H4:I14 with your 10 largest customers over the 12 months.
  2. In J4, type Last 3 months. In J5, enter =SUMIFS(Data!L2:L,Data!E2:E,H5,Data!A2:A,">="&A14) and copy it down to J14.

In the sample, Riverside Interiors is the largest customer at R254 836. Karoo Logistics is second over the year but shows R0 for the last three months. That is the kind of line this view exists to catch: a large customer who has gone quiet deserves a call this week.

Sales by category

In H17, enter =QUERY(Data!A1:N,"select I, sum(L) where A is not null group by I order by sum(L) desc label I 'Category', sum(L) 'Sales excl VAT'",1). It lists every category with its sales over the 12 months, largest first, on the same page as everything else.

With the sample, Paper and printing leads at R1 043 574, and a row with a blank category shows the R7 671 on the three lines that have none.

That completes the free route. Continue to step 09 ↓

03 · AI workspace route

Make a safe upload copy and turn off training

  1. Open your export and delete every column that names or identifies a person or business: customer name, sales rep, phone numbers, email addresses, physical addresses, ID numbers and bank details. Keep the customer code; the dashboard uses it instead of the name.
  2. On your computer, create a folder named Sales dashboard and save the copy there as sales-export-upload.csv. Practising? Do the same to the sample file: delete its Customer name and Sales rep columns.
  3. In Claude: select your name, then Settings → Privacy, and turn off Help improve our AI models.
  4. In ChatGPT: open your account menu, then Settings → Data controls, and turn off Improve the model for everyone.
What this does and doesn’t do: this route sends your sales data to the AI company. Turning off training means your conversations won’t be used to train future models. It does not stop the data being sent, so upload only what the dashboard needs.
The trade-off: without names, the dashboard lists customers by code, such as C002. Keep your system’s customer list to hand when you read it. If you would rather see names on screen, use the free route: it never uploads your data.

04 · AI workspace route

Open your AI workspace

Follow the instructions for the tool you pay for. Both lead to the same place.

Claude Cowork (Claude Pro)

  1. Install the Claude Desktop app for Mac or Windows from claude.ai/download and sign in.
  2. If your message box offers Chat and Cowork, select Cowork. Newer accounts have one combined experience and no separate option.
  3. Select Projects in the left navigation, create a project and choose Use an existing folder. Pick your Sales dashboard folder.
  4. Leave the approval mode on Manual for the first build, so Claude asks before each action.

ChatGPT Work (ChatGPT Plus)

  1. Install the ChatGPT desktop app for Mac or Windows and sign in.
  2. In the message box, switch from Chat to Work.
  3. Keep Work locally selected, not Cloud. Only Work locally can use files on your computer.
  4. Give ChatGPT access to your Sales dashboard folder when it asks. Approve each action it proposes during the first build.

Both apps change often. If a label here doesn’t match what you see, look for the option to give the app access to a folder on your computer.

05 · AI workspace route

Ask for the whole workbook in one go

Paste this prompt into Claude or ChatGPT. It is the same for both.

Copy this prompt

Build a sales dashboard workbook from sales-export-upload.csv. It is a sales export at invoice-line level for the last 12 complete months.

Make three tabs. 1. Data: the export exactly as supplied. Do not change, sort, clean or remove anything. 2. Checks: live formulas showing the number of rows; total sales excluding VAT; the first and last invoice date; how many dates aren’t real dates; the number and value of credit notes; lines with no category; and lines that appear more than once (same invoice number, product and amount). 3. Dashboard: live formulas showing sales excluding VAT and the number of invoices for each of the last 12 months, ending with the latest month in the data, with a column chart; the latest month’s sales, change on the previous month, invoices and average sales per invoice; the top 10 customer codes by sales over the 12 months, with each one’s sales in the last three months beside it; and sales by category, largest first.

Rules: every number on Checks and Dashboard must be a formula that reads the Data tab, so replacing the Data tab next month updates everything. Use amounts excluding VAT. Credit notes stay in and reduce sales. Don’t invent or estimate anything. If a column you need is missing or unclear, stop and ask me.

Save it as Sales dashboard.xlsx in this folder. Then give me a short table of every value on the Checks tab.

Why a workbook, not an interactive web dashboard: both tools can build a web dashboard with clickable charts, and it can look impressive. We chose a workbook because you can see and check every formula, it keeps working if you stop paying for the AI tool, anyone in the business can open it, and handing it over is simple. If you want a web view later, build it from the checked workbook, and keep the workbook as the source of truth.

06 · AI workspace route

Check its work

  1. Open the workbook. Select three numbers on the Dashboard tab and confirm the formula bar shows a formula, not a typed number.
  2. Compare the total on the Checks tab with the total you wrote down in step 02. Practising with the sample? Every Checks value should match the “Sample result” column in step 04.
  3. Compare one month on the Dashboard with your accounting system’s report for that month. With the sample, August 2026 is R212 939 from 43 invoices.
  4. Read the top 10 customer codes. With the sample, C001 leads at R254 836 and C002, second over the year, shows R0 for the last three months.
  5. If anything differs, tell the AI exactly which number is wrong and what it should be, and ask it to show the formula it used. Check again after every fix.
Why this matters: an AI tool can produce a convincing dashboard that is quietly wrong. It won’t always say so. Ten minutes of checking is what lets you trust it.

07 · AI workspace route

Save the monthly refresh

Keep this prompt. You will use it every month in step 09.

Monthly refresh prompt

This is my monthly sales dashboard refresh. Use my current Sales dashboard workbook and this month’s export, sales-export-upload.csv. Replace the whole Data tab with the new export exactly as supplied. Don’t change any formula, tab, chart or layout.

Then give me: every value on the Checks tab; the latest month’s sales, change on the previous month, invoices and average sales per invoice; any top-10 customer code whose last three months are well below their usual level; and anything unusual in the checks. Save the result as a new workbook named Sales dashboard followed by the month and year.

Claude Cowork

Open your project and paste the prompt into its Instructions, so Claude follows it in every task in that project. Each month you will start a new task there and say “Run the monthly refresh.”

Claude’s scheduled tasks can’t read folders on your computer and don’t offer a monthly option, so you run the refresh yourself after exporting. That suits the job: someone has to export from your system first anyway.

ChatGPT Work

Save the prompt as a note in your Sales dashboard folder. Each month, open the desktop app, switch to Work with Work locally selected, give it the same folder and paste the prompt.

Work can also save a reference file with reusable instructions as a template, which saves pasting the prompt each month.

08 · AI workspace route

Know what you’re trusting

  • The workbook is yours. It keeps working without the AI tool; only the refresh needs it.
  • Keep last month’s workbook. If a refresh goes wrong, you can compare the two or go back.
  • The AI’s summary is a convenience. The Checks tab is the evidence. Read the checks, not only the summary.

09

Refresh it next month

  1. Export the same report for the latest 12 complete months.
  2. Free route: open the Data tab and repeat File → Import → Upload → Replace current sheet. AI workspace route: make the upload copy as in step 03, then run your saved refresh from step 07.
  3. Open Checks. Compare the total with your system’s sales summary and look at every other line.
  4. Open Dashboard. Everything has moved on by one month.
Why replace instead of adding rows: credit notes and corrections can change past months. Replacing the whole export each time keeps the dashboard identical to your accounting system.

10

Keep it useful

  • Monthly: refresh, check, then spend five minutes on the three questions from step 01. Write down one action.
  • Quarterly: ask whether the three questions are still the right ones.
  • When your system changes: compare the new export’s columns with the old ones before importing, and use step 05 to adapt the formulas.

Common fixes

Dates show as text (Dates not recognised is above 0)
Check File → Settings → Locale matches the date format in your export, then import again. Exporting dates as year-month-day avoids the problem altogether.
The total is higher than your accounting report
Look for VAT-inclusive amounts, an extra month, or lines exported twice.
A formula shows #ERROR! or #VALUE!
If your spreadsheet uses semicolons, replace the commas between formula parts with semicolons. Otherwise paste the formula and error into ChatGPT and ask what is wrong, without any data.
One customer appears twice with slightly different names
“Riverside Interiors” and “Riverside Interiors (Pty) Ltd” split one customer’s total and can push them out of the top 10. Correct the name in your system. Or group by customer code instead: change every E to D in the top-customers formula, and Data!E2:E to Data!D2:D in the Last 3 months formula.
The latest month looks too low
The export probably includes the current, unfinished month. Export complete months only.

Official instructions used in this guide

Tool interfaces change. These official instructions were checked on 26 September 2026:

If you would rather not do this alone

Bring the export you have. We’ll help you turn it into numbers you can act on.

This guide is enough on its own when your sales come from one system, the checks panel matches your accounting report, and three questions cover what you need to know each month.

It’s worth getting help when:

  • sales come from more than one system, such as a till and an online shop;
  • the checks never quite match your accounting report;
  • you need margins, targets or cash collected, not only sales;
  • several people need different views of the same numbers;
  • or you’ve built it and nobody uses it.
Ask LightWork for help