Multi-store sales template
By the Analistable team · Updated · 2 min read
A free workbook for retailers with several shops: paste every store's sales lines into one Sales sheet, list stores with their region and target, and the Summary shows units, net sales, % of target, average price per unit and a sales rank for each store, plus a group total.
Part of our guide: How to consolidate data in Excel
What the template does
Each till system or store manager usually sends its own sales report. Rather than copying each total into a summary by hand, this template stacks all lines into one table with a Store column and calculates the roll-up from it. That is the same pattern as combining monthly sales reports into one spreadsheet, and the consolidating data in Excel guide covers the alternatives.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Sales | Store, Month, Category, Units, Net sales, Store check | First five columns |
| Stores | Store, Region, Target | Yes |
| Summary | Store, Region, Units, Net sales, Target, % of target, Avg per unit, Rank | No — calculated |
How to fill it in
- Paste each store's report into Sales under the last one, with the store name on every row.
- Use net sales (after discounts and returns, excluding VAT) so stores are comparable.
- Keep Stores as the master list; the Summary has one row per store on it.
- If Store check says 'Store not in list', fix the spelling or add the store.
How the formulas work
Net sales per store:
=SUMIFS(Sales!$E$2:$E$1000,Sales!$A$2:$A$1000,A2)Rank without the RANK function, so it behaves the same everywhere — count stores with higher sales, add one:
=COUNTIFS($D$2:$D$6,">"&D2)+1Percentage of target, rounded to four decimals:
=IFERROR(ROUND(D2/E2,4),0)Worked example
| Store | Region | Units | Net sales | Target | % of target | Avg/unit | Rank |
|---|---|---|---|---|---|---|---|
| Manchester | North | 890 | 29570 | 30000 | 98.6% | 33.22 | 1 |
| Leeds | North | 680 | 23160 | 22000 | 105.3% | 34.06 | 3 |
| Bristol | South West | 730 | 23870 | 25000 | 95.5% | 32.7 | 2 |
| Cardiff | Wales | 435 | 14470 | 15000 | 96.5% | 33.26 | 5 |
| Brighton | South East | 670 | 22380 | 20000 | 111.9% | 33.4 | 4 |
| Total | 3405 | 113450 | 112000 | 101.3% |
Manchester sells the most but misses target; Brighton ranks fourth on sales yet beats target by the widest margin. Leeds has the highest average price per unit because footwear is a bigger share of its mix. To see which products drive this, find the top products across stores.
Limits and when to move on
Pasting works for a few stores once a month. With daily exports or dozens of branches, use Power Query's From Folder to stack the files automatically, or load them into Analistable and ask 'net sales by region and category this month vs last' — the files are read in your browser and nothing is stored.
Frequently asked questions
- Can I add a region summary?
- Yes. Add a sheet with one row per region and use SUMIFS over the Summary sheet's Net sales and Target columns keyed on Region.
- How do I compare months?
- Add the month as a second SUMIFS condition and make one Summary block per month, or a Month cell that the formulas read.
- What happens on a tie in the rank?
- Tied stores get the same rank and the next rank is skipped, the same as Excel's RANK.EQ.