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

Manually building a quarterly sales by territory report can feel like a data-wrangling marathon, especially when you’re just trying to get a clear picture of what’s working and where. This guide will show you how to streamline the entire process by combining the power of Google Sheets with the formula-writing prowess of ChatGPT. You'll learn how to structure your data, ask for the right formulas, and build a reusable report in minutes, not hours.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First, Get Your Sales Data Ready

Before you can ask ChatGPT for help, you need a clean, well-organized dataset. An AI is only as good as the data you give it, and a little bit of prep work here will save you a lot of headaches later. Think of it as setting the table before your expert analyst (ChatGPT) arrives.

Your Google Sheet should have a simple, flat structure. Avoid merged cells, extra header rows, or weird formatting. Each row should represent a single sale, and each column should represent a piece of information about that sale.

The Essential Columns for Your Report

At a minimum, your spreadsheet should include these columns. It's fine if they are in a different order or you have more columns, but these are the basics you’ll need:

• Sale Date: A column with the date of each transaction. Make sure every cell is formatted as a date (you can do this via Format > Number > Date in Google Sheets). Inconsistent formats like "Jan 5" vs "01/05/2024" can confuse formulas.
• Territory: The sales region or territory. The most important thing here is consistency. "North," "N.", and "northern" will be treated as three separate territories. Pick one naming convention and stick to it.
• Sale Amount: The value of the sale. This column should be formatted as a number or currency to ensure it can be summed up correctly.
• Sales Rep: The name of the person who closed the deal. Essential if you want to drill down into rep performance within a territory.

Here's what your data might look like. Let's assume you've named this tab "Sales Data".

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Using ChatGPT to Write the Right Formulas

This is where the magic happens. Instead of trying to remember the complex syntax of Google Sheets functions, you can just tell ChatGPT what you need in plain English. The key is to be specific in your prompts.

Create a new tab in your spreadsheet and name it something like "Q1 Report". This clean sheet is where you'll build your summary table.

How to Craft the Perfect Prompt

A good prompt gives ChatGPT all the context it needs to write an accurate formula. It should include:

1. The Tab Name: Tell it where the source data lives (e.g., 'Sales Data').
2. The Columns: Specify which column holds which piece of data (e.g., "column A is the date, C is the territory, D is the sale amount").
3. Your Goal: State exactly what you want to calculate (e.g., "sum the sales for each unique territory").
4. Your Conditions: Define the date range or any other filters (e.g., "only include sales from Q1 2024, which is from January 1, 2024, to March 31, 2024").

Let's ask ChatGPT to generate a single, powerful formula that will build our entire Q1 report table.

**Example Prompt for ChatGPT:** "Hi, I need a Google Sheets formula. My data is in a tab named 'Sales Data'. Column A has the sale date, Column C has the territory name, and Column D has the sale amount. Can you give me a single QUERY formula that calculates the total sales for each territory for Q1 2024 (January 1 to March 31)? The formula should create a two-column table with the territory name and its total sales."

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Understanding the Formula ChatGPT Gives You

ChatGPT will likely give you something that looks complicated at first, but it’s quite simple once you break it down. It will probably suggest the QUERY function, one of the most flexible in Google Sheets.

Here's a likely response:

=QUERY('Sales Data'!A:D, "SELECT C, SUM(D) WHERE A >= DATE '2024-01-01' AND A <= DATE '2024-03-31' GROUP BY C LABEL SUM(D) 'Q1 Total Sales'")

Let's quickly translate that formula:

• =QUERY('Sales Data'!A:D, ...): Ask Google Sheets to run a query on columns A to D in the 'Sales Data' tab.
• "SELECT C, SUM(D) ...": Display column C (Territory) and sum of column D (Sale Amount).
• " ...WHERE A >= DATE '2024-01-01' AND A <= DATE '2024-03-31'": Only include rows with dates in Q1 2024.
• " ...GROUP BY C": Group results by territory.
• " ...LABEL SUM(D) 'Q1 Total Sales'": Rename the sum column to "Q1 Total Sales".

Copy this formula from ChatGPT and paste it into cell A1 of your "Q1 Report" tab. You'll get a perfect summary of your first-quarter sales by territory.

Building a Dynamic, Reusable Report

A static Q1 report is great, but a report you can update for any quarter with a single click is even better. We can ask ChatGPT to help us make our formula more flexible so we don't have to manually update the dates every time.

Setting Up Date Controls

In your report tab (e.g., "Q1 Report"), set aside two cells for your date range. For example:

• In Cell E1, type "Start Date". In cell F1, enter the first day of the quarter (e.g., 1/1/2024).
• In Cell E2, type "End Date". In cell G1, enter the last day of the quarter (e.g., 3/31/2024).

Now, let's go back to ChatGPT with a follow-up request to modify our formula.

**Follow-Up Prompt for ChatGPT:** "That previous formula was perfect! Now, can you modify it to be dynamic? I want it to use a start date from cell F1 and an end date from cell G1 of the current sheet, instead of having the dates hard-coded into the formula."

ChatGPT's job is now to adjust the syntax to properly reference those cells.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Dynamic Formula

The modified formula will look slightly more complex due to the way Google Sheets combines text and cell references inside a QUERY, but the logic is the same:

=QUERY('Sales Data'!A:D, "SELECT C, SUM(D) WHERE A >= date '"&TEXT(F1, "yyyy-mm-dd")&"' AND A <= date '"&TEXT(G1, "yyyy-mm-dd")&"' GROUP BY C LABEL SUM(D) 'Total Sales'")

Paste this new formula into cell A1. Now, to generate a Q2 report, just change the dates in cells F1 and G1 to 4/1/2024 and 6/30/2024 respectively. The table updates instantly.

Visualizing the Report

Numbers are great, but visual charts make trends and outliers easier to see. Once your summary table is ready, creating a visualization in Google Sheets is straightforward:

1. Select your data: Highlight the territory names and their totals.
2. Insert Chart: Go to Insert > Chart.
3. Choose a Chart Type: Default options like column or bar charts work well, pie charts can be good for fewer territories.
4. Customize: Add titles like "Quarterly Sales by Territory," adjust colors, and ensure labels are clear.

Now you have a professional report that combines numbers and visuals for easy insight.

With this template, you can build views for all four quarters, compare year-over-year, and analyze performance without wasting time on manual data crunching.

Final Thoughts

Creating a quarterly sales report doesn't have to be manual or repetitive. By asking clear, specific prompts, you leverage ChatGPT to generate formulas, allowing you to focus on insights instead of mechanics. This transforms you from a data gatherer into a data analyst in minutes.

While ChatGPT automates formula writing, managing and updating data remains essential. We built Graphed to connect directly to data sources like Salesforce, HubSpot, or SQL databases. Simply ask for what you need in plain English and get a live dashboard, auto-updated and interactive, giving you always the real-time business picture.