Every month the same ritual starts. Someone exports last month's orders, pastes them into a workbook, builds a pivot table, fixes the pivot table because a rep's name was spelled two ways, copies the numbers into a slide and sends it to the sales director. By the time it arrives, half the questions it raises are already out of date, and nobody is sure whether the total matches the accounting system.
A sales report does not have to be rebuilt every month. If the data is laid out properly and the summaries are formulas, you paste in new orders and the report updates itself. This guide shows how to structure the data, which metrics belong in a sales report, the formulas behind the monthly, weekly and daily views, how to track targets by rep and what a good dashboard shows. A worked example ties the numbers together.
Start with the data: one row per order line
Everything depends on this step. A good sales data sheet is a flat table:
- One row per order line, not one per order
- One column per field, with a single header row
- No blank rows, no merged cells, no subtotals mixed in
- Dates stored as real dates, not text
A practical set of columns: Date, Order number, Customer, Sales rep, Product, Quantity, Unit price, Discount, Unit cost. From these you can calculate everything else, either in extra columns or in the reports.
Add calculated columns next to the raw ones:
- Revenue:
=Quantity * UnitPrice * (1 - Discount) - Cost:
=Quantity * UnitCost - Gross margin:
=Revenue - Cost - Month:
=EOMONTH(Date, -1) + 1(the first day of the month, easy to filter on) - Week start (Monday):
=Date - WEEKDAY(Date, 2) + 1 - Region and category: look them up from the customer and product lists
The lookups matter. Spell customers, reps and products exactly as they appear on their master lists, so a sale to "Acme Ltd" is never counted separately from "ACME Ltd.". A check column catches mistakes before they reach a report:
=IF(COUNTIF(CustomerList, C2)=0, "Unknown customer", IF(F2<=0, "Missing quantity", ""))
The metrics a sales report needs
You can measure dozens of things, but most decisions come from five numbers and a few breakdowns:
- Revenue: total sales in the period
- Gross margin: revenue minus cost, and the margin percentage
- Units: how many items were sold
- Orders: how many orders were placed
- Average order value (AOV): revenue divided by orders
Each number needs context to mean anything, so show every one against:
- Last period (last month, last week)
- Same period last year, which removes seasonality
- Target
Then break the period down by sales rep, region, product category, top products and top customers. Those five breakdowns answer almost every follow-up question before anyone asks it.
The formulas behind a monthly report
Put the report month in one cell, as a date such as 1 October 2026. Everything else references it.
Revenue for the month:
=SUMIFS(Revenue, Date, ">=" & ReportMonth, Date, "<=" & EOMONTH(ReportMonth, 0))
Last month and the same month last year: reuse the same formula with a shifted start date:
=SUMIFS(Revenue, Date, ">=" & EDATE(ReportMonth, -1), Date, "<=" & EOMONTH(ReportMonth, -1))
=SUMIFS(Revenue, Date, ">=" & EDATE(ReportMonth, -12), Date, "<=" & EOMONTH(ReportMonth, -12))
Growth: =(Current - Previous) / Previous
Attainment against target: =Actual / Target
Year to date, from the start of your fiscal year (it does not have to be January):
=SUMIFS(Revenue, Date, ">=" & FiscalYearStart, Date, "<=" & EOMONTH(ReportMonth, 0))
Orders need a distinct count, because each order has several lines. In Excel 365:
=COUNTA(UNIQUE(FILTER(OrderNumber, (Date>=Start)*(Date<=End))))
Top 10 products: in Excel 365, sort a summary of revenue by product and take the first ten:
=TAKE(SORTBY(ProductList, ProductRevenue, -1), 10)
In older versions, use LARGE on the revenue column and INDEX with MATCH to find each product's name.
A worked example: one month's report
A fictional kitchenware wholesaler closes October 2026 with 184,500 in revenue from 410 orders. The report compares it like this:
| Measure | October 2026 | Comparison | Result |
|---|---|---|---|
| Revenue | 184,500 | September: 172,000 | +7.3 percent |
| Revenue | 184,500 | October 2025: 161,800 | +14.0 percent |
| Revenue | 184,500 | Target: 180,000 | 102.5 percent attainment |
| Gross margin | 73,800 | Cost: 110,700 | 40.0 percent margin |
| Average order value | 450 | 184,500 / 410 orders | 450.00 |
The month looks good on every line. Now the reps:
| Rep | Revenue | Target | Attainment |
|---|---|---|---|
| Alice | 52,000 | 50,000 | 104.0 percent |
| Ben | 47,500 | 50,000 | 95.0 percent |
| Carla | 44,000 | 40,000 | 110.0 percent |
| Dev | 41,000 | 40,000 | 102.5 percent |
The reps add up to 184,500 and the targets to 180,000, matching the company line. Ben is the only rep below target, and it is easy to see why a team total can hide that.
Then the year to date. Revenue for the year so far is 1,620,000 against a target of 1,700,000, which is 95.3 percent attainment, and up 9.5 percent on the 1,480,000 of the same period last year.
That is the point of reporting several views of the same data. October beat target, yet the year is still 80,000 behind, and growth on last year is healthy. A single total would have told only one of those three stories.
Weekly and daily sales reports
Monthly reports are for reviews. Weekly and daily reports are for management.
Weekly report: show any Monday-to-Sunday week by day and by rep, against the week before. The same SUMIFS, with the date range replaced by the week start and week start plus 6, does it. A small chart of daily revenue shows quickly whether a week was steady or leaned on one big day.
Daily report: list every sale on a chosen date, totals by rep and the month to date against target. The month-to-date figure is useful because it shows pace:
=SUMIFS(Revenue, Date, ">=" & MonthStart, Date, "<=" & ReportDate)
If 18 days of a 31-day month have passed (about 58 percent of it) and revenue is only 55 percent of target, the team is slightly behind pace, early enough to do something about it.
Setting targets by rep
Targets work best when they are set once and phased automatically. Enter an annual target for each rep, then spread it across the months. A flat spread of a 2,160,000 company target gives 180,000 a month. Businesses with a busy season should use a pattern instead, with a weight for each month that adds up to 100 percent, so December's target is higher than February's. Let managers type over any single month when they need to.
Shade each rep's monthly result against their target: green at or above, amber within a tolerance you choose, red below. A 12-month grid of colours shows consistency far better than a table of numbers.
What a good sales dashboard shows
A dashboard should be readable in ten seconds. A reliable layout:
- Top row: revenue this month and year to date against target and last year, gross margin and average order value
- Trend chart: this year against last year by month, with a target line
- Rep leaderboard: revenue and attainment, sorted
- Breakdowns: region and category, as simple bars
- Top customers: a short list
Resist adding more. Every extra chart makes the important ones harder to find.
Mistakes that make sales reports unreliable
- Pasting summaries instead of raw data. You cannot rebuild a different view from totals.
- Inconsistent names. "Acme" and "Acme Ltd" become two customers.
- Dates stored as text, so
SUMIFSsilently ignores them. - Counting lines as orders. Orders need a distinct count.
- No comparison. A number without last period, last year or target is trivia.
- Mixing returns into revenue without a separate treatment.
- Hard-coded months in formulas, which break the next month.
- No reconciliation to accounting. Check the report total against your ledger once a month.
A sales report that builds itself
All of this can be built in a blank workbook, but connecting data, lookups, six reports, targets and a dashboard without a broken reference takes many hours. The Sales Report & Dashboard turns a list of order lines into a complete sales report in Excel.
Paste or type your sales data (date, order number, customer, rep, product, quantity and optionally price, discount and cost) and the region, category, net revenue, margin and week fill in from your lists. A check column flags unknown customers, reps or products and missing quantities. The Monthly Report compares the chosen month with last month, the same month last year, the fiscal year to date and target, then breaks it down by rep, region, category, the top 10 products, the top 10 customers and the five slowest products.
The Weekly Report shows any week by day and by rep, the Daily Report lists every sale on a chosen date with month to date against target, and Rep Performance shades each month green, amber or red against target. The Dashboard shows the month and year at a glance with a chart of this year against last year and the target line. Targets are set once per rep and spread by a season pattern, the fiscal year can start in any month, and it holds 8,000 sales lines, 200 products, 500 customers and 10 reps. It locks the formulas without a password and contains no macros.
If you need something simpler, these templates may be enough:
Common questions
How do I make a sales report in Excel?
Keep one row per order line with the date, customer, rep, product, quantity and price. Then summarise it with SUMIFS by period, rep, region and product, and compare each total with last period, last year and target. A template does the summaries for you once the data is pasted in.
What should a sales report include?
Revenue, gross margin, units, orders and average order value for the period, compared with the previous period, the same period last year and target. Add breakdowns by rep, region, category, product and customer.
How should sales data be laid out in Excel?
As a flat table with one row per order line and one column per field, with no blank rows, subtotals or merged cells. Dates must be real dates, and customer, rep and product names should match their master lists exactly.
How do I compare sales with last year in Excel?
Use SUMIFS with a start date shifted back by 12 months using EDATE, and an end date shifted using EOMONTH. Make sure the data includes last year's sales.
Does a fiscal year have to start in January?
No. Put the first month of the fiscal year in a settings cell and base the year to date formulas on it, along with the target months.
Does it work in Google Sheets?
The workbook is built for Microsoft Excel 2016 or later (desktop or Excel for the web). You can import it into Google Sheets, but sheet protection does not carry over, so the locked formulas are easier to overwrite there by accident.
A report that updates itself leaves time for the part that matters: deciding what to do about the numbers. You can preview the Sales Report & Dashboard free; downloading it is included in the OpenWorksheet monthly plan, along with the rest of the library. All figures in this guide are examples for a fictional business.