How to Create a Sales Dashboard in Google Sheets with ChatGPT

Creating a functional sales dashboard shouldn't feel like a major project that requires battling with complex software. While dedicated BI tools are powerful, you can build an incredibly useful dashboard right inside a Google Sheet - especially when you have an AI assistant like ChatGPT to help with the heavy lifting. This guide walks you through how to structure your sales data and use ChatGPT to generate the exact formulas and charts you need to visualize your team’s performance.

Setting the Stage: How to Structure Your Sales Data

Before you can build a dashboard, you need clean, well-organized data. The principle of "garbage in, garbage out" is especially true here. ChatGPT can write flawless formulas, but if your data is a mess, the formulas won't work. The goal is to set up a raw data tab that acts as the single source of truth for your entire dashboard.

Your data should be in a simple, flat-table format. This means each row is a single record (like a deal or a transaction) and each column is a specific attribute of that record. Avoid merged cells, fancy formatting, or multiple tables on the same sheet. Keep it clean and simple.

Here’s a great starting structure for a raw sales data tab, which you can name "SalesData":

• Deal ID: A unique identifier for each deal.

• Close Date: The date the deal was won or lost. Use a consistent format (e.g., MM/DD/YYYY).

• Sales Rep: The name of the person who owns the deal.

• Region: The geographic area (e.g., North America, EMEA).

• Product/Service: The item sold.

• Lead Source: How the lead was generated (e.g., Organic, Paid Social, Referral).

• Deal Size: The value of the deal in dollars.

• Status: The current state of the deal (e.g., Won, Lost, Open).

Your sheet should look something like this:

With this clean data source, you’re ready to let ChatGPT do the work.

Using ChatGPT as Your Formula-Writing Assistant

This is where the process becomes much faster. Instead of trying to remember the exact syntax for SUMIFS, VLOOKUP, or the powerful but intimidating QUERY function, you can simply describe what you want to calculate in plain English.

Create a new tab in your Google Sheet and call it "Dashboard." This is where your charts and summary data will live. We'll ask ChatGPT to write formulas that pull data from your "SalesData" sheet and summarize it here.

Let's walk through a few common examples.

Calculating Key Performance Indicators (KPIs)

KPIs are the big, at-a-glance numbers that tell you the overall health of your sales. Let's start with Total Revenue.

1. Total Revenue

Your Prompt to ChatGPT:

I have a Google Sheet with a tab named 'SalesData'. The data is in columns A through H. 'Deal Size' is in column G and 'Status' is in column H. Write me a Google Sheets formula to calculate the sum of 'Deal Size' for all deals where the 'Status' is 'Won'.

ChatGPT's Likely Response:

=SUMIF(SalesData!H:H, "Won", SalesData!G:G)

Just copy this formula, paste it into a cell on your "Dashboard" tab, and it will instantly calculate your total revenue. Format the cell as currency, and you have your first KPI.

2. Average Deal Size

Your Prompt to ChatGPT:

Using the same sheet, write a formula to calculate the average 'Deal Size' (column G) for deals with the status 'Won' (column H).

ChatGPT's Likely Response:

=AVERAGEIF(SalesData!H:H, "Won", SalesData!G:G)

3. Win Rate

This one is slightly more complex as it involves two calculations.

Your Prompt to ChatGPT:

Okay, I need a Google Sheets formula to calculate my win rate. On the 'SalesData' tab, 'Status' is in column H. The formula should count the number of cells that contain 'Won' and divide it by the total count of cells that contain either 'Won' or 'Lost'.

ChatGPT's Likely Response:

=COUNTIF(SalesData!H:H, "Won") / (COUNTIF(SalesData!H:H, "Won") + COUNTIF(SalesData!H:H, "Lost"))

Format this cell as a percentage, and you have your key conversion metric.

Creating Summary Tables for Charts

KPIs are great, but for a dashboard, you need to see trends and comparisons. This is where the QUERY function shines. It’s like running SQL inside your Google Sheet, and it’s a perfect task to outsource to ChatGPT.

1. Revenue by Sales Rep

Your Prompt to ChatGPT:

Write me a Google Sheets QUERY formula for my 'SalesData' tab (data in A:H). It should create a two-column summary table showing sales rep names (column C) and the total revenue (sum of column G) for each rep. Only include deals where the status (column H) is 'Won'. The table should be sorted by total revenue in descending order.

ChatGPT's Likely Response:

=QUERY(SalesData!A:H, "SELECT C, SUM(G) WHERE H = 'Won' GROUP BY C ORDER BY SUM(G) DESC LABEL SUM(G) 'Total Revenue'", 1)

When you paste this single formula into one cell on your dashboard, it will generate a dynamic, multi-row table that updates automatically as you add new data to the "SalesData" sheet.

2. Monthly Revenue Trend

Your Prompt to ChatGPT:

Now write a QUERY formula that summarizes my revenue by month. 'Close Date' is in column B and 'Deal Size' (revenue) is in column G. Only include 'Won' deals from column H. The result should be a table with two columns: the first for the month and the second for the total monthly revenue.

ChatGPT's Likely Response:

=QUERY(SalesData!A:H, "SELECT MONTH(B)+1, SUM(G) WHERE H = 'Won' GROUP BY MONTH(B)+1 ORDER BY MONTH(B)+1 LABEL MONTH(B)+1 'Month', SUM(G) 'Monthly Revenue'", 1)

This formula generates a simple table showing the revenue for each month, ready to be turned into a line chart.

From Raw Numbers to Visual Insights: Building Dashboard Charts

Now that you have your summary tables, creating the charts is easy. Google Sheets makes this part straightforward.

1. Sales Leaderboard (Bar Chart)

Using the "Revenue by Sales Rep" table you created with the QUERY formula:

1. Select the entire table (both the Rep names and their revenue figures).

2. Go to Insert > Chart.

3. Google Sheets will likely default to a bar chart or column chart, which is perfect for this.

4. Customize the title to "Revenue by Sales Rep" and adjust colors as needed.

You now have a clear visualization of your top performers.

2. Revenue Trend (Line Chart)

Using the "Monthly Revenue Trend" table:

1. Select the two columns of data (Month and Monthly Revenue).

2. Go to Insert > Chart.

3. Choose a line chart to best show the trend over time.

4. Title it "Monthly Revenue Trend."

3. KPI Scorecards

For your main KPIs like Total Revenue and Win Rate, you can use the Scorecard chart type.

1. Select the single cell containing your Total Revenue calculation.

2. Go to Insert > Chart.

3. Under Chart type, scroll down and find "Scorecard."

4. This creates a large, easy-to-read display of your most important number. Repeat this for Average Deal Size and Win Rate.

Arrange these charts, scorecards, and tables on your "Dashboard" tab to create a clean, organized, and shareable report.

Tips for Better ChatGPT Prompts

The quality of your output depends on the quality of your prompt. Here are a few tips to get the best formulas from ChatGPT:

• Be Hyper-Specific: Don't say "summarize my sales." Say "sum the values in column G for rows where column H is 'Won' and group the results by the names in column C."

• Provide Context: Always state your tab name (e.g., 'SalesData') and the specific columns for each metric (e.g., "column C contains sales rep names"). This prevents errors.

• Ask for a Specific Function: If you know you want a QUERY, ask for it. This helps steer the AI toward the most efficient solution.

• Iterate and Refine: If a formula returns an error, copy the error message back to ChatGPT and say, "This formula returned an error: [paste error here]. Can you fix it?" The AI is surprisingly good at debugging its own code.

The Limitations: What to Watch Out For

While this method is powerful, it's not a perfect replacement for a dedicated business intelligence tool. Here's what to keep in mind:

• Manual Data Updates: The biggest drawback is that the data isn't live. Your dashboard is only as current as the data in your "SalesData" tab. You'll need to manually export CSVs from your CRM (like Salesforce or HubSpot) and paste them into the sheet on a regular basis.

• Guesswork and Errors: ChatGPT doesn't actually understand your business. It's a text-generation tool that is brilliant at syntax. You still need to sanity-check the numbers it produces to make sure the logic is sound.

• Performance on Large Datasets: Google Sheets can start to slow down significantly if your "SalesData" tab has tens of thousands of rows. If your business scales, this solution may become less practical.

Final Thoughts

By pairing the organizational power of Google Sheets with the formula-writing intelligence of ChatGPT, you can build a highly effective sales dashboard without spending weeks learning a new tool or months waiting for a data team. You get the insights you need by describing them in plain English, which empowers you to focus on what the numbers mean instead of how to calculate them.

We built Graphed because we wanted to solve the remaining friction in this process - the manual data refreshing and the copy-pasting. We give you a way to connect your data sources like Salesforce, HubSpot, or Shopify directly and then use natural language not just for formulas, but to build entire live-updating dashboards automatically. It's the next step beyond using AI as an assistant to having an AI-powered data analyst on your team.