How to Create a CRM Dashboard in Google Sheets

Building a powerful sales dashboard doesn't have to mean investing in expensive, all-in-one CRM software. If you're managing your sales process with a spreadsheet, you can create a surprisingly effective and visual CRM dashboard right inside Google Sheets. This article will walk you through setting up your data, building key metrics with formulas, and creating charts to visualize your sales pipeline from start to finish.

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

Why Use Google Sheets for a CRM Dashboard?

Before we build, let's cover why Google Sheets is such a great tool for this, especially for small businesses, startups, or sales teams just getting started. It's an accessible and flexible platform that punches well above its weight class.

• It's Free and Accessible: The most obvious benefit is the cost. Google Sheets is completely free and cloud-based, meaning you and your team can access your dashboard from anywhere without paying for hefty software licenses.
• Completely Customizable: Unlike rigid CRMs that lock you into specific reports, a Google Sheets dashboard is a blank canvas. You can track exactly what you want, name metrics whatever you like, and design the layout to fit your team's unique workflow.
• Ideal for Simpler Sales Processes: If your sales cycle is relatively straightforward and you're not juggling thousands of leads, Google Sheets can handle the load perfectly. It's often overkill to use a massive platform like Salesforce when a well-organized spreadsheet will do the job.

Of course, the primary trade-off is the manual work involved. As you'll see, you're responsible for keeping the data clean and updated. But for many, the control and cost-savings are well worth it.

Step 1: Structure Your Raw Sales Data

A great dashboard is built on a foundation of clean, organized data. The most important rule is to separate your raw data from your dashboard. Create two distinct tabs in your Google Sheet:

1. Raw Data: This is where you will log every single deal. It’s a simple, tabular list that will grow over time. Nothing fancy, no formatting - just data.
2. Dashboard: This is where the magic happens. It will pull information from your "Raw Data" tab to create visuals, summaries, and key metrics.

In your "Raw Data" sheet, set up a simple table with headers that capture the essential information for each deal. This acts as the single source of truth for your entire dashboard.

Essential Data Columns for Your CRM

Here are the columns you should start with. You can always add more later.

• Lead ID: A unique identifier for each deal (e.g., 001, 002).
• Contact Name: The name of your primary contact.
• Company Name: The organization you're engaging with.
• Created Date: The date the lead entered your pipeline.
• Deal Stage: The current status of the deal. Using a consistent set of stages is critical. For example: Prospecting, Qualification, Demo, Proposal, Negotiation, Closed Won, Closed Lost.
• Deal Value: The potential or actual monetary value of the deal.
• Sales Owner: The team member responsible for the deal.
• Close Date: The date the deal was marked as "Closed Won" or "Closed Lost."

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: Calculate Your Key Sales Metrics with Formulas

Now, click over to your "Dashboard" tab. This is where we'll build the reporting engine. You'll be using a few powerful but straightforward formulas to summarize the information from your "Raw Data" sheet.

Metric 1: Total Pipeline Value

This is the total value of all deals that are still in progress. We don't want to include deals that are already won or lost. To calculate this, we'll use the SUMIFS function, which adds up numbers based on multiple criteria.

Find a cell on your dashboard, label it "Total Pipeline Value," and in the cell next to it, enter this formula:

=SUMIFS('Raw Data'!F:F, 'Raw Data'!E:E, "<>Closed Won", 'Raw Data'!E:E, "<>Closed Lost")

How it works:

• 'Raw Data'!F:F tells Sheets to sum the values in the "Deal Value" column.
• 'Raw Data'!E:E, "<>Closed Won" adds the first condition: only include rows where the "Deal Stage" is not equal to "Closed Won."
• 'Raw Data'!E:E, "<>Closed Lost" adds the second condition: only include rows where the "Deal Stage" is also not equal to "Closed Lost."

Metric 2: Number and Value of Deals by Stage

Understanding where your deals are clustered is essential for forecasting. Create a small table on your dashboard with each of your deal stages listed. Then, use COUNTIF and SUMIF to get the count and value for each stage.

Assume your stage name (e.g., "Prospecting") is in cell A5. In B5, you'll count the deals, and in C5, you'll sum their value.

• Formula for Deal Count (e.g., in cell B5):

=COUNTIF('Raw Data'!E:E, A5)

• Formula for Deal Value (e.g., in cell C5):

=SUMIF('Raw Data'!E:E, A5, 'Raw Data'!F:F)

You can drag these formulas down for each stage in your list to populate the table automatically.

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

Metric 3: Overall Win Rate

This calculates the percentage of closed deals that your team wins. It’s a vital indicator of sales effectiveness.

The formula is: (Number of Won Deals) / (Total Number of Closed Deals).

=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 a percentage to make it easy to read.

Metric 4: Average Deal Size

This shows the average value of a successfully closed deal, helping you understand what a typical "win" looks like for your business.

The formula is: (Total Value of Won Deals) / (Number of Won Deals).

=AVERAGEIF('Raw Data'!E:E, "Closed Won", 'Raw Data'!F:F)

The AVERAGEIF function is a simple shortcut that combines these two steps for you.

Step 3: Visualize Your Pipeline with Charts

With your key metrics calculated, it's time to bring them to life with charts. Visualizations make it much easier to spot trends and understand performance at a glance.

Sales Funnel Chart

Use the table you created showing the "value of deals by stage" to create a funnel visualization. A horizontal bar chart works perfectly for this.

1. Highlight the data in your "Deals by Stage" table (including the stage names and their values).
2. Go to the menu and select Insert > Chart.
3. Google Sheets will probably suggest a chart type, but in the Chart editor on the right, select a Bar chart.

This gives you a clear visual representation of your pipeline, showing how value moves through each stage towards closing.

KPI Scorecards

For standalone metrics like "Win Rate," "Total Pipeline Value," and "Average Deal Size," a scorecard chart is a fantastic option. It displays a single, prominent number that's easy to read.

1. Select the single cell containing the metric you want to visualize (e.g., your "Win Rate" calculation).
2. Go to Insert > Chart.
3. Scroll down in the chart type options and select Scorecard chart.

Repeat this to create scorecards for your most important KPIs and arrange them at the top of your dashboard for quick reference.

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

Sales Performance by Owner

To see how different sales reps are performing, a pie or bar chart is effective. The easiest way to organize this data is with a Pivot Table.

1. Click anywhere inside your "Raw Data" tab.
2. Go to Insert > Pivot Table. Choose to place it on a new sheet.
3. In the Pivot Table editor:

• For Rows, add "Sales Owner."
• For Values, add "Deal Value" (summarized by SUM).

This will generate a simple table showing total deal value attributed to each sales rep. From here, you can select that table and insert a Pie chart or Column chart to visualize the contributions of each team member.

Pro Tips for a Better Dashboard

To take your CRM dashboard to the next level and make it more user-friendly, consider these extra steps:

• Use Data Validation: On your "Raw Data" sheet, use Data > Data validation on the "Deal Stage" column. Set the criteria to "List from a range" and select the list of your official stages from your dashboard tab. This creates a dropdown menu, preventing typos and keeping your data consistent.
• Freeze Panes: On both sheets, freeze the top row (View > Freeze > 1 row) so your headers are always visible as you scroll.
• Protect Your Formulas: On the "Dashboard" tab, once your formulas are set, protect the sheet so no one can accidentally type over them. Right-click the tab and choose Protect sheet, setting permissions so only you can edit certain ranges.

Final Thoughts

You now have the complete roadmap to build a fully functional and customized CRM dashboard using only Google Sheets. It's a powerful way to take control of your sales data, visualize your pipeline, and track performance without spending a dime on extra software.

Of course, the process we just walked through is still manual. Keeping the sheet updated, connecting data from your marketing channels (like Google Analytics or your ad platforms), and answering follow-up questions still requires time. We created Graphed for exactly this reason. It automates the process by connecting directly to your sources - HubSpot, Salesforce, ad platforms, and more - and allows you to create live, real-time dashboards just by asking questions. Instead of building SUMIF formulas, you can just ask, "Show me my pipeline value by stage," and get an instantly updated chart, freeing you up to focus on strategy instead of spreadsheets.