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

Creating a monthly sales report often feels like a chore, involving hours of exporting CSVs, wrestling with formulas, and trying to format everything just right. But by pairing Google Sheets with ChatGPT, you can turn your AI assistant into a powerful data analyst that builds formulas and structures reports for you. This article will walk you through exactly how to use this duo to streamline your reporting process, saving you time and giving you clearer insights into your sales performance.

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, Get Your Sales Data Ready

Before you can ask ChatGPT for help, you need a clean, organized dataset. Your sales data probably lives in a few different places - like Shopify, Stripe, Salesforce, or another CRM. The first step is to pull it all into one place.

1. Consolidate Your Raw Data

The simplest way to start is by exporting your sales data as a CSV file from each source for your chosen month. Then, create a new Google Sheet and dedicate the first tab to your raw data. You can name it something like "Raw Data - Dec 2023".

Combine the information from your different exports into a single table. The goal is to have one row for each sales transaction or line item. This manual step is the foundation, and getting it right makes everything else easier.

2. Structure Your Data Table

Make sure your data is organized into clear columns with headers. ChatGPT is smart, but it works best with structured information. A typical sales data table might look like this:

• OrderID: A unique ID for each transaction.
• Date: The date of the sale (e.g., 2023-12-05).
• CustomerName: The name of the customer.
• ProductName: The name of the product sold.
• Category: The product category (e.g., "Software," "Apparel," "Services").
• UnitsSold: The quantity of the product sold.
• PricePerUnit: The price for a single unit.
• TotalRevenue: The total revenue from that line item (UnitsSold * PricePerUnit).

Your table might have more or fewer columns, but the idea is to keep it clean and simple. Fill in any missing TotalRevenue values with a simple formula like =F2*G2 and drag it down the column.

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 Build Your Report Formulas

Now for the fun part. Open a new tab in your Google Sheet and name it "Monthly Report". You will use this tab to build your summary. Instead of fumbling with formulas yourself, you'll ask ChatGPT to write them for you.

The key to getting great results is providing clear context in your prompt.

Prompting ChatGPT for Key Metrics

Open ChatGPT and provide it with a prompt describing your setup. Here's a template you can use:

“I'm creating a monthly sales report in Google Sheets. My raw data is in a tab called 'Raw Data - Dec 2023'. The data covers the range A2:H1000. My columns are: Column B (Date), Column D (ProductName), Column E (Category), Column F (UnitsSold), and Column H (TotalRevenue). Please provide me with the Google Sheets formulas to calculate the following metrics on my 'Monthly Report' tab.”

Now, let's get some common sales formulas.

1. Total Monthly Revenue

Your prompt to ChatGPT:

"A formula to calculate the total revenue for the month."

ChatGPT's likely response:

To calculate the total revenue, use the SUM formula. Place this formula in a cell on your 'Monthly Report' tab:

```=SUM('Raw Data - Dec 2023'!H:H)```

2. Total Units Sold

Your prompt to ChatGPT:

"A formula for the total number of units sold this month."

ChatGPT's likely response:

"Here is the formula to sum the total units sold:"

```=SUM('Raw Data - Dec 2023'!F:F)```

3. Average Order Value (AOV)

This is a slightly more complex metric that shows where ChatGPT really shines.

Your prompt to ChatGPT:

"A formula to calculate the Average Order Value. My OrderID is in Column A."

ChatGPT's likely response:

"Average Order Value can be calculated by dividing total revenue by the number of unique orders. Here is a formula that does that:"

```=SUM('Raw Data - Dec 2023'!H:H)/COUNTA(UNIQUE('Raw Data - Dec 2023'!A:A))```

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

4. Revenue by Product Category

This is where things can get tedious manually. Let's ask ChatGPT for help using a SUMIF formula.

Your prompt to ChatGPT:

"I have my product categories listed in the 'Monthly Report' tab in cells A5, A6, and A7 (e.g., 'Software', 'Apparel', 'Services'). Give me a formula for cell B5 that calculates the total revenue for the category listed in A5. I should be able to drag this formula down."

ChatGPT's likely response:

"Use the `SUMIF` formula. This will sum the revenue if the category matches the value in your specified cell:"

```=SUMIF('Raw Data - Dec 2023'!E:E, A5, 'Raw Data - Dec 2023'!H:H)```

Copy this formula into B5 and drag down to get totals for other categories.

5. Top 5 Selling Products by Revenue

Writing a QUERY formula from memory is tough for almost everyone. This is a perfect task for your AI assistant.

Your prompt to ChatGPT:

"Please give me a single Google Sheets `QUERY` formula that will list my top 5 best-selling products by total revenue from the 'Raw Data - Dec 2023' tab. It should show the product name and its total revenue."

ChatGPT's likely response:

```=QUERY('Raw Data - Dec 2023'!D:H, "SELECT D, SUM(H) GROUP BY D ORDER BY SUM(H) DESC LIMIT 5", 1)```

Copy this formula into a cell, and it will automatically generate your top 5 products by revenue, with product names and totals.

Step 3: From Data to Visualizations

With your key metrics calculated, you can create a visual dashboard. While Google Sheets has built-in charting tools, ChatGPT can help you decide which visualizations work best.

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

Asking ChatGPT for Chart Suggestions

Give ChatGPT a summary of the metrics you’ve calculated and ask for advice.

Your prompt to ChatGPT:

"I have summarized my monthly sales data into total revenue, sales broken down by product category, and a list of my top 5 selling products. What are the best chart types to visualize each of these?"

ChatGPT will likely suggest:

• Total Revenue: A "Scorecard" or "Big Number" chart to display the headline figure.
• Sales by Category: A Pie Chart or a Bar Chart to compare the performance of each category.
• Top 5 Selling Products: A Horizontal Bar Chart to clearly rank the products by revenue.

Building the Charts in Google Sheets

Creating charts from your summary table is straightforward:

1. Select the data you want to visualize (e.g., the product categories and their corresponding revenues).
2. Go to the menu and click Insert > Chart.
3. Google Sheets will suggest a chart type, but you can change it in the chart editor to match ChatGPT's recommendations.
4. Customize the titles, colors, and labels to fit your report's style.

Arrange these charts alongside your metrics to assemble an effective dashboard.

Step 4: Generate a Narrative Summary with ChatGPT

The final step is to add a narrative that explains the numbers. Copy the key metrics from your 'Monthly Report' tab (e.g., total revenue, category performance, top products) and ask ChatGPT to craft a summary.

Your prompt to ChatGPT:

“Based on the following data, please write a brief, friendly summary for my monthly sales report. Highlight the main takeaway, the top-performing category, and one area to focus on for next month.”

Provide your data like:

ChatGPT will generate a concise paragraph that summarizes these insights, perfect for inclusion at the top of your report or in an email.

Final Thoughts

By blending Google Sheets with ChatGPT, you can ditch manual, time-consuming parts of reporting. This approach helps you focus on analyzing and interpreting data rather than building complex formulas or pivot tables.

While manual data import remains a chore, we built Graphed to connect directly to your data sources like Shopify, Salesforce, and Google Analytics. With live dashboards that update automatically, you can simply ask for insights — no spreadsheets needed.