How to build a sales pipeline dashboard in a spreadsheet

by

TL;DR

You can build a fully functional sales pipeline dashboard in Google Sheets or Microsoft Excel in two to three hours. The finished dashboard shows deal count and revenue by stage, stage-to-stage conversion rates, and a weighted forecast number. You need a structured deal log, basic spreadsheet skills, and the formulas in this guide.

What You Need Before You Start

  • Google Sheets (free, browser-based) or Microsoft Excel 2019 or later (Microsoft 365 preferred for FILTER and UNIQUE dynamic array functions)
  • A raw deal list with at minimum: Deal Name, Owner, Stage, Deal Value, Close Date, and Probability %
  • At least 20 rows of live or sample data so your charts are readable
  • Your agreed pipeline stages written down ahead of time (for example: Prospecting, Qualified, Demo, Proposal, Negotiation, Closed Won, Closed Lost)
  • Optional: Coefficient free tier to sync HubSpot or Salesforce data straight into Sheets without copy-pasting
  • Optional: a color palette with your brand hex codes so the dashboard looks presentable in a sales meeting

If you are starting from a CSV export out of your CRM, clean it first. Remove blank rows, fix inconsistent stage names (Prospect vs. Prospecting vs. prospect), and make sure your Value column holds numbers rather than text strings like “$5,000”. Our guide on cleaning CRM data exports for reporting covers the fastest way to handle that step.

Step 1: Set Up Your Raw Data Sheet

Open a new workbook and rename the first sheet tab Data. Add these column headers in row 1:

A: Deal ID   B: Deal Name   C: Owner   D: Stage   E: Value   F: Close Date   G: Probability

Format column E as Currency and column G as Percentage. Then turn the entire data range into a named Table. In Excel go to Insert > Table and check “My table has headers.” In Google Sheets, go to Format > Convert to table, or select the range and name it DealData using the Name Box in the top-left corner.

The table structure matters more than it looks. When you add new rows later, your SUMIFS and COUNTIFS formulas pick them up automatically without any range updates. This single habit prevents the most common broken-dashboard scenario that surfaces two weeks after you first build this.

You should now see a formatted header row with filter arrows and at least a few data rows below it.

Step 2: Define Your Stage List and Probability Weights

Add a second sheet tab called Config. In column A, list every pipeline stage in the exact order deals move through them. In column B, add the probability percentage your team assigns to each stage.

Stage Probability
Prospecting 10%
Qualified 25%
Demo Scheduled 40%
Proposal Sent 60%
Negotiation 80%
Closed Won 100%
Closed Lost 0%

Select A2:A8 and name that range StageList using the Name Box.

The reason you manage stages here rather than hard-coding them into thirty formulas is straightforward. When your VP of Sales renames “Proposal Sent” to “Proposal Delivered,” you update one cell instead of hunting through the entire workbook. If you need a starting point for probability numbers, our sales forecasting templates guide covers the research behind typical B2B win rates by stage.

You should now see a clean two-column reference table that everything else will point to.

Step 3: Build the Summary Table With COUNTIFS and SUMIFS

Add a third sheet called Summary. Set up column headers in row 1: Stage in A, Deal Count in B, Total Value in C, Weighted Value in D.

In A2 through A8, reference your stage list directly:

=Config!A2

Copy this down to A8. Now in B2, count how many deals sit at this stage:

=COUNTIFS(Data[Stage], A2)

In C2, sum the deal value for this stage:

=SUMIFS(Data[Value], Data[Stage], A2)

In D2, calculate the weighted value, which multiplies each deal’s value by its probability:

=SUMPRODUCT((Data[Stage]=A2)*Data[Value]*Data[Probability])

Copy B2:D2 down through row 8. Add a totals row at the bottom. Exclude Closed Lost from your pipeline totals so the forecast stays clean, but keep it visible in a separate cell for win-loss tracking.

You should now see a table that updates the moment you add new deals to the Data sheet.

Step 4: Calculate Stage-to-Stage Conversion Rates

Below your summary table, around row 12, add a conversion rate section. Label columns A through D: Stage, Entry Count, Exit to Next Stage, and Conversion Rate.

The conversion rate from Prospecting to Qualified is the Qualified count divided by the Prospecting count:

=B3/B2

Repeat this pattern down for each adjacent stage pair. Skip Closed Lost in the denominator so it does not distort your active funnel.

These numbers tell you exactly where deals are stalling. If 60 deals enter Demo Scheduled but only 20 become Proposal Sent, your demo-to-proposal conversion sits at 33%. That single number focuses your next rep coaching conversation more sharply than any vague “pipeline health” discussion. Format column D as a percentage with one decimal place.

You should now see conversion rates between every active stage pair in the pipeline.

Step 5: Create the Pipeline Funnel Chart

Select the Stage and Deal Count columns from your summary table (A1:B7, excluding Closed Lost and Closed Won for funnel purposes). In Excel go to Insert > Charts > Funnel. In Google Sheets, go to Insert > Chart, then in the Chart editor switch to “Bar chart” and sort it by count descending. Google Sheets has no native funnel chart type, but a sorted horizontal bar communicates the same shape.

Clean up the chart before moving it:

  • Remove the legend (it is redundant with axis labels)
  • Add data labels showing the count on each bar
  • Title it “Active Pipeline by Stage”
  • Set all bars to one brand color, not a rainbow

Paste or link the chart onto a new sheet called Dashboard. Place it in the upper-left block of that sheet.

You should now see a narrowing shape that makes deal drop-off visible at a glance during any pipeline review.

Step 6: Build the Weighted Revenue Forecast Tile

Back on the Summary sheet, create a dedicated forecast cell. Sum the Weighted Value column for active stages only, meaning rows that are not Closed Won or Closed Lost:

=SUMPRODUCT((Config!A2:A8<>"Closed Won")*(Config!A2:A8<>"Closed Lost")*D2:D8)

Adjust the row references to match your layout. Label this cell “Weighted Forecast” and format it as currency in a large, bold font.

Add a second cell pulling your Closed Won total:

=SUMIFS(Data[Value], Data[Stage], "Closed Won")

These two numbers sit side by side on the Dashboard as KPI tiles. Weighted Forecast tells you what your pipeline probability-adjusted says you will close. Closed Won tells you what you have already banked. Managers who track both stop getting surprised at the end of the quarter.

You should now see two revenue summary numbers that refresh every time new data hits the Data sheet.

Step 7: Add a Rep Filter Using Data Validation

On the Dashboard sheet, select cell F1. Create a dropdown there using your list of owners. In Excel: Data > Data Validation > Allow: List > Source: =UNIQUE(Data[Owner]). In Google Sheets: add a helper column on Config using =UNIQUE(Data[Owner]), then point the data validation rule at that helper range.

Add a manual “All” option at the top of the source list so managers can reset the view.

Now update your COUNTIFS on the Summary sheet to respect the filter:

=COUNTIFS(Data[Stage], A2, Data[Owner], IF(Dashboard!$F$1="All", Data[Owner], Dashboard!$F$1))

When F1 says “All,” the IF returns the full Owner column (matching every rep). When a specific rep is selected, it filters to just that person. Apply the same pattern to your SUMIFS formulas.

This turns a static summary into a live one-on-one tool. You can pull it up during a rep coaching session and switch the filter to their name to walk through their specific numbers.

You should now see all charts and tables on Dashboard refresh when you change the dropdown value in F1.

Step 8: Assemble and Polish the Dashboard Tab

On the Dashboard sheet, arrange your elements in three zones:

Top row: three KPI tiles in wide cells. Weighted Forecast, Closed Won MTD, and Total Open Deals. Use a large font (18pt or above), a light gray background, and a thin border. These tiles should be readable from across a conference table.

Middle block: the funnel chart on the left, a compact table showing Stage, Count, and Total Value on the right.

Bottom row: a clustered column chart of Closed Won by rep for the current month, built from a helper SUMIFS table on the Summary sheet filtered by month using MONTH(Data[Close Date])=MONTH(TODAY()).

Additional polish steps:

  • Freeze row 1 so the rep filter stays visible when scrolling (View > Freeze Rows in Sheets, View > Freeze Panes in Excel)
  • Turn off gridlines (View > Show > Gridlines in Excel, View > Gridlines toggle in Sheets)
  • Add a last-updated timestamp in a corner cell:
="Last updated: "&TEXT(NOW(),"DD MMM YYYY")

Note: this formula recalculates every time the file opens, not every time data changes. For a live timestamp tied to actual data updates you need an Apps Script trigger in Sheets or a Power Automate flow in Excel.

You should now see a clean, single-tab dashboard that any stakeholder can read without a tutorial from you.

For a more advanced layout combining multiple data sources, see our guide on building a multi-source KPI dashboard in Google Sheets.

Common Mistakes To Avoid

  • Mixing stage name formats in the raw data. “Qualified” and “qualified” and “QUALIFIED” are three different values to COUNTIFS. Enforce a dropdown in column D of the Data sheet using Data Validation so reps cannot type free text into that column.
  • Hard-coding ranges instead of using table references. SUMIFS(E2:E200, ...) breaks the moment your data exceeds row 200. Named tables like Data[Value] expand automatically as you add rows.
  • Including Closed Lost deals in your pipeline totals. This inflates your weighted forecast and makes the funnel chart misleading. Filter them out of every active pipeline metric and track them separately in a win-loss section.
  • Building the dashboard on the same tab as raw data. One accidental sort or paste on the Data sheet wrecks the dashboard layout sitting next to it. Separate tabs are not optional.
  • Skipping the Config sheet for stage names. When stage names are hard-coded across twenty formulas, a single rename cascades into an afternoon of find-and-replace work that always misses something.
  • Leaving formula cells unprotected. Someone will paste over a SUMIFS cell eventually. Protect the Summary and Dashboard sheets (Review > Protect Sheet in Excel, Data > Protect sheets and ranges in Sheets) while leaving the Data sheet fully editable for the team.

When To Level Up

A spreadsheet pipeline dashboard handles teams of one to eight reps with fewer than a few hundred active deals comfortably. Past those limits, the cracks show up fast.

Formulas start to slow down noticeably on larger datasets. Shared Sheets workbooks create version conflicts when two reps edit simultaneously. Adding new metrics requires manual formula work every time. There is no audit trail when someone deletes a row. And connecting additional data sources beyond the CRM export means more copy-paste maintenance every week.

When you hit those friction points, the next move is a dedicated BI tool rather than a more complex spreadsheet. Power BI connects directly to your CRM and refreshes on a schedule without manual exports. Looker Studio is free and handles multi-source dashboards without formulas. Metabase is a strong open-source option if your deal data lives in a SQL database.

Browse the full comparison of BI and dashboard tools to find the right fit for your team size and budget. The migration from a spreadsheet dashboard to a proper BI layer is easier than most sales managers expect, especially once your data is already structured cleanly from this exercise.

Frequently Asked Questions

Can I connect this dashboard directly to my CRM instead of exporting CSVs?
Yes, if you use Google Sheets. Coefficient (free tier covers HubSpot and Salesforce) and the native HubSpot Sheets add-on can both pull live data into your Data tab on a schedule. Excel users can connect to Salesforce using Power Query under the Data tab, which supports scheduled refresh when the file is stored in SharePoint or OneDrive.

How do I make the dashboard update automatically without manual exports?
In Google Sheets, set up a timed refresh using Coefficient or a custom Apps Script trigger. In Excel with Microsoft 365, a Power Query connection to your CRM or a data warehouse can refresh on an interval as long as the workbook lives in SharePoint. Fully automated refresh without any third-party tool requires some scripting in both platforms.

What if my team uses different probability percentages than the ones I put in Config?
Change the values in the Config sheet Probability column and every SUMPRODUCT formula referencing that column recalculates immediately. You do not need to touch any other formulas. This is exactly why keeping probabilities in Config rather than hard-coding them into formulas is worth the extra setup step.

Can I track deals that moved backward through stages?
Not easily in this single-row-per-deal structure. Tracking stage history requires a separate log sheet where every stage change creates a new timestamped row, which is essentially building a lightweight CRM inside a spreadsheet. At that point, a real CRM or a BI tool with a proper data model is the better answer.

How do I show month-over-month pipeline trend lines?
Take a snapshot of your Summary table at the end of each month by copying the values (Paste Special > Values Only) into a separate History sheet. Then build a line chart from that accumulated data. It adds a two-minute manual step each month but gives you trend visibility without complex date-bucketing formulas.

Bottom Line

Start with a clean Data sheet, a Config tab for stage names and probabilities, and a Summary tab running COUNTIFS and SUMIFS against the named table. Build your funnel chart from the summary, add a weighted forecast formula, and wire in a rep filter using data validation. Then assemble everything on a Dashboard tab with three KPI tiles, your funnel chart, and a monthly closed-won chart by rep. The whole build takes two to three hours the first time. After that, ongoing maintenance is a five-minute data paste before your weekly pipeline review.

When the team grows past eight reps or the formulas start to drag, move to a purpose-built BI tool rather than patching the spreadsheet further. Start comparing your options at /category/bi-tools/.