How to Create a Business Development Dashboard in Excel
A business development dashboard is the easiest way to see exactly what’s working, what’s not, and where your team should focus its energy. Building one in Excel gives you a surprisingly powerful, customizable tool without needing expensive software. This guide will walk you through, step by step, how to transform a raw spreadsheet of sales data into an interactive and insightful dashboard.
What Exactly is a Business Development Dashboard?
Think of it as a one-page report card for your sales and growth efforts. Instead of digging through endless rows in a CRM or spreadsheet, a business development dashboard uses charts, graphs, and key numbers to give you a live, visual snapshot of your performance. It brings together all of your most important sales metrics in one place, helping you and your team quickly answer critical questions like:
Are we on track to hit our quarterly revenue target?
Which sales rep is closing the most deals?
Where are our best leads coming from?
How long does it take us to close a typical deal?
Is our sales pipeline healthy enough to support future growth?
The goal isn't just to report numbers, but to spot trends, identify bottlenecks, celebrate wins, and make smarter decisions backed by data. A well-designed dashboard takes the guesswork out of strategy and focuses conversations on what truly matters.
Planning Your Excel Dashboard: The Most Important Step
Jumping straight into building charts without a plan is a recipe for a confusing dashboard that no one uses. Before you open Excel, take a few minutes to map out what you want to achieve. This preliminary planning is the difference between a pretty but useless report and a tool that actively drives business growth.
1. Define Your Goals and Audience
Start by asking: "What business decision do I want this dashboard to inform?" Are you trying to improve lead qualification, speed up the sales cycle, or increase the average deal size? Your goal will determine what metrics you need to track.
Also, consider who will be using it. A dashboard for a sales executive should provide a high-level overview of revenue and pipeline health. In contrast, a dashboard for individual sales reps should focus on their specific activity metrics, like calls made, meetings booked, and deals in their personal pipeline.
2. Choose the Right Metrics (KPIs)
With your goals defined, you can select the key performance indicators (KPIs) that best measure progress. It’s easy to get carried away and track dozens of metrics, but the most effective dashboards focus on a handful of vital signs. Group them into two categories:
Leading Indicators (The Activities)
These are the day-to-day activities that fuel your pipeline and lead to results. They give you an early warning if things are going off track.
Number of discovery calls made
Number of emails sent
Meetings or demos booked
Proposals or quotes sent
Number of new contacts added
Lagging Indicators (The Outcomes)
These are the results of your activities. They tell you what happened in the past and confirm if your efforts paid off.
Number of leads generated (by source)
Number of opportunities created
Deal win rate (deals won / total deals)
Total revenue closed
Average deal size
Sales cycle length (average days from first contact to close)
3. Gather and Organize Your Data
Finally, figure out where your data lives. For most teams, this data is in a CRM like Salesforce or HubSpot, but it could also be in a simple Google Sheet or another spreadsheet. The key is to have a single, consistent source of data. You’ll want a raw data table with columns like:
Deal Name
Sales Rep
Deal Stage (e.g., Prospect, Qualified, Proposal, Won, Lost)
Lead Source (e.g., Web Form, Cold Call, Referral)
Creation Date
Close Date
Deal Value
With clean data and a clear plan, you're ready to start building.
How to Build a Business Development Dashboard in Excel
Now for the fun part. We'll break down the technical build into clear, manageable steps. For this tutorial, we’ll assume you have a raw data export from your CRM ready to go.
Step 1: Structure Your Workbook
Organization is everything. An organized workbook is easy to update and troubleshoot. Create three separate tabs in your Excel file:
Data: This is where your raw, unedited data from your CRM will live. Don't do any calculations or formatting here.
Calculations: This tab will house all your PivotTables and analysis. Keeping this separate from the final dashboard keeps it clean and clutter-free.
Dashboard: This is the final, front-facing tab where all your charts and visualizations will be presented.
Step 2: Import Your Data and Convert to a Table
Navigate to your "Data" tab. Paste your raw sales data here. Once it's in the sheet, click anywhere inside your data set and press Ctrl + T (or Cmd + T on Mac). This converts your range into a formal Excel Table.
Why use an Excel Table? It offers two massive advantages:
It's dynamic. When you add new rows of data (e.g., last week's sales numbers), your charts and PivotTables will automatically include the new information upon a refresh. You'll never have to manually adjust data ranges again.
It's structured. Tables allow for easier, more readable formulas if you choose to use them later.
Step 3: Create PivotTables to Summarize Your Data
PivotTables do the heavy lifting for your dashboard. They summarize large amounts of data into concise, usable information. Go to your "Calculations" tab to create them.
Click anywhere in your data Table on the "Data" tab, then go to Insert > PivotTable. Place the PivotTable on your existing "Calculations" worksheet.
Now, build a few PivotTables to power your charts. Here are some examples:
Total Revenue by Rep: Drag "Sales Rep" into the Rows area and "Deal Value" into the Values area. Make sure "Deal Value" is set to Sum and format it as currency.
Deals Won by Month: Drag "Close Date" into the Rows area (Excel will automatically group this by months and years) and "Deal Name" into the Values area. Change the calculation for "Deal Name" to Count.
Leads by Source: Drag "Lead Source" into Rows and a second instance of "Lead Source" into Values to create a count.
Create a separate PivotTable for each metric you want to visualize on your dashboard. Give them plenty of space on your "Calculations" sheet.
Step 4: Design the Dashboard Layout
Go to your "Dashboard" tab. This is your canvas. A few cosmetic tips can make a huge difference:
Go to the View tab and uncheck "Gridlines" for a cleaner, modern look.
Consider using a dark grey or solid blue for the entire background to create an area for your charts.
Leave some "white space" between your charts. A crammed dashboard is hard to read.
Plan out where each element will go. A good practice is to put high-level KPIs (like total revenue or total deals won) at the top, followed by trends and more detailed breakdowns below.
Step 5: Create Your Charts and KPI Cards
It's time to bring your data to life. For each PivotTable you created, you'll now create a corresponding chart.
Building the Charts
Click on one of your PivotTables in the "Calculations" sheet. Go to the PivotTable Analyze tab and click PivotChart. Choose the right chart type for your data:
Column/Bar Chart: Best for comparing categories, like "Revenue by Sales Rep" or "Deals by Stage."
Line Chart: Perfect for showing trends over time, like "Deals Won by Month."
Pie Chart: Use sparingly, but it can work for showing composition, like "Percentage of leads from each source."
After creating a chart, cut it (Ctrl + X) from the "Calculations" sheet and paste it (Ctrl + V) onto your "Dashboard" sheet. Repeat this for all your metrics and arrange them according to your plan.
Building KPI Cards
For standalone numbers like "Total Revenue," charts can be overkill. A simple "KPI card" works best. Create one by inserting a textbox (Insert > Text > Text Box). Click on the text box border, then in the formula bar, type = and click on the specific cell in your PivotTable you want to display (like the grand total for revenue). This dynamically links the text box to the data.
Step 6: Add Interactivity with Slicers
Slicers are filters that turn a static report into an interactive dashboard. You can create slicers to filter your dashboard by Sales Rep, Date Range, Deal Stage, and more.
On your "Dashboard" tab, click on any of your charts. Then go to PivotChart Analyze > Insert Slicer. Choose a field to filter by, like "Sales Rep."
By default, this slicer will only control the chart you selected. To make it control all your charts, right-click the slicer and select "Report Connections." In the pop-up window, check the box for every PivotTable in your workbook. Now, when you click a name in the slicer, your entire dashboard will update to show only that rep's data. This simple step is an absolute game-changer for usability.
Step 7: Refresh Your Data
When you get new sales data, simply paste it into the bottom of your table on the "Data" tab. Then, go to the "Data" tab on the ribbon and click "Refresh All." All of your PivotTables and charts will instantly update with the new information. The tedious Monday morning reporting ritual just turned into a 10-second task.
Final Thoughts
Creating a business development dashboard in Excel puts the power of data analysis directly into your hands. Using a structured approach with Tables and PivotTables, you can build a flexible, interactive tool that helps your team track performance, find opportunities, and make better-informed decisions that drive real growth.
While Excel is a great starting point, this manual setup can still feel limiting, especially as your data sources grow. We built Graphed to remove this friction entirely. Instead of creating PivotTables and manually designing charts, you can connect directly to your CRM, e-commerce platform, and ad accounts, then simply describe the dashboard you want in plain English. Graphed automates the entire process, turning hours of Excel wrangling into a 30-second conversation and giving you a real-time, shareable dashboard that always stays up to date.