How to Create a Business Development Dashboard in Google Sheets with ChatGPT

Building a powerful business development dashboard might sound like a task for a data analyst, but you can create one right inside Google Sheets without knowing a single complex formula. By pairing the familiarity of a spreadsheet with the intelligence of ChatGPT, you can build a reporting system that turns raw sales data into actionable insights. This article will guide you through the entire process, step by step.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Makes a Business Development Dashboard So Important?

Before jumping into the setup, it helps to understand why this is worth your time. A good dashboard moves your business development efforts from being driven by gut feelings to being guided by data. It's the difference between guessing what's working and knowing exactly what's driving results.

A well-structured dashboard allows you to:

• Track Progress in Real-Time: See instantly how many leads are in the pipeline, how many meetings are booked, and how close you are to hitting your revenue goals.
• Identify Bottlenecks: If you're getting lots of meetings but not sending many proposals, your dashboard will make that gap obvious. It shows you exactly where deals are stalling.
• Understand Your Sales Cycle: Measure how long it takes to move a lead from initial contact to a closed deal, helping you forecast future revenue more accurately.
• Optimize Your Efforts: By tracking lead sources, you can see which channels (e.g., LinkedIn, referrals, cold outreach) are delivering the most valuable opportunities and double down on what works.

Step 1: Laying the Groundwork - Defining Your Key Metrics

A dashboard is only as useful as the metrics it tracks. Start by defining the Key Performance Indicators (KPIs) that matter most for your business development process. Pouring every possible piece of data into your dashboard will only create noise, so focus on the numbers that directly reflect the health of your sales pipeline.

Key Metrics to Track on Your Biz Dev Dashboard

• Leads Generated: The total number of new opportunities entering your pipeline. It's useful to segment this by source (e.g., Referral, Organic, Paid Ad, etc.).
• Meetings Booked: A critical early-stage milestone that shows a lead is engaged and qualified.
• Proposals Sent: The number of formal offers you've presented to potential clients.
• Deals Closed (Won/Lost): The final outcome. Tracking both wins and losses helps you understand your win rate.
• Total Deal Value (Won): The revenue you've successfully brought in.
• Average Deal Size: Calculated by dividing your total deal value by the number of deals won.
• Sales Cycle Length: The average time it takes from first contact to a closed-won deal.
• Conversion Rates: These are the percentages that show how efficiently leads move through your funnel (e.g., Lead-to-Meeting Rate, Proposal-to-Win Rate).

You don't need to track all of these from day one. Pick 3-5 that feel most important to you right now and build from there.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 2: Structuring Your Data in Google Sheets

The cardinal rule of any data analysis is: garbage in, garbage out. A clean, organized data source is essential for your dashboard to work properly. To achieve this, we'll create a Google Sheet with two distinct tabs:

1. Activity Tracker: This is where you'll manually input all your raw data for every lead.
2. Dashboard: This will be your visual summary, pulling data from the "Activity Tracker" tab and displaying it in an easy-to-read format.

In your Activity Tracker tab, create the following columns. Each row will represent a single lead or opportunity.

• Lead ID: A unique identifier (e.g., 001, 002).
• Company Name: The name of the potential client.
• Contact Name: Your main point of contact at the company.
• Lead Source: Where the lead came from (LinkedIn, Referral, Website, etc.).
• Date of First Contact: When you first reached out or they came to you.
• Status: The current stage of the lead. Use a drop-down menu for consistency (e.g., Lead, Contacted, Meeting Booked, Proposal Sent, Won, Lost).
• Deal Value ($): The potential or final value of the deal.
• Projected Close Date: Your estimate of when the deal might close.
• Actual Close Date: The date the deal was marked as Won or Lost.
• Owner: The team member responsible for the opportunity.

Pro Tip: Select the "Status" column, go to Data > Data validation, and create a drop-down list of your predefined statuses. This prevents typos and keeps your data consistent, which is crucial for accurate formulas.

Step 3: Using ChatGPT to Generate Your Formulas

Here's where the magic happens. You don't need to memorize dozens of spreadsheet functions. Instead, you can simply ask ChatGPT to write them for you. The key is to be clear and specific in your prompts.

Navigate to your blank Dashboard tab. This is where you will build your summary KPIs. Let's create a few examples.

Example 1: Counting Total Leads

You want a simple card on your dashboard that shows the total number of leads you're tracking.

Your Prompt to ChatGPT:

"I have a Google Sheet with a tab named 'Activity Tracker'. Every row in this tab is a lead. Write me a formula to count the total number of leads by counting all the non-empty cells in the 'Lead ID' column (Column A)."

ChatGPT's Likely Response:

=COUNTA('Activity Tracker'!A2:A)

Simply copy this formula, paste it into a cell on your "Dashboard" tab, and it will automatically count every lead you've added to the tracker.

Example 2: Calculating Your Proposal-to-Win Rate

Now, let's try something more advanced. You want to know what percentage of proposals you send result in a win.

Your Prompt to ChatGPT:

"In my 'Activity Tracker' tab, Column F contains the 'Status' of each deal. Some statuses are 'Proposal Sent', and others are 'Won'. I need a Google Sheets formula that calculates my win rate based on proposals. It should divide the total count of deals with a 'Won' status by the combined total of 'Won' AND 'Proposal Sent' deals."

ChatGPT's Likely Response:

=COUNTIF('Activity Tracker'!F:F, "Won") / (COUNTIF('Activity Tracker'!F:F, "Won") + COUNTIF('Activity Tracker'!F:F, "Proposal Sent"))

Paste this into a cell on your dashboard and format the cell as a percentage to see your conversion rate.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Example 3: Summing the Value of Won Deals This Month

What if you want to track how much revenue you've closed in the current month?

Your Prompt to ChatGPT:

"I need a Google Sheets formula that sums values from the 'Deal Value ($)' column (Column G) on my 'Activity Tracker' tab. It should only sum the values IF two conditions are met: the 'Status' in Column F is 'Won' AND the 'Actual Close Date' in Column I is in the current month."

ChatGPT's Likely Response:

=SUMIFS('Activity Tracker'!G:G, 'Activity Tracker'!F:F, "Won", 'Activity Tracker'!I:I, ">="&EOMONTH(TODAY(),-1)+1, 'Activity Tracker'!I:I, "<="&EOMONTH(TODAY(),0))

ChatGPT breaks down a complex task into a formula you can use instantly. You can repeat this process for every single KPI you decided to track.

Tips for Effective ChatGPT Prompting

• Be Hyper-Specific: Always mention the tab name ('Activity Tracker') and the exact column names or letters (Column F, 'Status').
• State Your Goal Clearly: Start with what you want to achieve ("I want to calculate...", "I need to count...", "I want to sum...").
• Copy, Paste, and Verify: Carefully copy the formula and paste it into your sheet. Make sure the column ranges (A2:A, F:F, etc.) match your data layout perfectly. AIs can sometimes make assumptions, so a quick check is always a good idea.

Step 4: Building the Visual Dashboard

With your formulas calculating the KPIs on your dashboard tab, it's time to visualize the data. Charts and graphs make trends and patterns much easier to spot than a grid of numbers.

A good dashboard often consists of KPI "cards" at the top (your main numbers from Step 3) followed by a few key charts.

Creating a "Leads by Source" Bar Chart

A bar chart is perfect for comparing different categories, like your lead sources.

1. Create a Summary Table: First, you need a small table that aggregates the data. You can ask ChatGPT for help with this, too.

Prompt: "In a new area on my 'Dashboard' tab, show me how to create a summary table that lists all unique lead sources from 'Activity Tracker' Column D and counts how many times each one appears."

ChatGPT will likely suggest a combination of UNIQUE and COUNTIF formulas to build this table for you.

1. Insert the Chart: Once your summary table is ready (e.g., showing "LinkedIn: 15", "Referral: 10"), highlight the entire table.
2. Format: Go to Insert > Chart. Google Sheets will probably default to a pie chart, but you can easily change it to a column or bar chart in the Chart Editor on the right.

You can use this same process to create other useful visuals, like a line chart showing deals won over time or a pie chart illustrating the distribution of your current pipeline statuses (Lead vs. Meeting Booked vs. Proposal Sent).

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 5: Bringing It All Together and Maintaining Your Dashboard

Now, arrange your KPI cards and charts on the 'Dashboard' tab in a way that is clean and logical. You can use cell merging and background colors to group related metrics and make the whole thing look more polished and professional.

The most important part of making this dashboard successful is constancy. Set aside five minutes at the end of each day to update your "Activity Tracker" tab with any new leads or status changes. Because all your dashboard's formulas and charts are connected to this raw data tab, your entire dashboard will update automatically the moment you add new information.

Final Thoughts

Building a custom business development dashboard in Google Sheets is no longer a complex or code-heavy task. By clearly structuring your raw data and leveraging ChatGPT to instantly generate the necessary formulas, you can get a powerful, data-driven view of your sales pipeline without any prior spreadsheet expertise.

Of course, the final hurdle for many teams is the manual data entry required to keep a spreadsheet up-to-date. That consistent upkeep is precisely why we created Graphed. We wanted a way to skip the spreadsheets and tedious data-wrangling entirely. By connecting directly to your CRM, ad platforms, and other data sources, we let you ask questions in plain English - like "Show me a dashboard of my sales pipeline from HubSpot this quarter" - and then we build the live, auto-updating dashboard for you in seconds.