Sheet2ChartHow to Build a Sales Dashboard in Excel
A sales dashboard in Excel needs three things a generic dashboard doesn’t: pipeline by stage, win rate, and quota attainment per rep. Here’s how to build each from a standard CRM export (Salesforce, HubSpot, or similar all export in a similar shape: one row per opportunity, with stage, owner, amount, and close date columns).
Set up the source data
Export opportunities with at minimum: Deal ID, Owner (rep), Stage, Amount, Close Date, and Created Date. Paste onto a Data sheet and convert to an Excel Table (Ctrl+T) so PivotTables stay live as you paste in new exports.
If reps have individual quotas, add a second small sheet — Quota — with one row per rep and their target for the period. This doesn’t come from the CRM export; it has to be maintained separately (even a 5-row table works).
Pipeline by stage
Insert a PivotTable from the Data table: Stage in Rows, Amount in Values (set to Sum), and filter out closed-won/closed-lost if you only want open pipeline. Build a PivotChart (bar, horizontal reads better with long stage names) from it — this is the chart most people check first.
Win rate
Win rate isn’t a single PivotTable field — it’s a ratio you calculate from two counts:
Win rate = Closed-Won deals ÷ (Closed-Won + Closed-Lost deals)Build a small PivotTable with Stage in Rows and Count of Deal ID in Values, restricted to Closed Won and Closed Lost only. Reference the two counts in a formula cell on the dashboard: =WonCount/(WonCount+LostCount), formatted as a percentage.
Quota attainment by rep
This is the one PivotTables can’t do alone, because quota lives in a table you maintain yourself, not the CRM export. Use SUMIFS against the Data table to total each rep’s closed-won revenue, then divide by their row in the Quota sheet:
=SUMIFS(Data[Amount], Data[Owner], "Rep Name", Data[Stage], "Closed Won") / VLOOKUP("Rep Name", Quota, 2, FALSE)Lay this out as a small table — one row per rep, attainment as a percentage, with conditional formatting (Home > Conditional Formatting > Color Scales) so reps below target stand out immediately.
Slicers for region and quarter
Add a slicer for Close Date (group it by quarter — right-click a date field in a PivotTable and choose Group) and one for region or team if your export includes it. Connect both slicers to every PivotTable on the sheet via Report Connections, so filtering by Q3 updates pipeline, win rate, and quota attainment together.
Excel vs. a live sales dashboard
| Excel | Sheet2Chart | |
|---|---|---|
| Quota join | Manual SUMIFS/VLOOKUP against a separate table | Handled automatically once quota is a column in the upload |
| Refreshing with a new export | Re-paste data, PivotTables update | Re-upload the file, dashboard rebuilds |
| ”Why did win rate drop this month?” | Filter the PivotTable manually, inspect rows | Ask the question, get the answer with the underlying SQL shown |
Excel handles this well for a single rep-facing sheet reviewed in a weekly call. It gets heavier once several reps or managers need to filter it independently, or the quota join has to be redone every export.
See the sales use case for a version of this that updates from a plain upload instead of manual formulas.
Frequently asked questions
What's the best way to calculate win rate in Excel?
Win rate = closed-won opportunities ÷ (closed-won + closed-lost), excluding anything still open. A PivotTable with Deal Stage in Rows and Count of Deal ID in Values, then a formula referencing the won and lost counts, keeps this accurate as new deals close.
How do I show quota attainment per rep?
Keep a small Quota table (one row per rep, one column for their target) on a separate sheet, then use SUMIFS to total each rep's closed-won revenue and divide by their quota. A PivotTable alone can't do this because quota isn't in the CRM export — it has to be joined in.
Can I build this from a CRM export, or do I need a live connection?
A CSV or Excel export works fine and is what most reps actually have access to. The dashboard just needs re-pasting (or refreshing, if you use Power Query) each time you pull a new export.