How to Create a Sales Report in Google Sheets with ChatGPT

Building a sales report in Google Sheets often feels like a frustrating exercise in remembering obscure formulas or searching for the right combination of functions. Today, that changes. By pairing the natural language power of ChatGPT with the flexibility of Google Sheets, you can generate complex formulas, structure clean reports, and visualize your sales data faster than ever before. This article will guide you step-by-step through the process of creating a powerful sales report with your new AI assistant.

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

Good Reports Start with Good Data

Before you write a single prompt, the most important step is ensuring your sales data is clean and structured properly. ChatGPT is powerful, but it's not a mind reader. It works best with data that is organized in a simple, tabular format, just like a well-organized spreadsheet.

For your AI assistant to understand your request, your raw data should have a few key characteristics:

• Consistent Column Headers: Each column needs a clear, simple header in the first row (e.g., "Date," "Sales Rep," "Region," "Product SKU," "Units Sold," "Sale Amount").
• One Row Per Record: Every individual sale or transaction should occupy its own row. Don't merge cells or combine multiple sales into a single entry.
• No Gaps or Empty Rows: Your data should be a continuous block. Avoid blank rows separating your sales records.
• Clean Data Types: Make sure your dates are formatted as dates, and your numerical values (like revenue or units sold) are formatted as numbers.

Your source data tab in Google Sheets might look something like this:

Once your data is tidy, you're ready to start building your report.

Building Your Sales Report with ChatGPT: Step-by-Step

Let’s create a new tab in your Google Sheet and name it "Sales Report." This is where we'll place the tables and charts generated with ChatGPT’s help. We'll start with simple questions and progressively build a more insightful report.

Step 1: Define Your Key Metrics

First, decide what you want to measure. Clear goals lead to clear prompts. Good starting questions for a sales report are:

• What is our total revenue for the period?
• Who are our top-performing sales reps?
• Which products are selling the most?
• What is our monthly revenue trend?

Let's use ChatGPT to answer these one by one.

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: Start with Basic Summary Formulas

Let's find the total revenue. If your raw data is in a tab named 'SalesData' and revenue is in column F, your prompt to ChatGPT would be:

I have a Google Sheet with a tab named 'SalesData'. Column F contains revenue from individual sales. Write a formula for my 'Sales Report' tab to calculate the sum of all values in column F of the 'SalesData' tab.

ChatGPT will likely respond with:

=SUM(SalesData!F2:F)

This simple formula correctly pulls data from the other tab and calculates the total. Now let's ask for the average sale size.

Great. Now give me the formula to calculate the average sale amount from the same column.

And you will get:

=AVERAGE(SalesData!F2:F)

Copy these formulas into your "Sales Report" tab. In a few seconds, you've already created summary key performance indicators (KPIs) that would otherwise require you to remember function names and syntax.

Step 3: Create Summary Tables with the QUERY Function

The QUERY function is arguably one of the most powerful - and intimidating - functions in Google Sheets. It uses a language similar to SQL to pull, filter, group, and organize data. This is where ChatGPT becomes a game-changer.

Finding Top Sales Reps

To produce a table showing total revenue for each sales representative, you can use the following prompt. Notice how we specify all the relevant details for context:

In my 'SalesData' tab, column B has the Sales Rep name and column F has the Revenue. In my 'Sales Report' tab, write a Google Sheets QUERY formula that shows the total revenue for each sales rep. The output should have two columns: Sales Rep and Total Revenue. Please sort the results to show the top-performing rep first.

ChatGPT will generate a powerful formula like this:

=QUERY(SalesData!B2:F, "SELECT B, SUM(F) WHERE B IS NOT NULL GROUP BY B ORDER BY SUM(F) DESC LABEL SUM(F) 'Total Revenue'", 0)

When you paste that single formula into a cell in your report tab, it generates a complete, sorted table of your sales reps and their total revenue. No pivot tables or manual sorting needed.

Breaking Down Sales by Product

Let's do the same for our product sales analysis using column D ("Product") and F ("Revenue"):

Write a similar QUERY formula to show total revenue by Product (column D). Sort it from highest to lowest.

And you'll get another perfect formula:

=QUERY(SalesData!D2:F, "SELECT D, SUM(F) WHERE D IS NOT NULL GROUP BY D ORDER BY SUM(F) DESC LABEL SUM(F) 'Total Revenue'", 0)

In just two prompts, you've created summary tables that form the foundation of an effective sales report.

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: Structuring Data for Visualization

ChatGPT can't directly create a chart in Google Sheets yet, but it can perfectly structure the data and give you precise instructions on how to create the visual yourself.

Let's create a monthly revenue trend chart. First, we need to ask ChatGPT to generate a summary table of revenue per month.

My 'SalesData' tab has a Date in column A and Revenue in column F. I need to create a table that sums the total revenue for each month. The first column should be the month and the second column should be the total revenue.

ChatGPT might output this QUERY:

=QUERY(SalesData!A2:F, "SELECT MONTH(A), SUM(F) WHERE A IS NOT NULL GROUP BY MONTH(A) LABEL MONTH(A) 'Month', SUM(F) 'Total Revenue'", 0)

Note: The formula above might show the month as a number (e.g., 9 for October). To make it more readable, you can refine your prompt or adjust the formula's formatting. An even better prompt would ask for specific formatting:

Can you adjust the formula to show the month name (e.g., 'October 2023') instead of just a number?

After a bit of iteration, you'll have a data table ready for visualization. Now, you can simply ask ChatGPT for the next steps:

I now have a two-column table in Google Sheets showing Month and Total Revenue. What are the exact steps to create a line chart from this data in Google Sheets?

ChatGPT will provide a clear set of instructions, such as:

1. Highlight the data range for your months and revenue.
2. Go to the "Insert" menu in the Google Sheets toolbar.
3. Select "Chart."
4. In the Chart editor that appears on the right, select "Line chart" as the chart type.
5. Use the "Customize" tab in the editor to add a chart title, axis labels, and change colors.

By following these instructions, you can quickly turn your summary table into a professional-looking chart to visualize your sales trends over time.

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

Tips for Writing Effective ChatGPT Prompts

How you ask matters. The quality of your prompts directly impacts the quality of the formulas and instructions you receive. Here are some best practices:

• Provide Detailed Context: Always mention you are working in Google Sheets and specify tab names and column letters or headers (e.g., "'SalesData' tab, column F ('Revenue')").
• Be Explicit About Your Goal: Instead of "make a report," say "write a QUERY formula to sum revenue by sales rep and sort it."
• Iterate and Refine: Don't expect the first prompt to be perfect. If the formula is almost right, tell ChatGPT what to change. For example, "That sales rep report is great, but could you add a column for the total number of units sold (column E) as well?"
• Ask for an Explanation: If you get a complex formula you don't understand, ask ChatGPT to explain it. "Can you explain what each part of that QUERY formula does?" This is a fantastic way to learn more about how Google Sheets works.

A Few Final Considerations

While this workflow is incredibly efficient, keep a couple of things in mind. First, be mindful of data privacy. Avoid pasting large amounts of sensitive customer or financial data directly into ChatGPT. Instead, describe the structure of your data and use dummy examples. Your goal is to get the analytical method and the formulas, not to have the AI process your proprietary information.

Second, always double-check the output. ChatGPT is an excellent assistant, but it can make mistakes. Test the formulas it provides with a small, known dataset to ensure they are calculating things as you expect before applying them to your full report.

Final Thoughts

By using ChatGPT as your co-pilot, you can dramatically speed up the process of creating insightful, dynamic sales reports in Google Sheets. It transforms the task from a test of your formula knowledge into a simple conversation, allowing you to focus on the story the data is telling, not the syntax needed to uncover it.

While using AI to generate formulas in Google Sheets is a huge improvement over doing it all by hand, we recognized it still involves manual steps like exporting CSVs, setting up tabs, and checking for errors. We built Graphed to remove that friction completely. You just connect your data sources directly - like Shopify, HubSpot, or Google Analytics - and ask questions in plain English. Graphed automatically builds live, real-time dashboards so you can get immediate answers without ever opening a spreadsheet again.