Small Business Sales Tracker Excel: Build and Check It

Quick Answer: A small business sales tracker in Excel is a single table with one row per sale (date, customer, product, quantity, amount, payment method) and a few PivotTables on top of it that total sales by month, product and customer. Once a month, compare what the tracker says you were paid with the deposits on your bank and card processor statements, so the analysis rests on money that actually arrived.

A sales tracker is not your bookkeeping. Its job is to answer questions about what sells, to whom and when, which a ledger organized by account cannot do easily. Built well, it is one data table that you only ever add rows to, and every report is a PivotTable that refreshes from it.

What should a sales tracker tell you?

Decide the questions first, because they set the columns. Most small businesses want:

• Revenue by month, and this month against the same month last year
• Best and worst products by revenue and by units
• Top customers, and customers who bought last year but not this year
• How customers pay (card, transfer, cash, invoice), which affects fees and timing
• Average order value, and whether it is moving

If a question needs a field, the field goes in the table. If no question needs it, leave it out; extra columns are where data entry gets sloppy.

The Sales table, column by column

1. Date — the sale date, not the date the money arrived.
2. Order or invoice number — unique; your link back to the receipt.
3. Customer — consistent names. A drop-down from a Customers list prevents "J. Ortiz" and "Jana Ortiz" splitting one customer in two.
4. Product or service — also from a list.
5. Quantity and Unit price.
6. Amount — =[@Quantity]*[@[Unit price]], before sales tax.
7. Sales tax — kept separate so revenue reports exclude it.
8. Payment method — Card, ACH, Check, Cash, Invoice.
9. Paid date — blank until payment arrives; used for the deposit check.
10. Month — =TEXT([@Date],"yyyy-mm"), handy for grouping and filters.

To make it a table, select the data and use Insert > Table, or Home > Format as Table as Microsoft's table guide describes. Tables extend formulas to new rows by themselves and let PivotTables pick up new data on refresh. For the customer and product drop-downs, Data > Data Validation with Allow set to List points each column at its master list.

Three PivotTables that answer most questions

Microsoft's steps to create a PivotTable: select a cell in the data, choose Insert > PivotTable, pick New Worksheet, and click OK; then tick fields in the PivotTable Fields pane or drag them between Rows, Columns and Values.

Monthly revenue by product. Rows: Product. Columns: Month. Values: Sum of Amount. Add a grand total column and you have the product mix over the year at a glance.

Customer ranking. Rows: Customer. Values: Sum of Amount, Count of Order number. Sort descending by amount. Add a second Values field showing Amount as a percentage of the grand total to see how concentrated sales are. If one customer is 40% of revenue, that is a business risk worth knowing.

Payment mix. Rows: Payment method. Values: Sum of Amount. Filter by Month. This feeds the deposit check below and shows how much of revenue carries card fees.

Refresh each PivotTable after adding rows; they do not update on their own when the table grows. For more PivotTable patterns on transaction data, importing bank statements into Excel PivotTables works through grouping by date and category.

Average order value and year-over-year

Average order value is a calculated cell, not a PivotTable: =SUM(Sales[Amount])/COUNTA(Sales[Order]). For a month, wrap each part in SUMIFS/COUNTIFS on the Month column. For year-over-year, a PivotTable with Month in rows grouped by year and month, and Years as columns, lines up March against March.

Finding customers who stopped buying

A list of lapsed customers is often worth more than any revenue chart, because winning back a past buyer is usually cheaper than finding a new one. On the Customers sheet, next to each name, add two cells:

• Last purchase: =MAXIFS(Sales[Date],Sales[Customer],[@Name])
• Lifetime spend: =SUMIFS(Sales[Amount],Sales[Customer],[@Name])

Then a status column: =IF(TODAY()-[@[Last purchase]]>180,"Lapsed","Active"), with the 180-day window adjusted to your normal buying cycle. A bakery's wholesale accounts might lapse after 30 days; a furniture maker's customers might reasonably go two years between orders. Sort lapsed customers by lifetime spend, highest first, and you have a call list ranked by what each relationship used to be worth. Microsoft lists MAXIFS for Excel 2019 and later; on older versions a PivotTable with Max of Date per customer gives the same answer.

Does the tracker match your deposits?

This is where a sales tracker earns trust. Recorded sales and the money in your bank account differ for predictable reasons, and a monthly check makes sure every difference is one of them.

Take one month. From the tracker, sum Amount plus Sales tax for rows with a Paid date in that month, grouped by payment method. From the outside records:

• Card sales: your card processor's payout report or statement, which lists gross sales, fees, refunds and the net amount deposited.
• Transfers, checks, cash: the bank statement deposits. If the statements are PDFs, QuickBankConvert turns them into rows you can filter for credits only.

Worked example for April:

Two findings: the tracker was missing a refund row (add it as a negative sale so product and customer totals are right), and one payment was logged as April when it arrived in May (fix the Paid date). The fees are not a tracker problem; they belong in your expense records. If your bookkeeping lives in a small business spreadsheet for income and expenses, the net deposits are what its money-in register records, while the tracker keeps the gross sale for analysis.

Timing differences at month end are normal: card payouts often land a day or more after the sale. Note them and confirm they cleared the next month.

Habits that keep the data clean

• Enter sales daily or weekly, never "when there is time". A quarter's backlog becomes guesswork.
• Record refunds and credits as their own negative rows referencing the original order. Never edit the original row.
• Keep prices on a Products list and look them up, so a price change does not require retyping.
• Freeze the header row and keep the table on its own sheet, with reports on other sheets.
• Once a year, start a new table for the new year in the same workbook and keep last year's for comparisons.

Linking sales to stock

If you sell physical products, the tracker's Product and Quantity columns are exactly what an inventory sheet needs for its outgoing movements. A monthly PivotTable of quantity by product can be pasted into the movement log of an inventory spreadsheet for small business, so stock on hand and sales history come from the same source.

Related guides

• Small business spreadsheet for income and expenses
• Import bank statements into Excel PivotTables
• Inventory spreadsheet for small business
• Marketing budget template, to set spend against what the tracker shows each channel brings in