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

Building a quarterly sales by territory report from scratch doesn't have to be a multi-day spreadsheet project. By organizing your data correctly and leveraging the right tools in Google Sheets, you can quickly see which regions are driving growth and which need more support. This article will walk you through exactly how to structure your sales data and create a clear, insightful territory report, using both traditional pivot tables and new, faster AI-powered methods.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why a Quarterly Sales by Territory Report Is a Game-Changer

Before jumping into the how-to, it’s worth a moment to remember why this specific report is so powerful. Manually sifting through sales data can feel overwhelming, but organizing it by territory and time period immediately brings clarity. A well-built quarterly report isn't just a scoreboard, it's a roadmap that helps you make smarter decisions.

It allows you to:

• Identify Top-Performing Regions: Instantly see which territories are hitting and exceeding their quotas. What are they doing right? Can their strategies be replicated in other regions?
• Spot Underperforming Areas Early: If a territory is lagging behind, a quarterly report gives you a chance to intervene before a small problem becomes a year-long issue. It helps you ask the right questions: Is it a problem with market fit, competition, or does the local sales team need more training and resources?
• Allocate Resources Effectively: Use hard data to justify where you invest your marketing budget, hire new sales reps, or channel support resources. Stop guessing and start making data-informed choices.
• Set Realistic Sales Goals: Set future sales targets and quotas based on actual historical performance, not just wishful thinking. This makes goals more achievable and keeps your team motivated.

Step 1: Get Your Sales Data Ready for Analysis

The quality of your report depends entirely on the quality of your data. The biggest mistake people make is jumping straight into analysis with messy, inconsistent data. Taking just a few minutes to clean and structure your data will save you hours of frustration.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Data You Need

To create a sales by territory report, you’ll need a simple raw data table in Google Sheets. Each row should represent a single sale, and each column should represent a piece of information about that sale. At a minimum, your spreadsheet should have these columns:

• Sale ID: A unique identifier for each transaction (e.g., A-1001, A-1002).
• Date: The date the sale was closed. Make sure this is in a consistent date format (e.g., MM/DD/YYYY).
• Sale Amount: The revenue from the sale, formatted as currency.
• Sales Representative: The name of the person who closed the deal.
• Territory: The sales region (e.g., North, South, East, West, or state names like California, New York). This is the key field we’ll use for grouping.

Pro Tips for Data Organization:

• One Header Row Only: Your table should have a single, frozen header row at the top. Avoid merging cells or having multiple header lines.
• No Empty Rows or Columns: Ensure there are no completely blank rows or columns within your dataset, as this can break functions and pivot tables.
• Consistent Naming: Be consistent with your territory names. "North" and "N." will be treated as two different territories. Use data validation or find-and-replace to clean these up.

Step 2: Build the Report (Two Ways)

Once your data is clean, it's time to build the actual report. We’ll cover two methods: the classic pivot table approach and the modern, faster way using AI add-ons within Google Sheets.

Method 1: The "Traditional" Way Using Pivot Tables

Pivot tables are Google Sheets' most powerful tool for summarizing large datasets. They might seem intimidating at first, but they're perfect for exactly this type of analysis.

Create a Helper Column for the "Quarter"

First, we need a way to group your sales dates by quarter. The easiest way is to add a "helper column" to your data table.

1. Click on the letter of the column next to your "Date" column and select "Insert 1 column right." Name this new column "Quarter."
2. In the first cell of this column (e.g., F2), enter the following formula:
3. Drag the little blue square at the corner of the cell all the way down to apply this formula to every row in your dataset. Your sheet should now have a column that clearly labels each sale's quarter (e.g., "2024-Q1," "2024-Q2").

Build the Pivot Table

1. Click anywhere inside your data table.
2. Go to the nav menu and choose Insert > Pivot table.
3. In the "Create pivot table" dialog, make sure your data range is correct and choose "New sheet." Click "Create."
4. A blank pivot table and an editor sidebar will appear. Now, tell the pivot table how to organize the data:
5. In the filter box that appears on your pivot table, uncheck all quarters except the one you want to report on (e.g., "2024-Q2").

Instantly, you'll have a clean summary table showing the total sales for each territory for that specific quarter.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 2: The Modern AI-Powered Way

If pivot tables feel a bit too manual, you can now use natural language to get the same result in seconds. AI add-ons for Google Sheets let you "talk" to your data. Instead of dragging and dropping fields, you just type what you want.

Several of these tools exist on the Google Workspace Marketplace. Once you've installed one, the process generally looks like this:

1. Open the AI add-on: You can usually find this in the Extensions menu of Google Sheets.
2. Select your data range: Highlight the data you want to analyze.
3. Write your prompt: In the add-on's chat box, type a clear, plain-English command. You don't need to know formulas or functions.

Example Prompts for Your Report:

• "Summarize Sale Amount by Territory."
• "Show me the total sales for each territory, but only for the second quarter."
• "Create a pivot table showing total sales by territory and sales rep for deals closed in Q2."

The AI will interpret your request and generate the summary table or pivot table for you, often in a new sheet, without any manual setup. This approach dramatically lowers the technical barrier, allowing anyone on your team to get answers from data without needing to be a spreadsheet expert.

Step 3: Visualize the Data for Instant Insights

Numbers in a table are good, but a visual chart tells a story at a glance. Visualizing your sales territory data makes it easier for your team and stakeholders to digest the key findings.

Creating a Bar Chart

1. Highlight the data in your final pivot table (both the territory names and their sales totals).
2. Go to Insert > Chart.
3. Google Sheets will usually default to a bar or column chart, which is what you want. You can use the Chart Editor on the right to customize colors, add titles (e.g., "Q2 2024 Sales by Territory"), and adjust formatting.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Building a Geo Map

Since this is a territory report, why not display your data on a map? A geo chart adds a powerful geographic context that a bar chart can't provide.

1. Make sure one of your columns in your pivot table is a recognizable geographic area (e.g., states like "California," "Texas," "Florida").
2. Highlight your data, just like for the bar chart.
3. Go to Insert > Chart.
4. In the Chart Editor, under the "Setup" tab, click the "Chart type" dropdown and scroll down to select the Geo chart.
5. Google Sheets will automatically plot your sales values on a map, with darker colors representing higher sales.

Just like with creating the summary table, you can frequently use AI tools to create charts as well. A prompt like, "Build a bar chart comparing sales by territory for Q2" will often generate the visual for you instantly.

Final Thoughts

Mastering a quarterly sales by territory report pulls your business operations out of the dark and into the light. Now you know how to structure your raw data and use tools like pivot tables, AI assistants, and charts in Google Sheets to turn endless rows of numbers into a clear strategic overview for your team.

While AI in Google Sheets makes this process faster, it still requires you to manually export data and keep your spreadsheets up-to-date. At an even earlier stage in the process, we built Graphed to remove that friction completely. You just connect your data sources like Salesforce or HubSpot once, and then use natural language to build and chat with real-time, self-updating dashboards. You can ask "Show me my sales pipeline from Salesforce compared with marketing spend from Facebook for this quarter" and get a live dashboard back in seconds - no more exporting CSVs or fighting with pivot tables.