Graphed LogoGraphed
Login

How to Create a Business Development Dashboard in Google Sheets

Cody Schneider•

Tracking your business development efforts can feel like you're trying to assemble a puzzle in the dark. You know all the pieces are there - emails sent, calls made, meetings booked - but seeing the full picture of your pipeline is a constant challenge. This guide will walk you through building a simple, powerful business development dashboard right in Google Sheets, giving you the clarity needed to track progress and close more deals.

First, Why a Business Development Dashboard?

A business development dashboard is a visual report that tracks your most important outreach, pipeline, and sales metrics in one place. It centralizes information, helping you quickly spot bottlenecks, identify what’s working, and keep your entire team aligned on key objectives. Instead of digging through CRM records or scattered notes to understand your progress, you get a clean, at-a-glance view of your pipeline's health.

The main benefits are:

  • Visibility: See your entire pipeline from initial outreach to a closed deal.

  • Accountability: Track individual and team performance against targets.

  • Better Decision-Making: Spot trends and problems early, allowing you to adjust your strategy before it's too late.

  • Motivation: Visualizing progress towards goals is a powerful way to keep the team focused and motivated.

Choosing Your Key Business Development Metrics

The first step in building any good dashboard is deciding what to measure. If you track too many things, the dashboard becomes noisy and useless. If you track too few, you'll miss important context. A good starting point is to select 5-7 core metrics across different stages of the business development cycle.

Think about your metrics in three categories:

1. Activity Metrics (The Work You're Doing)

These are leading indicators that measure the volume of outreach. They help you understand if your team is putting in the effort required to fill the pipeline.

  • Outreach Volume: Emails Sent, Calls Made, LinkedIn Connections Sent per week/month.

  • Meetings Booked: The number of first meetings or discovery calls scheduled. This is often the most important activity metric, as it's the first real sign of a prospect's interest.

2. Pipeline Metrics (The Health of Your Pipeline)

These metrics track the progression and value of opportunities as they move through your sales process.

  • New Opportunities Created: The number of new, qualified leads entering your pipeline.

  • Pipeline Value: The total potential dollar value of all open opportunities.

  • Conversion Rate: The percentage of opportunities that move from one stage to the next (e.g., Meeting Booked to Proposal Sent). The most-watched one is usually the overall win rate (Closed-Won / Total Closed Deals).

  • Sales Cycle Length: The average time it takes for an opportunity to move from creation to close.

3. Outcome Metrics (The Results)

These are lagging indicators that measure the ultimate success of your efforts.

  • Deals Closed-Won: The total number of deals won in a given period.

  • Total Revenue Won: The total contract value of all deals won.

  • Average Deal Size: The average revenue per closed-won deal (Total Revenue Won / Deals Closed-Won).

For your first Google Sheets dashboard, let’s focus on tracking these key metrics: New Opportunities, Pipeline Value by Stage, Deals Closed-Won (by Rep), and Total Revenue Won (by Month).

Setting Up Your Google Sheet for Success

Organization is everything in Google Sheets. A messy structure will lead to broken formulas and headaches. The best practice is to separate your raw data from your dashboard visualization.

Step 1: Create Two Tabs

Start with a new Google Sheet and create two tabs at the bottom:

  1. Raw Data: This is where you will log every single business development opportunity. Think of it as your single source of truth.

  2. Dashboard: This is where your charts and summary tables will live. This tab will pull all its information from the "Raw Data" tab.

Step 2: Structure Your "Raw Data" Tab

In the "Raw Data" tab, set up columns to capture the essential information for each opportunity. Your column headers in row 1 should look something like this:

  • A: Opportunity ID (A unique number for each deal)

  • B: Company Name

  • C: Contact Name

  • D: Create Date (When the opportunity was first identified)

  • E: Stage (The current stage of the deal)

  • F: Value ($) (The potential deal size)

  • G: Owner (The name of the business development rep)

  • H: Close Date (When the deal was won or lost)

Pro-Tip: To ensure data consistency, use data validation for the "Stage" and "Owner" columns. Select the entire "Stage" column (E), go to Data > Data validation, and create a rule with a dropdown list of your stages: Lead, Contacted, Meeting Scheduled, Proposal Sent, Negotiation, Closed-Won, Closed-Lost. Do the same for the "Owner" column with a list of your team members' names. This prevents typos that can break your formulas.

Your "Raw Data" tab should start to look like this as you add opportunities:

Building the Dashboard: Formulas and Charts

Now for the fun part. Navigate to your "Dashboard" tab. This is where we'll use formulas to summarize your data and charts to visualize it. It's helpful to organize a small area on your dashboard for calculations that will feed your charts.

1. Create Your KPI Scorecards

First, let's create simple scorecards for your top-level metrics. In cells A1, A2, A3, and A4, type the labels: Total Open Pipeline ($), Number of Open Deals, Win Rate (All Time), and New Leads (Last 30 Days).

In the adjacent cells (B1, B2, B3, B4), you can use the following formulas. Note that 'Raw Data'!F:F refers to the 'Value' column in your 'Raw Data' tab.

  • In B1 (Total Open Pipeline $):=SUMIFS('Raw Data'!F:F,'Raw Data'!E:E,"<>Closed-Won",'Raw Data'!E:E,"<>Closed-Lost")

  • In B2 (Number of Open Deals):=COUNTIFS('Raw Data'!E:E,"<>Closed-Won",'Raw Data'!E:E,"<>Closed-Lost")

  • In B3 (Win Rate):=COUNTIF('Raw Data'!E:E, "Closed-Won") / (COUNTIF('Raw Data'!E:E, "Closed-Won") + COUNTIF('Raw Data'!E:E, "Closed-Lost"))Format this cell as Percentage.

  • In B4 (New Leads - Last 30 Days):=COUNTIF('Raw Data'!D:D,">"&TODAY()-30)

Format these cells to look big and clear - they're your headline numbers!

2. Visualize Your Pipeline by Stage

Knowing how many deals are in each stage helps you spot bottlenecks. A funnel or bar chart is perfect for this.

Somewhere on your dashboard tab (e.g., in cells D2:E7), create a small summary table:

  • List your stages in column D: Lead, Contacted, Meeting Scheduled, Proposal Sent, Negotiation.

  • In column E, use COUNTIF formulas to count the deals in each stage:

    • E2 (next to "Lead"): =COUNTIF('Raw Data'!E:E, "Lead")

    • E3 (next to "Contacted"): =COUNTIF('Raw Data'!E:E, "Contacted")

    • E4, E5, etc., repeat for each stage.

Now, create the chart:

  • Select the range D2:E7.

  • Go to Insert > Chart.

  • In the Chart editor, select a Column chart or Funnel chart.

  • Title it "Open Pipeline by Stage."

3. Track Revenue Over Time with a Pivot Table

A line chart showing revenue won over time is essential for tracking growth. The easiest way to summarize monthly data is with a pivot table.

  • Go to your "Raw Data" tab and select all your data (Ctrl+A or Cmd+A).

  • Click Insert > Pivot table.

  • Choose "Existing sheet" and select a blank spot on your "Dashboard" tab (e.g., cell G1). Click "Create."

  • In the Pivot table editor:

    • Add 'Close Date' to Rows, right-click on a date and choose Create pivot date group > Year-Month.

    • Add 'Value ($)' to Values (sum).

    • Add 'Stage' to Filters, filter to show only "Closed-Won" deals.

Create a line chart from this pivot table to visualize monthly revenue.

4. Analyze Performance by Rep

Understanding who on your team is closing deals helps in coaching. Use a bar chart.

Create a small table (e.g., D10:E12):

  • List your reps in column D.

  • In E10, use:

    =COUNTIFS('Raw Data'!G:G,"Jane Doe",'Raw Data'!E:E,"Closed-Won")

  • Repeat for each rep, changing the name.

Select this table, insert a bar chart, and title it "Deals Won by Rep."

Tips for an Effective Dashboard

  • Keep it Simple: Focus on key metrics that matter most.

  • Use Clear Visuals: Titles, labels, and consistent colors improve readability.

  • Establish a Routine: Update your "Raw Data" regularly for accurate insights.

  • Review and Discuss: Use the dashboard in meetings to track progress and address bottlenecks.

Final Thoughts

Building a business development dashboard in Google Sheets puts you in control of your data without requiring expensive software. By tracking your activities and outcomes consistently, you turn raw data into a clear roadmap, empowering you to make smarter decisions and grow your business more effectively.

While Google Sheets is fantastic for getting started, you'll eventually notice the time spent on manual data entry and formula maintenance adds up. That's where we designed Graphed to help. We automate this whole process by connecting directly to your data sources like a CRM (HubSpot, Salesforce) or ad platforms. Instead of building pivot tables and writing SUMIFS, you can just ask in plain English: "Show me a dashboard of our sales pipeline by stage this quarter." We create the real-time, interactive dashboards for you, so you can spend less time wrangling spreadsheets and more time acting on insights.