Most sales teams already have the data. It just lives in the wrong shape. There is a CRM export sitting in one tab, a hand-kept pipeline sheet in another, and a monthly number that someone retypes into a slide every Friday. Nobody can see the full picture in one place, so the picture is whatever the loudest person says it is in the meeting.

A sales dashboard fixes that. It is a single screen that reads from your raw deal records and answers the questions a sales leader actually asks: how much is in the pipeline, where is it stuck, how much have we closed against target, and who is carrying the number. The trick is not fancy charts. The trick is one clean source of truth plus a handful of formulas that recalculate on their own.

This is a complete walkthrough. By the end you will have a dashboard that pulls everything together from a single deals tab, and a set of copy-pasteable formulas you can drop straight in.

What a sales dashboard should show

Before you build anything, decide what goes on the screen. A good sales dashboard is opinionated. It shows the numbers that change a decision and leaves everything else in the raw data. At a minimum, yours should show:

  • Pipeline value by stage: how much open opportunity sits in Discovery, Proposal, Negotiation, and so on. This tells you whether the top of the funnel is healthy.
  • Revenue vs target: total closed-won value this period against the goal, so the team knows if it is ahead or behind.
  • Win rate: the share of decided deals that closed won, not just the count of wins.
  • Average deal size: the mean value of a closed-won deal, which anchors forecasting.
  • Rep leaderboard: closed value and open pipeline per owner, so coaching goes to the right person.
  • New vs closed: deals created versus deals closed in the period, the simplest read on momentum.

Six numbers. If you can answer those at a glance, you have a real dashboard. Everything past this is detail.

Set up your data

The single most important decision is this: one deals tab, one row per deal, and the dashboard never touches it. The dashboard reads from the deals tab. It does not store anything of its own. That separation is what keeps the whole thing honest when the data changes.

Create a tab called Deals with a clean header row. A practical column layout looks like this:

A: Deal
The opportunity name, for example "Acme renewal" or "Bright Labs new logo."
B: Stage
Discovery, Proposal, Negotiation, Closed Won, Closed Lost. Use data validation so it stays consistent.
C: Value
Deal amount as a plain number, no currency symbols mixed into the cell.
D: Owner
The rep who owns the deal. Pull from a dropdown so names match exactly.
E: Close Date
Real date type. The expected close for open deals, the actual close for decided ones.
F: Created Date
When the deal entered the pipeline. Used for new vs closed and aging.

If you export from a CRM, paste into this exact layout and map the columns once. The reason data validation matters so much: every formula below filters on the text in Stage and Owner. One stray "Closed-Won" with a hyphen, or " Negotiation" with a leading space, and your totals quietly go wrong. A dropdown removes that whole class of bug.

Now make a second tab called Dashboard. Every formula from here lives on that tab and points back at Deals.

Build the dashboard

Three building blocks carry most of a sales dashboard: a sum filtered by some condition, a count filtered by some condition, and a grouped roll-up. SUMIFS, COUNTIFS, and QUERY cover all three. Here is each one in context.

Pipeline by stage

For pipeline value, you want the total open value sitting in each stage. List your stage names down a column on the dashboard, say in A2:A4 (Discovery, Proposal, Negotiation), then sum the matching deal values with SUMIFS. Because the stage label is in the cell next to each total, you can write one formula and fill it down.

Pipeline value by stage (SUMIFS) // In B2, with the stage name in A2. Fill down for each stage. =SUMIFS(Deals!$C:$C, Deals!$B:$B, $A2) // Total open pipeline (everything not yet decided): =SUMIFS(Deals!$C:$C, Deals!$B:$B, "<>Closed Won", Deals!$B:$B, "<>Closed Lost") // Weighted pipeline, if you keep a probability % in column G: =SUMPRODUCT( (Deals!$B$2:$B<>"Closed Won") * (Deals!$B$2:$B<>"Closed Lost") * Deals!$C$2:$C * Deals!$G$2:$G)

The first formula is your core pipeline-by-stage figure. The second gives you total open pipeline using two "not equal" criteria, which is cleaner than listing every open stage by hand. The third is optional: if you track a probability per stage, SUMPRODUCT gives a weighted pipeline that forecasters love.

Win rate and conversion

Win rate is the number that gets reported wrong most often. It is not wins divided by all deals. It is wins divided by decided deals, the ones that actually closed one way or the other. Open deals are not in the denominator because they have not been decided yet. Count each side with COUNTIFS and wrap the division in IFERROR so an empty pipeline shows a clean zero instead of #DIV/0!.

Win rate and conversion (COUNTIFS + IFERROR) // Won deals: =COUNTIFS(Deals!$B:$B, "Closed Won") // Win rate = won / (won + lost). Format the cell as a percent. =IFERROR( COUNTIFS(Deals!$B:$B, "Closed Won") / ( COUNTIFS(Deals!$B:$B, "Closed Won") + COUNTIFS(Deals!$B:$B, "Closed Lost") ), 0) // Stage-to-stage conversion, e.g. Proposal -> Won this quarter: =IFERROR( COUNTIFS(Deals!$B:$B, "Closed Won", Deals!$E:$E, ">="&DATE(2026,4,1)) / COUNTIFS(Deals!$B:$B, "Proposal"), 0)

The middle formula is the one to put on the dashboard. Format the cell as a percentage and it reads "32%" rather than "0.32". The third shows how the same pattern handles a date filter: chain extra criteria pairs onto COUNTIFS to scope a number to this quarter, this month, or one rep.

Revenue trend and leaderboard

A single closed-won total is useful, but a trend tells the story. Build a small monthly table, then drop a SPARKLINE beside it so the shape of the year sits in one cell. First, total closed-won revenue per month with SUMIFS across a date window, then feed that range to SPARKLINE.

Monthly revenue trend (SUMIFS + SPARKLINE) // In B2, with the first of the month in A2 (A2=DATE(2026,1,1), fill down): =SUMIFS(Deals!$C:$C, Deals!$B:$B, "Closed Won", Deals!$E:$E, ">="&A2, Deals!$E:$E, "<"&EDATE(A2,1)) // Once B2:B13 holds 12 monthly totals, one cell draws the trend: =SPARKLINE(B2:B13, {"charttype","column"; "color","#0F9D58"; "empty","zero"})

That gives you closed revenue for each month and a tiny inline column chart of the whole year. If you would rather have the monthly table build itself instead of filling dates by hand, QUERY can group and total in one formula:

Revenue by month, self-building (QUERY) =QUERY(Deals!A:F, "SELECT YEAR(E), MONTH(E)+1, SUM(C) WHERE B = 'Closed Won' GROUP BY YEAR(E), MONTH(E)+1 ORDER BY YEAR(E), MONTH(E)+1 LABEL YEAR(E) 'Year', MONTH(E)+1 'Month', SUM(C) 'Revenue'", 1)

For the rep leaderboard, the same QUERY pattern groups by owner instead of by month. This one ranks reps by closed-won revenue and shows how many deals each one won, sorted from top performer down:

Rep leaderboard (QUERY) =QUERY(Deals!A:F, "SELECT D, SUM(C), COUNT(A) WHERE B = 'Closed Won' GROUP BY D ORDER BY SUM(C) DESC LABEL D 'Rep', SUM(C) 'Closed Revenue', COUNT(A) 'Deals Won'", 1)

One formula, and the leaderboard re-sorts itself every time a deal flips to Closed Won. Average deal size drops out of the same data: divide total closed-won value by the count of closed-won deals, again wrapped in IFERROR.

Make it useful day to day

A dashboard you only look at once a quarter is a report. To make it a tool, add a few touches that surface problems on their own.

  • Flag stalled deals with conditional formatting. On the Deals tab, add a custom-formula rule that highlights any open row whose created date is more than 30 days old. Use =AND($B2<>"Closed Won", $B2<>"Closed Lost", TODAY()-$F2>30) as the rule. Stuck deals turn red without anyone hunting for them.
  • Sort by value or close date so the biggest or most urgent deals sit at the top. Keep the dashboard formulas pointing at whole columns so sorting the source never breaks them.
  • Use filter views, not filters. A regular filter changes the sheet for everyone live. A filter view (Data, then Create filter view) lets each rep slice to their own deals without disturbing the shared screen.

These three habits turn a static grid into something the team actually opens on a Monday.

Keep it live

The whole design pays off here. Because every dashboard number is a formula reading from Deals, the dashboard updates the instant the deals tab changes. There is no refresh button to remember and no "let me rebuild the report" step. Update a stage, and pipeline, win rate, revenue, and the leaderboard all move together.

Two things make it stay that way:

  • Share read-only. Give the wider team Viewer access to the dashboard and Editor access only on the deals tab to the people who own deals. Viewers always see the latest numbers and cannot accidentally overwrite a formula.
  • Optional scheduled email. If you want the weekly number to land in inboxes on its own, a short Apps Script can read the dashboard and email a summary on a time trigger. It is a few lines: open the spreadsheet, grab the key cells, and call GmailApp.sendEmail, then set a weekly trigger under Triggers. That gets you a Monday-morning recap without anyone exporting a thing.

That is the entire system: one deals tab, one dashboard tab, SUMIFS for pipeline, COUNTIFS for win rate, QUERY for trend and leaderboard, conditional formatting to flag the stuck deals, and read-only sharing to keep it honest.

If you would rather start from a finished layout, grab our free client report dashboard template and point it at your deals tab. The same KPI-card and trend structure works for sales out of the box. If you also need a place to keep the deals themselves, our CRM template gives you the clean, validated deals tab these formulas expect.

A sales dashboard is not a chart problem. It is a single-source-of-truth problem. Get the deals tab clean, and the screen mostly builds itself.