Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create a Business Development Dashboard in Excel with ChatGPT

Cody Schneider••9 min read

Manually building a business development dashboard in Excel can feel like a slow, repetitive chore. You know the insights you need are hidden in your CRM data, but getting them into a clean, visual format requires endless formula writing and pivot table wrangling. This is where you can use ChatGPT to act as your expert co-pilot, turning hours of tedious work into a few straightforward prompts. You don't need to be an Excel wizard, you just need to know how to ask the right questions.

GraphedGraphed

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.

Watch Graphed demo video

This tutorial will walk you through the entire process of creating a dynamic business development dashboard in Excel with ChatGPT's help. We'll cover everything from defining your key metrics and preparing your data to using simple prompts to generate complex formulas, summarize information, and even suggest the best visualizations.

First, Define Your Business Development Metrics

Before you touch Excel or ChatGPT, you need to know what you want to measure. A great dashboard doesn't just show data, it answers important questions about your sales process. Start by thinking about the story you want your dashboard to tell.

Organize your metrics into logical categories. Here are some of the most common and effective ones for a biz dev dashboard:

Lead Generation Activity

  • New Leads Generated: The total number of new contacts entering your pipeline.
  • Leads by Source: Where are your best leads coming from? (e.g., Web Form, Cold Outreach, Referral, Event).
  • Marketing Qualified Leads (MQLs): Leads that marketing has deemed ready for sales outreach.
  • Sales Qualified Leads (SQLs): Leads that the sales team has accepted and is actively working.

Outreach and Engagement

  • Calls Made & Emails Sent: A raw count of your team's outreach activity.
  • Meetings Booked: The number of meetings or demos secured.
  • Response Rate: The percentage of emails or calls that get a response.

Sales Pipeline Health

  • Deals in Pipeline: The total number and value of all open opportunities.
  • Pipeline Value by Stage: The total value of deals in each stage of your sales process (e.g., Qualification, Proposal, Negotiation).
  • Sales Velocity: How quickly deals move through your pipeline, from initial contact to close.
  • Sales Cycle Length: The average time it takes to close a deal.

Performance and Revenue

  • Win Rate: The percentage of opportunities that are won. (Closed Won Deals / Total Closed Deals).
  • Monthly/Quarterly Revenue: The total value of deals closed in a given period.
  • Average Deal Size: The average value of a closed-won deal.

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.

Step 2: Prepare Your Data for Excel

ChatGPT is powerful, but it relies on clean, organized data to give you accurate results. The principle of "garbage in, garbage out" is especially true here. Your first step is to export your raw deal and lead data from your CRM (Salesforce, HubSpot, etc.) into a spreadsheet.

Create a single sheet in Excel named something simple like RawData. Format your data as an official Excel Table by selecting any cell in your data and pressing Ctrl + T. This makes formulas and references much easier to manage.

Your data table should have clear, simple headers. Here’s a basic example of what it might look like:

Key principles for clean data:

  • Consistency is King: Make sure stage names like "Closed Won" or sales rep names are spelled the same way every time.
  • Use Proper Data Formats: Dates should be in a date format, and deal values should be in a currency or number format.
  • No Merged Cells: Merged cells are a nightmare for formulas and pivot tables. Avoid them in your raw data sheet at all costs.

Step 3: A Crash Course in Prompting ChatGPT for Excel

Now for the fun part. Instead of searching Google for obscure Excel formulas, you can just ask ChatGPT. The key is to be clear and provide context in your prompt. The more specific you are, the better the answer will be.

A good prompt structure includes:

  1. Your Goal: What are you trying to calculate or create?
  2. The Context: Mention your sheet name (e.g., RawData), your Table name (e.g., Table1), and the column headers involved.
  3. The Desired Output: Do you want a formula, steps for a Pivot Table, or a VBA macro?

Generating Formulas with Natural Language

Let's say you want to calculate the total revenue from deals won. Your old method was probably digging through menus. Your new method is asking a simple question.

Your Prompt to ChatGPT:

"Write an Excel formula to calculate the total value of all deals from my 'RawData' sheet. In that sheet, Column F contains the deal 'Value' and Column G contains the 'Stage'. I only want to sum the values where the stage is 'Closed Won'."

ChatGPT's Likely Response:

Sure, you can use the SUMIF function for this. Place this formula in a cell on your dashboard sheet:

=SUMIF(RawData!G:G, "Closed Won", RawData!F:F)

See how easy that is? You just describe what you need, and ChatGPT provides the exact formula. You can repeat this for Win Rate, Average Deal Size, and any other KPI you need a specific number for.

GraphedGraphed

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.

Watch Graphed demo video

Summarizing Data with Pivot Tables

Beyond single formulas, you'll want to summarize data, for instance, to see how each sales rep is performing. Pivot Tables are perfect for this, but many users find them confusing to set up. ChatGPT can be your guide.

Your Prompt to ChatGPT:

"Give me the step-by-step instructions to create a Pivot Table in a new Excel sheet. I want it to use the data from my table named 'SalesData' on the 'RawData' sheet. It should show the total 'Value' for each 'Sales Rep' and only for deals in the 'Closed Won' stage."

ChatGPT would then give you a clear, actionable list of steps:

  1. Click any cell inside your data table on the 'RawData' sheet.
  2. Go to the 'Insert' tab in the Excel ribbon and click 'PivotTable'.
  3. Excel will automatically select your table and choose 'New Worksheet' as the destination. Click 'OK'.
  4. In the 'PivotTable Fields' panel on the right, drag the 'Sales Rep' field to the 'Rows' area.
  5. Drag the 'Value' field to the 'Values' area. It should default to 'Sum of Value'.
  6. Drag the 'Stage' field to the 'Filters' area. A filter will appear above your Pivot Table.
  7. Click the dropdown on the 'Stage' filter and select 'Closed Won'.

Just like that, you have a perfectly summarized table that would have taken several minutes of clicking and guessing before.

Asking for Chart Suggestions

Don't know whether to use a line chart or a bar chart? Ask your co-pilot. Visualizing data effectively is a skill, and ChatGPT has learned from millions of examples.

Your Prompt to ChatGPT:

"I have data showing the number of new leads we generated each month for the past year. What is the best type of chart to visualize this trend? Also, what's a good chart for comparing the total sales revenue between our 5 different sales reps for this quarter?"

ChatGPT's Likely Response:

  • "For showing new leads over time, a line chart is the best choice. It clearly shows trends, seasonality, and overall growth or decline."
  • "For comparing total sales revenue among reps, a bar chart (or column chart) is ideal. It makes it extremely easy to see who is the top performer and compare the others proportionally."

Now you can create those charts directly from your Pivot Table data, knowing you're using a best practice for data visualization.

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.

Step 4: Assembling Your Business Development Dashboard

With all your formulas calculated and Pivot Tables built on separate sheets, it's time to create your master dashboard view.

  1. Create a New Sheet: Make a blank sheet and name it "Dashboard". This is where your final presentation will live. You can add color to the background, and your company logo to make it feel more official.
  2. Organize with Cards: Use cells with borders and backgrounds to create 'cards' for your main KPIs (Total Revenue, Win Rate, New Leads). In the cell for the value, don't type the number manually. Instead, reference the cell from your calculation sheet (e.g., =Calculations!B2). Now when your data updates, this card will update automatically.
  3. Copy-Paste Your Charts: Go to the sheets where you built your PivotCharts. Don't copy the Pivot Table itself, just the chart. Copy (Ctrl+C) the chart and paste (Ctrl+V) it onto your Dashboard sheet. Arrange them in a way that tells a logical story - perhaps lead generation metrics on the left, pipeline in the middle, and revenue on the right.
  4. Add Slicers for Interactivity: This step turns a static report into a true dashboard. Click on any of your dashboard charts that came from a Pivot Table. Go to the PivotChart Analyze tab and click "Insert Slicer." Choose a field like "Sales Rep" or "Month." Now you have an interactive filter button. Better yet, you can link one slicer to multiple charts by right-clicking it, selecting "Report Connections," and checking a box for every Pivot Table you want it to control. Now users can click on a rep's name and see all the charts update to show just their data.

Limitations to Keep in Mind

Using ChatGPT with Excel is incredibly powerful, but it's not a magic button. Here are a few things to be aware of:

  • It's Not Truly Live Data: The biggest bottleneck is that you still need to manually export your data from your CRM and paste it into the RawData sheet. The dashboard is only as current as your last data export.
  • ChatGPT Can Hallucinate: Always sense-check the formulas and advice ChatGPT gives you. It's usually right, but it can occasionally invent functions that don't exist or misunderstand a complex request.
  • It Struggles with Messy Data: If your initial data export is messy, inconsistent, or huge, ChatGPT will have a hard time giving you reliable instructions or formulas. The cleaning and prep work is still on you.

Final Thoughts

By blending the structured flexibility of Excel with the conversational power of ChatGPT, you can build a genuinely useful business development dashboard in a fraction of the time it used to take. You no longer need to have every formula memorized, you can simply describe your goal in plain English and let AI handle the technical details, freeing you up to focus on the insights.

The biggest lingering challenge in this process is the manual work of constantly pulling and cleaning new data to keep your dashboard relevant. At Graphed, we specifically built our platform to eliminate that step. We connect directly to your data sources like Salesforce and HubSpot, so your dashboards are always live and update in real-time. Instead of telling ChatGPT how to build an Excel chart based on a static CSV file, you can just tell our AI, "Show me a dashboard of our sales pipeline by rep for this quarter," and it builds a live, interactive dashboard for you instantly - no exports or formulas required.

Related Articles