TL;DR: Most guides hand you a generic Excel walkthrough. This one covers what belongs in a sales report, walks through the build step by step, and identifies the exact point where a manual spreadsheet stops scaling and a purpose-built tool takes over.
What a Sales Report Actually Is
A sales report is a structured record of your company's sales data from a specific time period (Unito). It captures what happened, how much closed, and where deals stalled. Think of it as a snapshot, not a live feed.
This distinction matters. A sales dashboard updates in real time and answers "what's happening now." A sales report answers "what happened last week, last month, or last quarter, and what does that pattern mean?" You build one, review it, then act on it.
What a sales report typically contains:
Revenue closed within the reporting window
Number of deals opened, advanced, or lost
Activity volume (calls, demos, proposals sent)
Conversion rates between pipeline stages
If you want real-time metrics, you need a dashboard. If you want to spot trends, hold reps accountable, or brief leadership, you need sales reports. The two complement each other. For a breakdown of what belongs on the live side, see key metrics to include in a sales dashboard.
The next step is choosing which type of report fits your cadence.
Types of Sales Reports and When to Use Each
Not every sales report serves the same purpose, and picking the wrong format wastes the hour you spent building it. Here are the main types of sales reports and when each one earns its place:
Daily sales report. Tracks activity volume: calls made, emails sent, demos booked. Use it when you manage a team of 3+ reps and need to spot effort gaps before they compound into missed targets. A daily sales report works best as a single-tab sheet you update in under five minutes.
Weekly pipeline report. Shows deals by stage, total pipeline value, and movement since last week. Run this every Monday so your forecast stays current and you catch stalled deals before month-end.
Monthly revenue report. Summarizes closed-won revenue, average deal size, and revenue by rep. This is the report your CFO or co-founder actually reads. Pair it with a P&L template in Excel for a complete financial picture.
Funnel conversion report. Measures drop-off between stages (lead to qualified, qualified to proposal, proposal to close). For IT services companies, a healthy qualified-to-close rate typically sits between 20% and 30%. If yours is below that, this report tells you exactly where deals die.
Salesforce notes that sales reports may be used daily, weekly, monthly, quarterly, or annually depending on audience and purpose. The cadence question matters as much as the format. Match the report type to the decision it supports, and you will know which metrics belong in every sales report before touching Excel.
What to Include in a Sales Report
A sales report earns its value from the metrics it contains. Include the wrong ones and you get a spreadsheet people glance at but never act on. Here's what belongs in every report, and why each metric pulls its weight.
Deals created tracks pipeline generation. Without it, you can't tell whether your team's top-of-funnel activity is healthy or coasting on last month's momentum.
Deals closed (won and lost) shows output. Separating wins from losses lets you calculate win rate and spot patterns in why deals stall.
Revenue by rep makes individual contribution visible. It also exposes distribution problems, like one rep carrying 60% of closed revenue while three others split the rest.
Conversion rate by stage is where most reports get specific. If your demo-to-proposal rate drops from 45% to 28% in a single week, you know exactly where to dig. For IT and SaaS sales teams, a healthy opportunity-to-close rate typically sits between 15% and 30%, so anything outside that range deserves investigation.
Average deal size tells you whether you're moving upmarket or discounting your way to quota. A shrinking average with flat revenue means more deals for the same outcome, which costs more rep time.
Lead source connects marketing spend to pipeline. When you know that referrals close at 3x the rate of paid ads, budget conversations get easier.
Customer acquisition cost (CAC) rounds out the picture. As Qobra notes, effective sales reports include customer acquisition costs alongside conversion rates and pipeline metrics so leadership sees the full unit economics.
If you want a deeper breakdown of which metrics belong in every sales report, that guide covers the prioritization logic. For now, these seven fields form the backbone of any sales report template worth reusing. The next section walks through building this structure in Excel, column by column.
How to Build a Sales Report in Excel Step by Step
Open a blank workbook and start with a single flat table. Column A holds the date, B the sales rep name, C the lead source, D the deal stage, E the deal value, and F a closed (yes/no) flag. Every row is one deal. This structure matters because PivotTables need flat, non-merged data to work correctly. Name the range as a Table (Ctrl+T) so new rows automatically feed into your summaries later.
Populate at least two weeks of data before building anything on top. If you already track deals in a CRM or spreadsheet, paste the export here. Clean it first: no blank rows, no merged cells, consistent date formatting (YYYY-MM-DD avoids regional confusion). The columns map directly to the metrics that belong in every sales report, so you are not adding anything extra.
Build the PivotTable.
Select your table, go to Insert > PivotTable, and place it on a new sheet.
Drag "Sales Rep" into Rows, "Deal Value" into Values (set to Sum), and "Date" into Columns grouped by month. You now have revenue by rep by period in under 30 seconds.
Add a second PivotTable on the same sheet with "Deal Stage" in Rows and "Count of Deal Value" in Values to see conversion by stage.
If you use Excel 365 or 2021, dynamic arrays and GETPIVOTDATA pull live numbers into a summary row without manual updates. Earlier versions (2016, 2019) still support PivotTables but require a manual refresh (right-click > Refresh) when source data changes.
For the chart, click inside the revenue PivotTable, then Insert > Clustered Column. One axis shows months, the other shows revenue per rep. Keep it to one chart per sheet.
A second line chart plotting cumulative closed deals over time helps you spot whether the team is ahead or behind pace at a glance.
Save the file as an .xltx (Excel Template).
Next month, open the template, paste fresh data into the source table, refresh PivotTables, and the report rebuilds itself. That is your reusable sales report template without buying extra software.
One caution: once your team exceeds five reps or you need sales reports refreshed daily, maintaining this manually gets expensive in time. That is when manual reporting starts slowing your team down. For now, this Excel setup gives you a working report you control end to end, and it pairs well with a P&L template in the same workbook if you want finance and sales data side by side.
How Often You Should Run Sales Reports
The cadence depends on what decision you're making.
Daily sales reports track activity volume: calls made, emails sent, demos booked. They answer "is my team doing enough work today to hit this week's target?" If a rep logs 12 calls by Tuesday when the norm is 25, you catch it before Friday. Daily reports are lightweight, usually one PivotTable filtered to today's date.
Weekly reports track pipeline movement. Deals that advanced, stalled, or died. This is where you spot a clogged stage or a rep who booked meetings but closed nothing. Weekly cadence gives you enough data to act without drowning in noise.
Monthly reports track revenue and quota attainment. They answer the strategic question: are we on pace for the quarter, and which reps or segments are pulling weight? Monthly is also the right cadence for pairing your sales report with a P&L template in Excel so finance and sales stay aligned.
As Salesforce notes, sales reports "may be used daily, weekly, monthly, quarterly, or annually" depending on audience and purpose. The mistake is running one cadence for every question. Match the report frequency to the decision speed. Revenue decisions move monthly. Activity corrections move daily.
If you find yourself spending more time building these reports than reading them, that's a sign manual reporting is slowing your team down.
Can Sales Reports Help You Spot Trends
A sales report becomes useful the moment you stop treating it as a scorecard and start reading it as a signal detector. When you run the same report weekly with consistent structure, patterns surface on their own: a pipeline stage where conversion dropped from 35% to 22% over three weeks, a rep whose average deal size shrank by 40% after a territory change, or a lead source that quietly stopped producing qualified opportunities last month.
The key is consistency. A one-off report tells you where you are. A repeated report, built the same way each time, tells you where you're heading. Sales reporting reveals patterns in rep activity, buyer engagement, and deal timing that improve predictability.
When you spot a trend, act on it within the same reporting cycle. Reassign leads, adjust outreach cadence, or flag a stage for pipeline review. Knowing which metrics belong in every sales report determines whether your sales reporting dashboard surfaces real problems or buries them in noise.
When Excel Is No Longer the Right Tool
Excel works until your pipeline outgrows one person's discipline. Three friction points show up predictably:
Data entry lag. Reps update the sheet hours or days after a call. By the time you pull a sales report, the numbers reflect last week's reality, not today's.
Version drift. Two people edit the same file, or someone emails "v3_final_FINAL." You lose time reconciling instead of analyzing.
No automatic lead capture. Every new contact requires manual entry, which means leads slip through before they ever reach a row.
When you notice these patterns, you have outgrown a spreadsheet. That is when manual reporting starts slowing your team down.
The step up is a dedicated sales reporting dashboard that updates as deals move. Lio's Report Builder pulls pipeline data automatically, so your sales report reflects the current state without waiting on anyone to paste numbers into a cell. It removes the version problem entirely because there is one live source, not twelve files.
For teams past five reps or fifty active deals, sales reporting tools like this pay back the switching cost within a reporting cycle or two.
Closing
Building a sales report in Excel works well when your team is logging a handful of deals a week. You control the structure, own the data, and can refresh it on your cadence. But once you're running daily reports or managing more than five reps, manual entry creates reporting lag that costs you pipeline visibility. At that point, a purpose-built tool like Lio's Report Builder pulls lead and deal data automatically so your sales report is always current, not a snapshot from last Friday. Start with the Excel template above, run it for a month, then ask yourself: am I spending more time updating this than acting on it?
FAQ
How do I create a sales report in Excel?
Start with a flat table (date, rep, source, stage, deal value, closed flag), populate two weeks of clean data, then build a PivotTable with Sales Rep in Rows and Deal Value in Values. Add a chart, save as a template, and refresh monthly with fresh data.
What should be included in a sales report?
Include deals created, deals closed (won and lost), revenue by rep, conversion rate by stage, average deal size, lead source, and customer acquisition cost. These seven metrics give leadership the full unit economics and show where deals stall.
How often should I generate sales reports?
Match cadence to the decision it supports. Run daily reports for activity tracking, weekly for pipeline movement, monthly for revenue summaries, and quarterly for trend analysis. Most teams start with weekly pipeline reports and monthly revenue reports.
Can sales reports help identify sales trends?
Yes. A funnel conversion report shows drop-off between stages, and comparing month-to-month metrics like average deal size or win rate reveals whether you're moving upmarket or discounting your way to quota.
What is the difference between a sales report and a sales dashboard?
A sales dashboard updates in real time and answers 'what's happening now.' A sales report is a snapshot from a specific period that answers 'what happened and what does that pattern mean,' letting you spot trends and hold reps accountable.
What types of sales reports should a small sales team run?
Start with a weekly pipeline report (deals by stage, total value, movement) and a monthly revenue report (closed-won, average deal size, revenue by rep). Add a daily activity report only if you manage 3+ reps and need to spot effort gaps.
Get tactical playbooks every Tueday
One email. 5-min read. Tactical reads for B2B operators who actually run the business.
Join 48,000+ B2B operators · Unsubscribe anytime
Ashley Carter is a B2B Sales Strategist & Lead Growth Consultant who has spent over a decade helping sales teams turn cold pipelines into consistent revenue engines. With a background in outbound sales and CRM optimization, she writes about smarter lead capture, follow-up systems, and why most businesses are sitting on more opportunities than they realize