How to Create a Sales Dashboard in Google Sheets

Building a sales dashboard in Google Sheets is one of the best ways to get a real-time grip on your business performance without investing in expensive software. This guide will show you how to transform a simple spreadsheet of sales data into a dynamic, visual dashboard. We’ll cover everything from structuring your raw data to building interactive charts that bring your sales metrics to life.

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

Step 1: Get Your Sales Data in Order

A great dashboard starts with clean, well-organized data. Before you can build any charts or summaries, you need a solid foundation. The goal is to have all your raw sales information in a single, tidy table format on one sheet.

Create a "Raw Data" Tab

First, create a new tab in your Google Sheet and name it something like "Raw Data." This sheet will be the single source of truth for your entire dashboard. It’s crucial to keep this data transactional - meaning each row represents a single event, like a sale or a closed deal.

Your data table should include columns for all the key details you want to track. Here’s a common structure for a sales data table:

• Sale ID: A unique identifier for each transaction.
• Date: The date the sale was made.
• Sales Rep: The name of the person who closed the deal.
• Region: The sales region (e.g., North, South, East, West).
• Product/Service: The name of the item sold.
• Units Sold: The quantity sold in the transaction.
• Unit Price: The price of a single unit.
• Total Revenue: A calculated column (Units Sold * Unit Price).

Here’s an example of what your raw data table might look like:

Pro Tip: Consistency is everything. Make sure names and product categories are spelled the same way every time. "Bob C." and "Bob" will be treated as two different people, which will skew your reports.

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: Define Your Key Sales Metrics (KPIs)

A dashboard that tries to show everything ends up showing nothing. Instead of cluttering your dashboard with dozens of charts, focus on a few key performance indicators (KPIs) that give you the clearest picture of your sales health. Your KPIs should directly answer your most important business questions.

Here are some of the most common sales KPIs to track:

• Total Revenue: The most basic measure of performance. How much money are you bringing in?
• Sales Volume: The total number of units sold. This helps you understand demand separately from price.
• Average Deal Size: Calculated as Total Revenue / Number of Deals. This shows if you're closing bigger or smaller deals over time.
• Sales by Rep: Who are your top performers? This helps identify leaders and team members who might need more coaching.
• Revenue by Product/Service: Which offerings are driving the most revenue? This information is vital for inventory, marketing, and product development.
• Sales Over Time (Trend): Are sales growing month-over-month? A trendline helps you visualize your growth trajectory.

You don't need to track all of these, but choose 4-6 that are most meaningful for your team's goals.

Step 3: Summarize Your Data with Pivot Tables

Pivot tables are the engine of your Google Sheets dashboard. They do the heavy lifting of summarizing thousands of rows of raw data into neat, digestible tables. It’s best practice to create a separate tab for them called "Pivot Tables" to keep your workbook organized.

Building Your First Pivot Table: Revenue by Sales Rep

Let's create a summary that shows total revenue for each sales representative.

1. Go to your "Raw Data" tab and click anywhere inside your data table.
2. In the menu, go to Insert > Pivot table.
3. A new window will appear. Make sure the "Data range" covers your entire table (e.g., 'Raw Data'!A1:H1000).
4. Under "Insert to," select "Existing sheet" and choose your "Pivot Tables" tab. Click a cell like A1 to place it there.
5. Click Create.

The Pivot table editor will open on the right. To build your report:

• Under Rows, click "Add" and select "Sales Rep."
• Under Values, click "Add" and select "Total Revenue." It should default to SUM, which is what we want.

You now have a clean table showing the total revenue generated by each sales rep. Repeat this process for each KPI you chose. For example, to see sales over time, you’d use "Date" in the Rows field and "Total Revenue" in the Values field. You can then right-click the dates in the pivot table and group them by month or quarter.

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

Step 4: Create Visuals for Your Dashboard

Now for the fun part: turning those summary tables into charts. Your main dashboard will live on a brand new tab, which you can call "Dashboard." We will build the charts on our "Pivot Tables" sheet and then move them over.

Chart 1: Sales by Rep (Bar Chart)

Bar charts are perfect for comparing performance across categories, like your sales reps.

1. On your "Pivot Tables" sheet, highlight the data in your "Revenue by Sales Rep" pivot table (including the headers).
2. Go to Insert > Chart.
3. Google Sheets will usually default to a good choice, but if not, select "Bar chart" or "Column chart" from the Chart editor on the right.
4. Go to the "Customize" tab in the editor to change the title, colors, and fonts to match your preference.

Chart 2: Revenue Over Time (Line Chart)

Line charts beautifully illustrate trends over time.

1. Highlight the data in your "Revenue by Date" pivot table.
2. Go to Insert > Chart and choose a "Line chart."
3. In the "Customize" tab, you can add a trendline to help visualize your growth trajectory.

Chart 3: Key Number Callouts (Scorecards)

Sometimes, the most important information is simply a single number, like "Total Revenue This Month."

1. On your "Dashboard" tab, pick a cell where you want your KPI to appear.
2. In that cell, enter a simple formula to pull the grand total from a pivot table. For example:
3. Make this cell stand out! Increase the font size dramatically (e.g., to 36pt), make it bold, and center the text. Beneath it, add a simple label like "Total Revenue" in a smaller font size.

Creating these scorecard-style cards for your most important top-line numbers provides an excellent at-a-glance summary.

Step 5: Assemble and Refine Your Sales Dashboard

With your charts and scorecards ready, it’s time to bring them all together into a clean, professional-looking dashboard.

Organizing Your Dashboard Layout

1. Go to each chart you created on the "Pivot Tables" sheet.
2. Click the three vertical dots in the top-right corner of the chart and select "Copy chart."
3. Go to your "Dashboard" tab and paste the chart (Ctrl+V or Cmd+V).
4. Arrange your charts in a logical order. Typically, you want your most important "scorecard" numbers at the top, followed by trend charts and more detailed breakdowns.
5. Resize and align the charts to create a grid-like, organized layout.

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

Making Your Dashboard Interactive with Slicers

Slicers are filters that let you (or your team) dynamically filter all the pivot tables - and the charts connected to them at once. This transforms your static report into an interactive tool.

1. Click on any one of your pivot tables.
2. Go to Data > Add a slicer.
3. A slicer filter box will appear on your sheet. In the sidebar, choose the column you want to filter by - for example, "Region" or "Sales Rep."
4. Copy and paste this slicer onto your main "Dashboard" tab.

Now, when you use the slicer on your dashboard, every chart that's based on those pivot tables will update automatically. You can filter the entire dashboard to view data for just the "North" region or see performance for a single sales rep with just one click.

Final Thoughts

By organizing your raw data, summarizing it with pivot tables, and bringing it to life with charts, slicers, and scorecards, you've now built a powerful sales dashboard in Google Sheets. This tool gives you the critical insights you need to make informed decisions without requiring you to manually rebuild reports every week.

While mastering a Google Sheets dashboard is empowering, we know that as your data grows across other platforms like your CRM, ad accounts, and analytics tools, this manual process can quickly become a bottleneck. We built Graphed to solve this problem. You can connect sources like Salesforce, Shopify, and Google Analytics in seconds and use simple natural language - not formulas - to build real-time, cross-platform dashboards. If you're ready to get instant answers from your sales and marketing data, give it a try.