Sales Dashboard in Excel — Free Template & Complete Build Guide (2026)
A professional sales dashboard tells your team exactly where they stand — right now — without digging through a CRM or waiting for a weekly report. This guide walks through building a complete Excel sales dashboard from scratch: KPI tiles for revenue, win rate, and average deal size; a monthly trend chart; regional breakdown; a top-5 rep leaderboard; and pipeline tracking. All with dynamic SUMIFS formulas, no macros, no add-ins required.
TL;DR
Convert your CRM export to an Excel Table named SalesData. Add StartDate and EndDate named cells as controls. Use SUMIFS for revenue, COUNTIFS for win rate, AVERAGEIFS for deal size — all referencing those two cells. Build a monthly summary on a hidden Calcs sheet and insert charts from it. Polish the layout, protect the sheet, hide all raw data. About 75 minutes on a clean export.
| # | Rep | Region | Won | Revenue |
|---|---|---|---|---|
| 🥇 1 | Sarah Chen | EMEA | 18 | £62,400 |
| 🥈 2 | James Okafor | Americas | 15 | £54,750 |
| 🥉 3 | Priya Mehta | APAC | 12 | £48,200 |
| 4 | Tom Wilkes | UK | 11 | £41,600 |
| 5 | Ana Rodriguez | Americas | 9 | £41,480 |
Contents
- What a sales dashboard should show
- Setting up your sales data
- Step 1 — Date filter controls
- Step 2 — Total Revenue KPI tile
- Step 3 — Win Rate KPI tile
- Step 4 — Average Deal Size tile
- Step 5 — Monthly Revenue trend chart
- Step 6 — Revenue by Region breakdown
- Step 7 — Top 5 reps leaderboard
- Step 8 — Pipeline tracker
- Step 9 — Dashboard design polish
- Design principles
- Common mistakes
- FAQ (10 questions)
📥 Free Sales Dashboard Excel Template
DataHub Pro auto-generates your sales KPI dashboard in 60 seconds from your CRM export — no formula work, no setup required.
Get your free dashboard →⚙️ Interactive KPI Formula Selector
Click the KPIs you want to include. Each one reveals its Excel formula and required columns. Then generate your personalised build checklist.
What a Sales Dashboard Should Show
A sales dashboard is a single-screen view of your team’s performance against targets. The critical distinction from a report: a dashboard shows right now status. It is designed to be glanced at in 30 seconds, not read for 30 minutes.
The most common mistake is trying to show too much. A dashboard is not a data dump — it is a highly edited, carefully prioritised view of the metrics that drive decisions today. Every KPI on the dashboard should answer a specific question a sales manager has each morning. If you cannot articulate the question a metric answers, it does not belong on the dashboard.
The 8 essential sales KPIs every dashboard needs:
| # | KPI | What it answers |
|---|---|---|
| 1 | Total Revenue | How much have we closed this period? (Won deals, date-filtered) |
| 2 | Revenue vs Target | Are we on track to hit quota? |
| 3 | Win Rate | What fraction of closed deals did we win? |
| 4 | Average Deal Size | Are we moving up or down market? |
| 5 | Pipeline Value | How much revenue is in progress or at risk? |
| 6 | MoM Revenue Growth | Are we accelerating or slowing? |
| 7 | Revenue by Region / Team | Where is performance concentrated? |
| 8 | Top Performing Reps | Who is driving results and who needs support? |
Who uses the dashboard: the Sales Director needs strategic signals (total revenue, YTD trajectory); the Sales Manager needs team-level visibility (leaderboard, win rate by rep); the Sales Ops analyst needs accuracy signals (data completeness, stage distribution); the VP Revenue needs forward-looking data (weighted pipeline forecast, coverage ratio).
The most important design principle: lead with the number, support with trend. Never bury your headline metric in a table. Total Revenue should be the largest element on the page, top-left, where the eye lands first. The trend arrow and percentage change are secondary context. Build in that order.
Setting Up Your Sales Data
Every formula in this tutorial assumes your data is a flat table — one row per deal. Export from your CRM with these columns at minimum:
| Column | Type | Example |
|---|---|---|
| DealID | Text | DEAL-001 |
| CloseDate | Date | 15/01/2026 |
| SalesRep | Text | Sarah Chen |
| Region | Text | EMEA |
| Product | Text | Enterprise Plan |
| DealValue | Number | 12500 |
| Status | Text | Won / Lost / Open |
| Stage | Text | Proposal / Negotiation / Closed |
| LeadSource | Text | Inbound / Outbound / Referral |
Select your data and press Ctrl+T to create an Excel Table. Check “My table has headers” and rename it to SalesData in the Table Design tab. Named table references like SalesData[DealValue] automatically expand as you add rows — every dashboard formula updates without maintenance.
💡 Pro Tip: Add two control cells on your dashboard sheet — StartDate and EndDate — and name them in Name Manager (Ctrl+F3). Every filter formula in this tutorial uses those names. Changing the date range updates the entire dashboard in one action.
1.Build the Date Filter Controls
The date filter is the foundation of the whole dashboard. Without it, you have a static snapshot that goes stale immediately. With it, one cell change updates every KPI on the page.
On your Dashboard sheet, create a controls section. Name cell B2 StartDate and B3 EndDate in Name Manager (Ctrl+F3 → New). On a hidden Helper sheet, add these pre-calculated period dates:
💡 Pro Tip: Add a Data Validation dropdown in a “Period” cell with the list: This Month,Last Month,This Quarter,YTD,Custom. Use CHOOSE to drive StartDate and EndDate from the selection.
⚠️ Warning: If your CloseDate column contains text-formatted dates (very common in CRM exports), SUMIFS date filters silently return zero. Check by clicking a date cell — if it is left-aligned, it is stored as text. Use Text to Columns → Date format to convert.
2.Total Revenue KPI Tile
Total Revenue counts only closed-won deals within the selected date range. Never open pipeline, never lost deals.
Current period revenue
Prior period revenue (MoM trend)
MoM growth %
For the trend arrow cell: =IF(TrendCell>0, "▲ "&TEXT(TrendCell,"0.0%"), "▼ "&TEXT(ABS(TrendCell),"0.0%")). Apply conditional formatting: green (#22c55e) if >0, red (#ef4444) if <0.
⚠️ Warning: Never include “Open” or “Lost” deals in revenue. This is the single most common error on sales dashboards. Revenue is always realised, closed-won value only.
3.Win Rate KPI Tile
Win rate measures sales process health. Low win rate = qualification or pitch problems. Rising win rate with falling revenue often means reps are cherry-picking smaller deals.
Critical: the denominator uses <>Open to exclude in-progress deals. Win rate is only meaningful when calculated on closed deals (won + lost). Including open deals deflates the rate artificially. Wrap in IFERROR(..., 0) to handle empty periods.
Industry benchmark: B2B SaaS win rate averages 20–30%. Below 20% usually signals a qualification problem; above 40% in enterprise can indicate the team is pursuing only low-ambition deals.
💡 Pro Tip: Wrap the entire formula in IFERROR(..., 0) to handle periods with no closed deals. A division-by-zero error on a polished dashboard looks unprofessional in a board meeting.
4.Average Deal Size Tile
Average Deal Size is one of the most underused KPIs. Teams obsess over win rate while missing six months of quietly declining deal sizes — often the real signal that the team is moving down-market.
AVERAGEIFS returns an error (not zero) if no matching cells exist — hence the IFERROR wrapper. Why it matters: a rising win rate alongside falling average deal size almost always means cherry-picking. Tracking both together surfaces that pattern immediately. Add a sparkline (Insert → Sparklines → Line) showing 6 months of average deal size below the tile number for instant visual trend.
5.Monthly Revenue Trend Chart
The monthly revenue trend is your most important chart. It reveals acceleration or deceleration and shows whether strong KPI tiles are hiding a deteriorating underlying trend.
Build a summary table on a Calcs sheet using EOMONTH for fully dynamic month boundaries:
| Month Start (Col A) | Revenue (Col B) |
|---|---|
| A2: =DATE(YEAR(TODAY()),1,1) | B2: =SUMIFS(SalesData[DealValue], SalesData[Status],"Won", SalesData[CloseDate],">="&A2, SalesData[CloseDate],"<"&A3) |
| A3: =EOMONTH(A2,0)+1 | B3: (drag down from B2) |
| A4–A13: (drag down) | B4–B13: (drag down) |
Select the Month and Revenue columns on your Calcs sheet and go to Insert → Column Chart → 2D Clustered Column. Format bars with a violet-to-pink gradient fill. Remove gridlines, set background to No Fill, add data labels formatted as £0K. For a 3-month moving average trendline: right-click bars → Add Trendline → Moving Average, Period 3, formatted as a dashed violet line at 1.5pt.
💡 Pro Tip: Format month labels on the x-axis by right-clicking the axis → Format Axis → Number → Custom → enter MMM to show three-letter month abbreviations (Jan, Feb, Mar) rather than date serial numbers.
6.Revenue by Region Breakdown
Revenue by region reveals where performance is concentrated and whether you have dangerous over-reliance on a single territory. Build a region summary on your Calcs sheet:
Repeat for each region (EMEA, Americas, APAC, UK). Add a % of Total column: each region revenue divided by total revenue from Step 2, formatted as percentage to one decimal.
Insert a horizontal bar chart (Insert → 2D Bar → Clustered Bar). Horizontal bars are superior to vertical for labelled comparisons — region names are readable without rotation. Sort by value: right-click the vertical axis → Format Axis → check “Categories in reverse order.”
⚠️ Warning: If your Region column has inconsistent values (“EMEA” vs “emea” vs “UK/EMEA”), SUMIFS misses rows silently. Before building the dashboard, use Data → Remove Duplicates on the Region column alone to see all unique values, then standardise with Find & Replace.
7.Top 5 Sales Reps Leaderboard
A rep leaderboard is one of the highest-engagement elements on any sales dashboard. It motivates reps, gives managers an instant view of who needs coaching, and makes every team member aware of where they stand.
Method 1 — Pivot Table (fastest)
Insert a Pivot Table with SalesRep on Rows, sum of DealValue as Values, Status as a filter (set to Won), sorted descending. For top 5 only: right-click a rep label → Filter → Top 10 Items → change to 5. Connect to date slicers via Report Connections.
Method 2 — Dynamic formulas (auto-updates)
Ctrl+Shift+Enter in Excel 2019; regular Enter in Excel 365. Drag down for ranks 1–5. Recover rep name:
Add rank numbers and medal emojis 🥇🥈🥉 for the top 3 using a conditional formula. Apply gold, silver, bronze background fills with conditional formatting.
8.Pipeline Tracker
The pipeline tracker provides the forward-looking view. Revenue tiles tell you what has been won; pipeline tells you what is coming. Healthy pipeline typically covers 3× of quota for the upcoming quarter.
Total pipeline value
Pipeline is not date-filtered — include all open deals regardless of expected close date.
Pipeline by stage
| Stage | Count | Value |
|---|---|---|
| Discovery | =COUNTIFS(SalesData[Status],"Open", SalesData[Stage],"Discovery") | =SUMIFS(SalesData[DealValue],SalesData[Status],"Open",SalesData[Stage],"Discovery") |
| Proposal | =COUNTIFS(SalesData[Status],"Open", SalesData[Stage],"Proposal") | =SUMIFS(SalesData[DealValue],SalesData[Status],"Open",SalesData[Stage],"Proposal") |
| Negotiation | =COUNTIFS(SalesData[Status],"Open", SalesData[Stage],"Negotiation") | =SUMIFS(SalesData[DealValue],SalesData[Status],"Open",SalesData[Stage],"Negotiation") |
💡 Pro Tip: Add a pipeline coverage ratio KPI: =TotalPipeline / (QuarterlyTarget − CurrentRevenue). Below 2× is a red flag; 3× is healthy. This is the single metric that best predicts whether a team will hit its quarterly number.
9.Dashboard Design Polish
The numbers are in place. Now make it look like a professional tool.
Remove all gridlines: View tab → uncheck Gridlines. This single step makes the dashboard look 80% more professional instantly.
Consistent background: select all (Ctrl+A), apply fill colour. Dark: #1a1a2e. Light: white or #f8fafc. Pick one and apply uniformly.
KPI tile styling: thick violet bottom border on each tile (Format Cells → Border → bottom, colour #a855f7, weight 2.25pt). Row heights ~60px for number rows. Column widths for horizontal breathing room.
Typography: KPI numbers 22pt bold white; labels 10pt grey (#94a3b8); trend 11pt conditional green/red; section headers 11pt bold violet.
Header & timestamp:
Protect the sheet: unlock StartDate and EndDate cells (Format Cells → Protection → uncheck Locked), then Review → Protect Sheet with a password. Users can change dates but nothing else. Right-click Calcs and Helper sheet tabs → Hide to conceal raw data.
Sales Dashboard Design Principles
Maximum 8–10 KPIs. More is noise. If you need 15 metrics, build two dashboards: one for the Sales Director (strategic, top 5) and one for the Sales Manager (operational, team detail).
Lead with Total Revenue top-left. The human eye scans top-left first. Your logo is not a KPI — do not put it in the most valuable position on the page.
Consistent colour semantics: green = positive or target met; red = negative or below target; amber = borderline. Never use these colours for decoration.
Avoid pie charts. Humans compare bar lengths more accurately than pie slice angles. Use bar charts for comparisons, line charts for trends. Pie charts rarely belong on a sales dashboard.
No raw data on the dashboard sheet. SalesData belongs on a hidden sheet. Showing raw data alongside KPIs creates a cluttered, unreadable view and exposes sensitive information.
Update cadence: fast-moving teams (daily targets) should refresh daily; most B2B teams with monthly quotas can refresh weekly. Define this explicitly and communicate it — a dashboard users do not trust to be current is quickly ignored.
Common Mistakes in Sales Dashboards
Including open deals in Revenue. Revenue is realised, closed-won value. Always filter Status = “Won.” Mixing pipeline into revenue produces a meaningless, inflated number.
Hardcoded date ranges. Formulas with ">="&"01/01/2026" go stale immediately. Use the dynamic StartDate/EndDate approach from Step 1 from day one.
Not refreshing Pivot Tables. They do not auto-update. Establish a process: refresh before every use, or record a macro that calls ActiveWorkbook.RefreshAll on a button click.
Too many KPIs. A dashboard with 20 tiles is a data page, not a dashboard. If a metric does not change a decision someone makes today, remove it.
Inconsistent number formatting. Decide once: thousands only (£248K) or full numbers (£248,430). Use custom format [>=1000]£0,"K";[<1000]£0 for automatic conversion.
Skip the formula work — build this in 60 seconds
DataHub Pro generates your full sales KPI dashboard automatically from a CRM export. Upload your data, map your columns, and get a complete interactive dashboard with all 8 KPIs, regional breakdowns, and a rep leaderboard. No Excel required.
Try DataHub Pro free →Frequently Asked Questions
What KPIs should a sales dashboard show?
How do I make my Excel sales dashboard update automatically when I add new data?
SalesData). Table ranges expand automatically as you add rows, so all SUMIFS formulas referencing SalesData[DealValue] include new rows without any formula changes. For Pivot Tables, right-click and Refresh, or go to PivotTable Analyze → Refresh All. To make this fully automatic, record a macro that calls ActiveWorkbook.RefreshAll and assign it to a button on the dashboard. If you use Power Query to import data, set it to refresh on file open via Data → Connections → Properties → Refresh data when opening the file.How do I filter my sales dashboard by date range?
StartDate and EndDate — on the dashboard sheet. Name them in Name Manager (Ctrl+F3). All your SUMIFS and COUNTIFS formulas then reference these names directly. For quick period switching, create a helper sheet with pre-calculated dates for This Month, Last Month, This Quarter, and YTD using DATE(YEAR(TODAY()),MONTH(TODAY()),1) and EOMONTH. Add a Data Validation dropdown that drives which period’s dates populate StartDate and EndDate.How do I add a sales target or quota line to my dashboard?
=ActualRevenue/TargetRevenue–1. Format with conditional formatting: green if ≥0%, amber if >–10%, red if <–10%. For the trend chart, add a second data series for the target values. Right-click the target series → Change Series Chart Type → switch to Line. Format as a dashed orange or white line to create a bullet chart style that immediately shows whether you are tracking above or below quota at each point in the year.Can I create a sales pipeline forecast in Excel?
=DealValue * VLOOKUP(Stage, ProbTable, 2, 0). Sum Expected Values by month using SUMIFS on the expected close date. Compare the weighted forecast total vs quarterly target to identify whether you have sufficient pipeline coverage — typically 3× quota is considered healthy.How do I build a sales leaderboard in Excel?
=LARGE(IF(SalesData[Status]="Won",SalesData[DealValue]),ROW(A1)) as an array formula. Recover the rep name with INDEX-MATCH on the returned value. Add rank numbers 1–5, colour the top 3 with gold, silver, and bronze conditional formatting, and connect to a Region slicer so managers can filter by territory.What’s the difference between a sales dashboard and a sales report?
How do I show month-over-month change with arrows in Excel?
=(CurrentRevenue – PriorRevenue) / ABS(PriorRevenue). Then display an arrow with the formatted percentage: =IF(TrendCell>0, "▲ "&TEXT(TrendCell,"0.0%"), "▼ "&TEXT(ABS(TrendCell),"0.0%")). Apply conditional formatting: >0 sets font colour to #22c55e (green), <0 sets it to #ef4444 (red). An alternative is Excel’s built-in icon sets (Conditional Formatting → Icon Sets → 3 Arrows) — lower effort but less customisable within a tight KPI tile layout.How do I share my sales dashboard without exposing raw data?
Should I use Excel or Power BI for my sales dashboard?
Further reading
- DataHub Pro — KPI Dashboard in Excel (build a broader KPI dashboard covering finance and operations).
- DataHub Pro — Variance Analysis in Excel (budget vs actual analysis — the natural extension of Revenue vs Target).
- DataHub Pro — DataHub Pro for Sales Teams (automated sales analytics without Excel).
- DataHub Pro — All Excel Analytics Tutorials.
- DataHub Pro — Cohort Analysis in Excel (track customer retention by acquisition cohort).