Sales Tracker Spreadsheet: Log Every Sale, Track Targets by Rep and See Revenue, AOV and Margin at a Glance
How to build a sales tracker spreadsheet in Excel or Google Sheets: one sales log table, monthly summaries with SUMIFS or a PivotTable, targets and attainment by rep, average order value, gross margin, repeat customers and commission, with a worked example for a small wholesale team.
Quick answer: a sales tracker spreadsheet works best with one log table where every sale is a row (date, order ID, customer, rep or channel, product, quantity, unit price, discount, cost), plus a summary sheet that rolls the log up by month, rep, product and customer. The core formula is a conditional sum:
=SUMIFS(Sales[Revenue],Sales[Month],$A5,Sales[Rep],B$4)
From there you can track revenue against targets, average order value (AOV), gross margin, repeat customers and commission without retyping anything. Below is the structure and a worked example for a small wholesale team; all figures are illustrative.
Step 1: The sales log
| Column | What goes in it | Formula or tip |
|---|---|---|
| Date | Order or invoice date | Data validation: date |
| Month | First day of the month | =EOMONTH([@Date],-1)+1 |
| Order ID | Unique reference | Use it to link invoices |
| Customer | Name or customer ID | Drop-down from a Customers table |
| Rep / Channel | Who or where the sale came from | Drop-down |
| Product / SKU | From a Products table | Drop-down |
| Qty, Unit price, Discount % | Inputs | |
| Revenue | Net of discount | =[@Qty]*[@[Unit price]]*(1-[@[Discount %]]) |
| Unit cost | Looked up | =XLOOKUP([@SKU],Products[SKU],Products[Cost]) |
| Gross profit | Revenue − cost | =[@Revenue]-[@Qty]*[@[Unit cost]] |
| Status | Paid, invoiced, returned, cancelled | Exclude cancelled rows from totals |
Format the range as an Excel table named Sales (Ctrl+T). New rows inherit the formulas, and every summary updates automatically. Keep sales tax out of Revenue so your figures stay comparable across states and channels.
Once you have several comparable periods, use completed sales as the baseline for a sales forecast. Keep open deals separate from booked revenue so the same sale is not counted twice.
Step 2: Monthly summary
- Revenue by month:
=SUMIFS(Sales[Revenue],Sales[Month],A5,Sales[Status],"<>Cancelled") - Number of orders (one row per order):
=COUNTIFS(Sales[Month],A5,Sales[Status],"<>Cancelled"). If an order can span several rows, count unique IDs in Excel 365:=COUNTA(UNIQUE(FILTER(Sales[Order ID],Sales[Month]=A5))) - AOV:
=Revenue/Orders - Gross margin %:
=SUMIFS(Sales[Gross profit],Sales[Month],A5)/SUMIFS(Sales[Revenue],Sales[Month],A5)
Prefer clicking to typing? Insert a PivotTable with Month in rows, Rep in columns and Sum of Revenue in values, then add slicers for Product and Status.
Step 3: Targets and attainment by rep
Keep monthly targets in a small table (Targets: Month, Rep, Target). Attainment is actual divided by target:
=SUMIFS(Sales[Revenue],Sales[Rep],A5,Sales[Month],$B$1)/XLOOKUP(1,(Targets[Rep]=A5)*(Targets[Month]=$B$1),Targets[Target])
Worked example: a three-person wholesale team, one month
| Rep | Target | Revenue | Attainment | Orders | AOV | Commission (3%) |
|---|---|---|---|---|---|---|
| Maya | $40,000 | $43,200 | 108% | 48 | $900.00 | $1,296.00 |
| Jon | $35,000 | $29,750 | 85% | 41 | $725.61 | $892.50 |
| Priya | $30,000 | $31,500 | 105% | 35 | $900.00 | $945.00 |
| Team | $105,000 | $104,450 | 99.5% | 124 | $842.34 | $3,133.50 |
With a 38% gross margin, the month produced about $39,691 of gross profit. The team almost hit target, but the detail tells a different story: Jon’s order count is close to Priya’s, yet his AOV is about $175 lower. That points to smaller baskets or heavier discounting rather than a lack of activity, which is a coaching conversation about bundles and pricing, not about calling more prospects.
Two formulas worth adding to the rep table:
- Average discount given:
=AVERAGEIFS(Sales[Discount %],Sales[Rep],A5,Sales[Month],$B$1) - Gross margin by rep:
=SUMIFS(Sales[Gross profit],Sales[Rep],A5)/SUMIFS(Sales[Revenue],Sales[Rep],A5)
Commission plans vary widely (tiers, thresholds, margin-based). Keep the plan’s rules on its own sheet and reference them, so a change in the plan doesn’t mean rewriting formulas.
Step 4: Products and customers
- Top products: revenue and gross profit per SKU, sorted descending with
=SORTBY(Summary,Summary[Revenue],-1). A product can sell a lot and still earn little; look at both columns. - Repeat customers: in the Customers table, count orders with
=COUNTIFS(Sales[Customer],[@Customer],Sales[Status],"<>Cancelled"), then the share of revenue from customers with more than one order. - Dormant accounts: last order date per customer with
=MAXIFS(Sales[Date],Sales[Customer],[@Customer]), and a flag when it’s older than 90 days. Those are the first calls to make next month; a client tracking setup helps keep that follow-up organized.
Step 5: A one-page dashboard
On a separate sheet, show month-to-date revenue vs target, revenue by month (line chart, this year vs last year), revenue by rep or channel (bar chart), AOV and gross margin trend, and the top 10 products. Keep inputs and charts apart so nobody edits the dashboard by accident. For layout and chart ideas, see the Excel dashboard templates guide.
Keeping the tracker accurate
- Log sales daily or import your invoicing or e-commerce export weekly instead of typing totals.
- Record returns and credit notes as negative rows (or a Returned status) rather than deleting the original sale.
- Reconcile monthly revenue with your accounting or invoice totals.
- Lock formula columns and summary sheets; here’s how to protect formulas in Excel.
Ready-to-invoice sales feed straight into your invoice template, and costs per product link to your inventory spreadsheet.
Common mistakes
- A new tab every month, which makes year-to-date and trend analysis painful.
- Mixing gross and net (before and after discounts, with and without tax).
- Averaging monthly AOVs instead of dividing total revenue by total orders.
- Tracking revenue only and missing margin erosion from discounts.
- Hard-coded targets inside formulas instead of a Targets table.
Related guide: Order Tracking Spreadsheet.
Frequently asked questions
How is a sales tracker different from a CRM?
A CRM manages contacts and deals before they close; a sales tracker records what actually sold. Many small teams use a pipeline sheet for open deals and this tracker for closed sales.
Can I track sales by channel instead of by rep?
Yes. Use the same Rep / Channel column for marketplaces, your website, retail or wholesale, and add channel costs (fees, shipping) to compare profit, not just revenue.
Does this work in Google Sheets?
Yes. SUMIFS, COUNTIFS, XLOOKUP, MAXIFS, UNIQUE, FILTER and SORTBY all work in Google Sheets; replace table references with regular ranges.
More guides are in Online selling & inventory.
