Retail KPI Dashboard in Excel: A Weekly Sheet From Till Data With AI

Export the week's till lines, clean them with rules an AI proposes (dates, duplicates, refunds, gift cards, name variants), prove the clean-up against the till's own sales total, then feed a small workbook that shows net sales, margin, stock cover and best and worst sellers. Excel's Copilot or Gemini in Google Sheets can write the formulas.

The order matters: clean first, build second. Blank dashboard templates look fine until real till data goes in, and then refunds, duplicate lines and gift cards quietly distort every number. The worked example below uses one week from an independent kitchenware and tableware shop; all figures are illustrative.

Follow me on Instagram@sagnikteaches

The export as it arrives: six problems in one week of lines

The shop's till exports every sale line for a chosen week as a CSV. Opened in a spreadsheet, the first thing you notice is that it looks fine. It has 563 rows and a total of $8,102.10 in the Line total column. A few rows show what is hiding in it (illustrative):

Connect on LinkedInSagnik Bhattacharya

Reading down: the first two rows may be the same line exported twice. A negative quantity is a refund. The gift card is cash taken in, not a product sold. The misc line has no real product code, so it has no cost. The same product appears as "COFFEE BEANS 1KG" and "Coffee Beans 1 kg", which a pivot table would treat as two products. And every date is text, not a date. In full, the file contains:

Subscribe on YouTube@sagnikteaches

Let AI find the problems, then fix them with rules, not retyping

Start by asking for a diagnosis only. The instruction to change nothing is deliberate, because a model asked to "clean this data" can start rewriting rows, and you will have no record of what moved.

I'm uploading one week of till export lines. Do NOT change or clean anything yet.
Report every data-quality problem you can find, as a table: problem, example row (receipt and code), rows affected, effect on net sales.
Look for: dates stored as text; the same receipt, code, quantity and time appearing twice; negative quantities (refunds);
lines that are not product sales (gift cards, deposits); open-price or "misc" lines with no product code;
descriptions that differ for the same code; zero-value lines; blank cells.
Then total the Line total column and show the working.

If you use Copilot in Excel, the same prompt works in its chat-only mode, which analyses the workbook without changing it; Microsoft's Copilot in Excel guide describes the edit, plan and chat modes. A chat assistant that accepts spreadsheet uploads, such as ChatGPT or Claude, also works, and for a file like this you should delete personal columns before uploading. A good answer lists the six problems above. Expect it to overreach on duplicates: in this file it might flag nine lines when only six are genuine. The other three are greeting cards scanned one at a time within the same minute on a single receipt, which are real sales. Removing all nine would understate the week by $10.50 (three cards at $3.50). The reconciliation in the next section is what catches this, and it is why you never accept a duplicates list on trust.

Once the diagnosis looks right, ask for rules and not rewritten data. A model that retypes 563 rows can change a quantity without telling you; a formula applies the same rule to every row and can be checked once.

Based on that list, write the clean-up as spreadsheet steps, not as retyped data. For each rule give the Excel formula or filter and the column it goes in.
Rules: 1) convert the text date to a real date; 2) flag possible duplicates (same receipt, code, quantity and time) in a helper column, do not delete;
3) keep refunds as negative lines; 4) mark gift cards "Exclude" and misc lines "Unmapped"; 5) replace descriptions with the standard name from my Products table, matched by code.
Do not guess. Anything uncertain goes in a column called Review.

For the date rule, the formula you should expect is explicit about positions rather than leaving Excel to guess. For text like "21/10/2026 11:42" in A2:

=DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))

Dates are where a locale mismatch bites. If your computer reads dates month-first and the till exports day-first, a value like 21/10/2026 cannot be read and stays as text, which is the visible symptom in this file. But a week running from 2 to 7 November would be read silently as 11 February through 11 July and look perfectly normal, and the explicit formula cannot repair a cell Excel has already turned into the wrong date. So do not double-click the CSV to open it. Use Data, then From Text/CSV, choose Transform Data, and in Power Query set the date column's type to Text before loading; the formula then reads every row the same way. (Power Query's Change Type, Using Locale option with a day-first locale does the conversion in one step and makes the formula unnecessary, if you prefer.) In Google Sheets, the spreadsheet's locale under File, then Settings, controls its date format, so check it before importing. Either way the check afterwards is simple: the minimum date must be the Monday, the maximum must be the Saturday, and the file must span exactly six trading days. If your till drops leading zeros ("2/11/2026"), the fixed positions in the formula break, so ask the AI for a version that splits on the slashes instead. For the broader clean-up moves, cleaning messy data with AI covers them.

The control total: one check that proves the clean-up

A cleaned file you cannot prove is a guess. Your till has its own weekly sales summary, produced by its own logic, and the cleaned sheet's net sales must match it. Here is the reconciliation for the example week:

When the difference is not zero, the size of the gap is a clue. A gap of $10.50 after removing nine "duplicates" says three of them were real. A gap of exactly one product's price says a line went missing. A gap equal to a gift card says the exclude rule did not run. Add two cheaper checks. The count of distinct sale receipts should match the till's transaction count (323 here). And the best-selling product by units should be one you recognise; a surprise at the top of the list can point at a code mix-up.

Ask the AI to do the arithmetic only if you also check it: paste the five totals and ask it to confirm the difference. Better still, put the reconciliation in the workbook as formulas, so it recalculates each week and goes red when it is wrong.

Set up the workbook: three working sheets and one input cell

Keep the structure boring. Each week's export lands on a Raw sheet, where the rule columns sit beside it. A Sales sheet holds every cleaned line as an Excel table named tblSales, with the columns Date, Receipt, SKU (the till's product code), Product, Qty, NetSales (the till's line total), UnitCost, LineCost and InMargin. A Products sheet holds one row per product as tblProducts: SKU, Product, Category, UnitCost and OnHand (stock on hand), then the calculated columns. A Dashboard sheet has one input cell, C2, holding the Monday that starts the week, and everything else reads from it. Each week you append the new cleaned lines to the bottom of tblSales, leaving out confirmed duplicates and anything marked Exclude, and change the Monday. You never delete earlier weeks, because stock cover needs four.

Raw sheet (export pasted in A:H: date/time, receipt, code, description, qty, unit price, disc, line total;
rule columns filled down beside it)
Date        =DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))
DupCheck    =IF(COUNTIFS($A:$A,A2,$B:$B,B2,$C:$C,C2,$E:$E,E2)>1,"Check","")
Type        =IF(C2="GIFT","Exclude",IF(C2="MISC","Unmapped","Sale"))
Product     =XLOOKUP(C2,tblProducts[SKU],tblProducts[Product],"Unmapped")

Sales sheet (table tblSales; cleaned lines appended as values)
UnitCost    =XLOOKUP([@SKU],tblProducts[SKU],tblProducts[UnitCost],0)
LineCost    =[@Qty]*[@UnitCost]
InMargin    =IF([@UnitCost]>0,1,0)

Products sheet (table tblProducts)
Units_wk    =SUMIFS(tblSales[Qty],tblSales[SKU],[@SKU],tblSales[Date],">="&Dashboard!$C$2,tblSales[Date],"<"&Dashboard!$C$2+7)
Units_4wk   =SUMIFS(tblSales[Qty],tblSales[SKU],[@SKU],tblSales[Date],">="&Dashboard!$C$2-21,tblSales[Date],"<"&Dashboard!$C$2+7)
GP_wk       =SUMIFS(tblSales[NetSales],tblSales[SKU],[@SKU],tblSales[Date],">="&Dashboard!$C$2,tblSales[Date],"<"&Dashboard!$C$2+7)-[@Units_wk]*[@UnitCost]
GP_4wk      =SUMIFS(tblSales[NetSales],tblSales[SKU],[@SKU],tblSales[Date],">="&Dashboard!$C$2-21,tblSales[Date],"<"&Dashboard!$C$2+7)-[@Units_4wk]*[@UnitCost]
Cover_wks   =IF([@Units_4wk]<=0,"no sales",[@OnHand]/([@Units_4wk]/4))

Dashboard (week start typed in C2; last week's column uses D2 =C2-7 and the same formulas)
C5 Net sales        =SUMIFS(tblSales[NetSales],tblSales[Date],">="&C2,tblSales[Date],"<"&C2+7)
C6 Margin basis     =SUMIFS(tblSales[NetSales],tblSales[Date],">="&C2,tblSales[Date],"<"&C2+7,tblSales[InMargin],1)
C7 Cost of those    =SUMIFS(tblSales[LineCost],tblSales[Date],">="&C2,tblSales[Date],"<"&C2+7,tblSales[InMargin],1)
C8 Gross margin %   =(C6-C7)/C6
C9 Transactions     =IFERROR(COUNTA(UNIQUE(FILTER(tblSales[Receipt],(tblSales[Date]>=C2)*(tblSales[Date]<C2+7)*(tblSales[Qty]>0)))),0)
C10 Avg transaction =IFERROR(C5/C9,0)
C11 Units per txn   =IFERROR(SUMIFS(tblSales[Qty],tblSales[Date],">="&C2,tblSales[Date],"<"&C2+7)/C9,0)
Top 5 by profit     =TAKE(SORT(FILTER(HSTACK(tblProducts[Product],tblProducts[Units_wk],tblProducts[GP_wk]),tblProducts[UnitCost]>0),3,-1),5)
Bottom 5, in stock  =TAKE(SORT(FILTER(HSTACK(tblProducts[Product],tblProducts[Units_4wk],tblProducts[GP_4wk],tblProducts[Cover_wks]),(tblProducts[OnHand]>0)*(tblProducts[UnitCost]>0)),3,1),5)
Under 2 wks cover   =FILTER(HSTACK(tblProducts[Product],tblProducts[OnHand],tblProducts[Cover_wks]),ISNUMBER(tblProducts[Cover_wks])*(tblProducts[Cover_wks]<2),"none")

These use dynamic-array functions (UNIQUE, FILTER, SORT, TAKE and HSTACK) that Microsoft 365 Excel has and some older versions lack. In an older version, use a PivotTable on tblSales with product in rows and net sales as values, then filter to the top ten. InMargin is 0 for any line without a known unit cost, misc lines included; counting them would raise the margin by treating $71.00 of sales as pure profit. For the same reason both product lists skip products with no unit cost, so an unmapped line cannot pose as your best earner. DupCheck marks both copies of a possible duplicate for you to judge; it never deletes. Two details trip people up. Cover_wks holds the text "no sales" for lines that sold nothing in four weeks, and Excel ranks text above every number in a comparison, so a filter for cover over 16 weeks without ISNUMBER would sweep those lines in too (list them separately: with stock on hand, they are your dead stock). And because the week-start date is joined into the SUMIFS criteria (">="&C2), C2 must hold a real date, not text that looks like one. The Sheets version of the SUMIFS is the same idea (SUMIFS over a Sales tab, with the dates compared to a week-start cell), and Gemini can write the rest, as the section on tools below explains.

Headline numbers: this week against last, and what each is for

The brief for a weekly sheet is short. It should tell you how much you sold, whether you made money doing it, what to order, what to push and what to stop buying. In the example week the page reads like this, with last week's figures beside it:

Gross margin is gross profit divided by the net sales of lines that have a known cost: $3,940.82 on $7,766.60 here. Sales rose 6.1%, and the table shows where it came from: more transactions (+2.2%) and a higher spend per visit (+3.8%). Units per transaction stayed flat, so the extra spend came from what people bought, not from how many things. That sentence is what you want from the sheet; the AI can write it if you ask it to describe the changes and nothing else, and you check each figure against the table. Do not ask it why the week was up. The till does not know about the weather or a school holiday.

Best and worst sellers: judge by profit, not only units

Ranking by units flatters cheap lines. The greeting card sold 65 this week, level with the 1 kg coffee beans and behind only the 250 g bags (89), and earned $167.70 of gross profit. The chef's knife sold 11 and earned $358.60. So rank by gross profit for the best-seller list, and show units beside it for context.

The worst sellers need a different test. One week of low sales for an expensive item proves little, so use the last four weeks, and only among lines with stock on hand, because an item you do not hold cannot sell. Sort by four-week gross profit, lowest first.

This list does two jobs. The recipe book, apron and platter are slow and overstocked, with 18 to 26 weeks of cover, which is a decision to make: discount, bundle or stop reordering. The hot chocolate tin sells steadily but earns little per tin, which is a pricing conversation rather than a stock one. A dashboard that only shows units would have missed the second. For the pricing conversation, checking margins product by product goes further.

Stock cover: weeks of sales on the shelf

Stock cover is stock on hand divided by average weekly units sold over the last four weeks. A line selling 66 a week with 60 on the shelf has 0.9 weeks of cover: it will run out before the next delivery arrives unless something is ordered now. A line selling four a week with 104 on the shelf has 26 weeks of cover and has used up cash that could have gone elsewhere. The sheet reads the stock figure from your till's stock report, which you refresh each week, and the four-week average from tblSales.

The thresholds are yours to set. Two weeks is "urgent" only if your supplier takes about two weeks to deliver; with a four-week lead time, anything under five weeks of cover is already late. For stock that does not keep, such as fresh food, the high alert may be two or three weeks, not sixteen. Cover is also a rear-view number: it uses the last four weeks, so a seasonal rise (candles in the run-up to the gift season, for instance) makes it optimistic. How small shops use AI to forecast stock and reorder covers forward-looking methods.

Excel with Copilot or Google Sheets with Gemini: what each adds

Excel. Microsoft's support page says Copilot in Excel can edit worksheets, generate formulas, create charts and PivotTables, and "make dashboards, planning tools, and templates from scratch". It opens in an edit mode that changes your workbook directly, has a plan mode that shows its approach for you to confirm first, and a chat-only mode that changes nothing. Use plan mode on a copy the first time. The same page warns that Copilot may not appear if your subscription or organisation settings do not include it, so check your licence before building a routine around it. Microsoft's pages also disagree on the file requirement: some say the workbook must be saved to OneDrive or SharePoint with AutoSave on, while another says Copilot runs with AutoSave on or off. If the button is greyed out, saving to OneDrive and switching AutoSave on is the usual first thing to try. And do not use the =COPILOT() worksheet function for any of this: Microsoft retired it on 14 September 2026, and recalculated cells show #NAME?. Ask the side pane to write ordinary formulas instead.

Using the tables tblSales and tblProducts, build a Dashboard sheet. The week start date is in C2.
Show net sales, gross profit and margin % (excluding rows where InMargin is 0), receipts, average transaction value
and units per transaction for that week, next to the previous week. Then the top 5 products by gross profit this week,
the 5 lowest by four-week gross profit among products with stock on hand, and products with under 2 weeks of cover.
Use formulas, not pasted values. Do not change tblSales.

The last two sentences matter. "Use formulas, not pasted values" keeps the sheet live, and "do not change tblSales" keeps your clean data safe. After it builds, test it: change the week-start date and confirm every number moves; compare net sales with your control total.

Google Sheets. Google's help page for building spreadsheets with Gemini says the feature needs an eligible Google Workspace or Google AI plan and works best on native Sheets files; to use it on an Excel file, use File, then Save as Google Sheets. Its example prompts include "Create a visual dashboard of this sales data set", and the page says a personal Google account may get a limited number of uses a month. Gemini's panel can build a dashboard from your data; for a weekly routine, ask it to use native formulas keyed to a week-start cell, as in the Excel version, rather than a one-off chart. Sheets also has an =AI("prompt", range) function, useful for categorising a few hundred product names, but it fills 350 cells at a time, cannot be nested and does not refresh on its own, so it is a poor basis for a weekly dashboard. Use it for the one-off job of tidying the product list, and keep the dashboard on plain formulas. Google's Gemini in Sheets guide lists the current limits. For Gemini-written formulas specifically, see Gemini in Google Sheets for pricing and margin formulas.

The fifteen-minute Monday routine

1. Export last week's lines from the till for Monday to Saturday (about 2 minutes), and delete any personal columns.
2. Load it into the Raw sheet with the date column kept as text (save each week's export over the same file name and the Power Query import just needs Refresh), then fill the rule columns down (2 minutes).
3. Reconcile. Cleaned net sales against the till's weekly figure, receipts against its transaction count, and date range against the week (3 minutes). Stop if any do not match.
4. Append the cleaned lines to tblSales, refresh the stock-on-hand column from the till's stock report, and change the Monday in C2 (3 minutes).
5. Read the page. Ask an assistant to summarise what changed in five lines and check each figure against the sheet (3 minutes).
6. Decide three actions: one thing to reorder, one to push, one to stop buying (2 minutes).

That is 15 minutes when the rule columns are built. The first week takes longer, mostly in the clean-up and the product list, and the product list is where most of the one-off effort goes: codes, names, categories and unit costs for every line. If you do not know a cost, leave it blank and mark the line as unmapped; a margin number built on guessed costs is worse than none.

Step 5 is where a confident sentence can slip past you, so give the assistant a tight brief:

Here are this week's and last week's dashboard figures, the top five and bottom five lists and the cover alerts.
Describe what changed in five lines or fewer. Quote every figure exactly as given.
Do not give reasons, forecasts or advice.
[paste the tables]

An illustrative first answer:

1. Net sales rose 6.1% to $7,837.60 on 323 transactions (+2.2%).
2. Average transaction value rose 3.8% to $24.27; units per transaction held at 1.91.
3. Gross margin edged up to 50.7% from 50.5%, helped by strong coffee bean sales.
4. Coffee beans 1 kg led gross profit at $495.00, ahead of the chef's knife at $358.60.
5. The recipe book, apron and serving platter each hold 18 or more weeks of cover.

Line 3 needs fixing. "Helped by strong coffee bean sales" is a reason, and nothing on the page says what moved the margin, so delete the clause. The other four lines match the tables, which you confirm by ticking each figure against the sheet rather than trusting the tone. Watch for numbers the sheet never produced: if a summary says "units up 5%", ask where that came from, because this dashboard does not show total units (on these figures they rose about 2%, from roughly 604 to 617).

How the dashboard goes wrong

• A promotion week. Sales rise and margin falls because of the discount. Put a note on the week; do not read the dip as a pricing problem.
• The stock snapshot does not match the sales week. If stock was counted on Monday morning and sales run to Saturday, cover is overstated by a week of sales for fast lines. Take the stock report at the same moment each week.
• Stale costs. Margin is only as good as the unit cost column. Update it when a supplier price changes, or you will report 50% on a line that now earns 42%.
• New lines. A product with two weeks of history has a four-week average that is half what it should be, so its cover looks twice as long. Mark new lines and ignore their cover.
• Reused or changed codes. If a code is reassigned to a new product, history merges. Retire a code rather than reusing it.
• Refunds landing in a later week. A return processed in the week after the sale reduces that week's sales and not the original one. It is a fair result of the way the till records it, but know it is there when a week looks odd.

The build effort is front-loaded and the weekly effort is small. Creating an Excel dashboard with Copilot covers the errors that come up when you ask Copilot to build a one-page view, and if your next question is staffing against the busy hours, the same cleaned data feeds a rota built from hourly till sales.

Retail dashboard questions from shop owners

Can ChatGPT build the dashboard from my till export?

It can analyse and chart an uploaded file, and it is useful for the one-off clean-up and for writing formulas. For a weekly repeat, though, you want a workbook whose formulas recalculate when you paste in a new week, so the numbers and charts update without another conversation and without the chance of a different answer each time.

What is stock cover, and what number is good?

Stock cover is the weeks of sales you have on the shelf: stock on hand divided by average weekly units sold. There is no universal good number. Set your low line from supplier lead time, so a line with a two-week lead time needs reordering well before two weeks of cover, and set your high line where money is tied up in slow stock.

Do I need Copilot to do this in Excel?

No. The formulas shown work in Microsoft 365 Excel, and a PivotTable gives you the top and bottom sellers in any version. Copilot saves time writing formulas and building the layout, but check the licence first, and never rely on it for numbers you have not reconciled to the till.

Is it safe to upload a till export to an AI tool?

Remove customer names, loyalty IDs, email addresses and card details before the file goes anywhere. A weekly sales line needs only date, receipt number, product code, quantity, price and discount. Business plans from the main vendors do not train on your content by default, while consumer plans have a switch in privacy settings.

Further reads

• How to Find Dead Stock and Slow Movers With AI — Act on the slow movers your stock-cover column flags.
• How to Use AI to Set Reorder Points and Prevent Stockouts — Turn low stock cover into reorder points.
• Best AI Inventory Tools for Small Retailers Compared — When the spreadsheet is outgrown and you need inventory software.
• How to Build a KPI Dashboard With AI When You Have No Data Team — The wider KPI set, built without an analyst.
• AI KPIs for Small Businesses: 12 Metrics Worth Tracking — Measure whether the AI steps in a routine like this pay off.
• Examples of AI in Retail: Eight Jobs a Small Shop Can Copy — Eight more retail jobs a small shop can hand to AI.
• AI Food Insights Tool: Toast IQ, Square AI or a Spreadsheet? — Compare Toast IQ, Square AI and a spreadsheet with ChatGPT using the same four sales questions, with a scoring sheet and a way to check every answer.
• AI for Retail Store Design: Layout and Display Ideas — Plan a shop re-layout with AI, safely: measure, get three options, tape-test the best, check exits and access, then measure whether sales moved.
• Customer Loyalty Programmes for Retail Stores: Can AI Design One? — A small shop's loyalty programme drafted with AI: points, stamps and tiers costed line by line, plus the five corrections the draft needed.
• How AI Can Help in Sales: A Gift Shop's Bundle and Follow-Up Plan — A gift shop's AI sales plan built on its own till data: three priced bundles, gift-note wording and four follow-up messages for one holiday month.
• Multi-Unit Franchisee: One Weekly AI Summary Across Locations — How a multi-unit franchise owner turns each store's POS export into one KPI sheet, an AI-written weekly summary, an exceptions list and a call sheet.
• AI Footfall Cameras for a Retail Store: What They Count and Privacy — What a footfall camera really counts, how to turn the numbers into a better rota, and the privacy checks and signage to settle before you install one.
• AI Pricing for Grocery Stores: Can It Set Short-Date Markdowns? — Turn an expiry list into markdown rules an AI can apply, with one chilled aisle worked through and a two-week check on waste and margin.
• How Can AI Help a Small Supermarket? Pricing, Stock and Posters — A one-store version of what the chains do: a weekly price check, a reorder list and offer posters, with the prompts, sums and checks to run each.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Microsoft support (Get started with Copilot in Excel) and Google Docs Editors Help (Build or edit entire spreadsheets with Gemini in Sheets). The shop, till export and figures in the worked example are illustrative.