How to Create a Quarterly Sales Report in Google Sheets with ChatGPT

Building a quarterly sales report can feel like a chore, consuming hours you’d rather spend coaching your team or closing deals. By using ChatGPT with Google Sheets, you can turn this tedious task into a fast, insightful process. This guide walks you through every step, from organizing your raw sales data to creating a dynamic dashboard that your entire team can understand.

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

First, Why Bother with a Quarterly Sales Report?

Before jumping into the how-to, let's briefly touch on the why. A quarterly sales report isn't just about looking back at numbers, it's a strategic tool for looking forward. A well-crafted report helps you:

• Track Performance Against Goals: Are you on track to hit your annual targets? A quarterly review tells you exactly where you stand and how much ground you need to cover.
• Identify Top Performers: See which sales reps and products are driving the most revenue. This information is invaluable for recognition, coaching, and inventory management.
• Spot Trends and Patterns: Did a particular product have a breakout month? Are sales dipping in a specific region? These insights help you double down on what’s working and address problems early.
• Allocate Resources Effectively: Based on performance, you can make smarter decisions about where to invest your sales and marketing efforts for the next quarter.

Essentially, it’s an essential feedback loop for your sales engine. With that in mind, let's get building.

Step 1: Get Your Raw Sales Data Into Google Sheets

Your report is only as good as the data it’s built on. The goal here is to get all of your raw sales transaction data for the quarter into a single, clean tab in Google Sheets. Think of this as your "master" data source.

Most sales data lives in a CRM like Salesforce, HubSpot, or Pipedrive. Nearly all of these platforms have a feature to export your sales or deal data as a CSV file for a specific date range.

Exporting Your Data:

1. Navigate to your CRM's reporting or deals section.
2. Set the date filter to the quarter you want to analyze (e.g., January 1, 2024, to March 31, 2024).
3. Export the view as a CSV file.
4. Open a new Google Sheet, go to File > Import > Upload, and select your CSV file. Choose "Replace current sheet" to import the data cleanly. Rename this tab to something like "Raw Data Q1."

Structuring Your Data for Success

Once imported, make sure your data is organized into clean columns. A typical sales data set should include:

• Date: The date the sale was closed. Make sure this column is formatted as a date (Format > Number > Date).
• Sale ID / Order ID: A unique identifier for each transaction.
• Sales Rep: The name of the salesperson who closed the deal.
• Product/Service Sold: The name of the product or service.
• Quantity: The number of units sold.
• Unit Price: The price per unit.
• Total Sale Value: The total value of the transaction (Quantity * Unit Price).
• Customer Name: The name of the company or individual who purchased.

Pro-Tip: Data consistency is your best friend. Ensure that names are standardized (e.g., "John Smith" not "John S." or "J. Smith") and product names are consistent. A little bit of cleanup now will save you from major formula headaches later.

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: Use ChatGPT to Generate Your Key Formulas

Now for the fun part. Instead of searching for complex Google Sheets formulas, we'll ask ChatGPT to generate them for us. Create a new tab in your Sheet and name it "Report Dashboard." This is where we'll build the actual report and visualize your data.

The key to getting great results from ChatGPT is providing it with clear context. Always specify:

• What you are trying to calculate.
• The name of your data sheet (e.g., 'Raw Data Q1').
• The column letters corresponding to your data.

How to Prompt ChatGPT: A Sales Report Example

Let's imagine our 'Raw Data Q1' sheet has the following structure:

• Column A: Date
• Column C: Sales Rep
• Column D: Product
• Column G: Total Sale Value

KPI #1: Total Quarterly Revenue

This is the simplest, most important metric. We just need to sum the 'Total Sale Value' column.

Your Prompt to ChatGPT:

"I have a Google Sheet with a tab named 'Raw Data Q1'. Sales revenue for each transaction is in Column G. Write me a formula to calculate the total sum of all sales in that column."

ChatGPT's likely response:

Certainly! Here is the Google Sheets formula to sum all values in Column G of your 'Raw Data Q1' tab:

`=SUM('Raw Data Q1'!G:G)`

In your "Report Dashboard" tab, type "Total Revenue" in one cell and paste the formula from ChatGPT next to it. Just like that, you have your headline number.

KPI #2: Sales Performance by Rep

Now, let’s see who our top performers are. We need to create a small table that lists each sales rep and their total sales for the quarter.

Your Prompt to ChatGPT:

"In my 'Raw Data Q1' sheet, Sales Rep names are in Column C and the total sale value for each deal is in Column G.

First, I need a formula to give me a unique list of all sales reps from Column C.

Second, for each unique sales rep, I need a formula to sum their total sales from Column G."

ChatGPT might suggest a UNIQUE and SUMIF formulas. Create two columns in your dashboard: "Sales Rep" and "Total Sales." In the "Sales Rep" column, use the formula ChatGPT provided for the unique list of reps:

`=UNIQUE('Raw Data Q1'!C2:C)`

Then, in the cell next to the first rep's name (let's say their name is 'Anna' and she's in cell B10), paste the SUMIF formula:

`=SUMIF('Raw Data Q1'!C:C, B10, 'Raw Data Q1'!G:G)`

Drag this formula down to apply it to every salesperson. Your dashboard will now show a ranked list of sales rep performance for the quarter.

KPI #3: Top-Selling Products by Revenue

This process is very similar to the sales rep table. We just swap the columns we're analyzing.

Your Prompt to ChatGPT:

"Using the same 'Raw Data Q1' sheet, product names are in Column D and sale values are in Column G. I want to create a table showing the total revenue generated by each product. Can you provide a Google Sheets QUERY formula to do this, sorted from highest revenue to lowest?"

ChatGPT's likely response:

Of course. This QUERY formula will select the product and the sum of its sales, group them by product, and order the results in descending order of sales. Make sure your data in columns D and G starting from row 2.

`=QUERY('Raw Data Q1'!D2:G, "SELECT D, SUM(G) GROUP BY D ORDER BY SUM(G) DESC LABEL SUM(G) 'Total Revenue'")`

Paste this single formula into your dashboard. It will automatically generate a dynamic, sorted table of your top-selling products. This is far more efficient than the manual copy-and-paste method! No dragging needed.

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 3: Visualize Your Data with Charts

Numbers are great, but charts make your data easy to digest in a single glance. ChatGPT can’t create the chart for you, but it can tell you exactly how to approach it.

Chart #1: Bar Chart for Sales Rep Performance

Bar charts are perfect for comparing totals across different categories, like sales reps.

Your Prompt to ChatGPT:

"I have a table in Google Sheets showing Sales Rep names in one column and their Total Sales in the next. What's the best chart type to compare their performance, and how do I create it?"

ChatGPT's recommendation:

"A bar chart or column chart would be ideal for comparing sales performance.

To create it in Google Sheets:

1. Select the data in your Sales Rep and Total Sales table, including the headers.
2. Go to the menu and click Insert > Chart.
3. Google Sheets will likely default to a column chart, which works perfectly. You can change the chart type in the Chart editor on the right if you prefer a horizontal bar chart."

Follow those steps, and you’ll have a clean visual showing who is leading the pack and who might need more support.

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

Chart #2: Line Chart for Sales Over Time

Line charts excel at showing trends. Let’s create one to visualize our sales on a weekly basis.

Your Quick Setup: For this, you need a summary table with two columns: Week and Total Sales. You can ask ChatGPT for another QUERY formula to generate this.

Your QUERY Prompt to ChatGPT:

"On my 'Raw Data Q1' sheet, dates are in Column A and purchase values are in Column G. Write me a QUERY formula for Google Sheets that will sum the purchase values by week and display the result in two columns: the week number and the total sales for that week"

ChatGPT will likely return a similar QUERY formula. Once you have that weekly sales data table, highlight it, and select Insert > Chart. Google Sheets should intelligently recommend a line chart, showing the ebb and flow of your sales throughout the quarter.

Step 4: Assembling Your Final Dashboard

Now, arrange your KPIs and charts neatly on the "Report Dashboard" tab. A good layout often includes:

1. Top-Line KPIs at the Top: Place big numbers like Total Quarterly Revenue prominently at the top.
2. Place Charts and Tables Below: Arrange your Sales by Rep and Top Products by Revenue visuals, alongside your trends charts.
3. Use Clear Titles: Label everything clearly (e.g., "Q1 2024 Sales Performance by Rep"). No one should have to guess what they're looking at.

Congratulations! You've successfully built a powerful, semi-automated quarterly sales report using Google Sheets and ChatGPT. You can now duplicate this template each quarter, swap out the raw data, and all your formulas and charts will update automatically.

Final Thoughts

Using Google Sheets and ChatGPT transforms sales reporting from a data-entry marathon into a quick process of analysis and insight. By organizing your data correctly and crafting clear prompts, you can calculate key metrics and create compelling visualizations without needing to be a spreadsheet expert.

But while this method is a huge improvement, you're still left exporting CSVs, cleaning data, and checking formulas. This is exactly why we built Graphed. We wanted to eliminate the manual steps altogether. Graphed connects directly to your sales data sources like Salesforce and HubSpot, so there’s no exporting necessary. You can just ask a question like, "create a dashboard showing my quarterly sales by rep and top products," and get a live, automated report in seconds - letting you get straight to the insights that grow your business.