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

Creating a quarterly sales report often feels like a tedious manual chore that eats away at your week. You have to pull data from your CRM, wrestle with spreadsheets, and hope your formulas are correct before your big team meeting. This article will show you how to build a clear and effective quarterly sales report right in Google Sheets and, more importantly, how to use AI to make the entire process dramatically faster.

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, What Actually Goes in a Quarterly Sales Report?

Before you start building, you need a blueprint. A great report tells a story about what worked, what didn't, and where the opportunities are. While the specifics can vary, most high-impact sales reports focus on the same core group of metrics.

Your goal is to provide a comprehensive snapshot of sales performance, pipeline health, and team productivity. Here are the key ingredients you should include:

• Total Sales Revenue: The most obvious and important metric. You'll want to track this against your quarterly goal.
• Deals Won vs. Lost: A raw count that gives you a quick look at volume and overall success.
• Win Rate: Calculated as (Deals Won / Total Deals), this efficiency metric shows how effectively your team is converting opportunities.
• Average Deal Size: Total revenue divided by the number of deals won. Tracking this helps you understand if you're closing bigger (or smaller) deals over time.
• Sales Cycle Length: The average time it takes to close a deal from first contact to signed contract. A shortening sales cycle often means increased efficiency.
• Pipeline Velocity: A measure of how quickly deals are moving through your sales pipeline and generating revenue.
• Lead Source Performance: Which marketing channels (e.g., organic search, paid ads, referrals) are generating the most valuable leads and closed deals?
• Individual Rep Performance: Metrics like revenue closed, win rate, and deals won for each person on your sales team. This helps identify top performers and areas for coaching.
• Quota Attainment: What percentage of the team hit their sales quota?

Trying to assemble all of this manually can be a puzzle. Let's break down how to put the pieces together in Google Sheets.

Step 1: Get Your Raw Sales Data Into Google Sheets

Your report is only as good as the data powering it. For a sales report, this data typically lives in your Customer Relationship Management (CRM) tool like Salesforce, HubSpot, or a smaller industry-specific CRM. If you're running an e-commerce business, your primary data source might be Shopify or Stripe.

The first step is always the same: export your raw data and get it into a Google Sheet.

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

Best Practices for Organizing Your Data

Resist the temptation to start building charts on your downloaded CSV file. Instead, create a dedicated "Raw Data" tab in your Google Sheet where you’ll paste all your exported information. This keeps your clean, unfiltered data separate from your analysis and dashboard.

A clean data set should follow a few simple rules:

• Each row represents one thing. For a sales report, each row should typically represent a single deal or opportunity.
• Each column represents a characteristic. Each column should have a clear header describing what it is, like "Close Date," "Sales Rep," "Deal Stage," or "Amount."
• Keep formatting consistent. Dates should all be in the same format, and deal stages should use consistent terminology (e.g., always "Won," not sometimes "Closed-Won").

Step 2: Building Your Report Manually (The Baseline Method)

Before introducing AI shortcuts, it’s helpful to understand the traditional way of building a report in Google Sheets with formulas and pivot tables. This method works perfectly fine, but it relies on your knowledge of specific functions.

Create a new tab called "Quarterly Dashboard." This is where your summary metrics and charts will live. Now, let’s calculate a few key metrics.

Using Formulas for Key Metrics

For this example, let's assume your raw data is on a tab named 'Raw Data.' The main functions you’ll rely on are SUMIFS (to add numbers that meet certain criteria) and COUNTIFS (to count rows that meet certain criteria).

Total Revenue for Q1:

To calculate the total revenue from deals won in the first quarter (Jan 1 to Mar 31), you'd use a SUMIFS formula.

=SUMIFS('Raw Data'!E:E, 'Raw Data'!D:D, "=Won", 'Raw Data'!A:A, ">=2024-01-01", 'Raw Data'!A:A, "<=2024-03-31")

Number of Deals Won in Q1:

Similarly, you’d use COUNTIFS to count the number of deals won in that period.

=COUNTIFS('Raw Data'!D:D, "=Won", 'Raw Data'!A:A, ">=2024-01-01", 'Raw Data'!A:A, "<=2024-03-31")

Using Pivot Tables for Rep Performance

Pivot tables are fantastic for summarizing data without writing complex formulas. To see how each sales rep performed, you can create a pivot table.

1. Select all the data in your 'Raw Data' tab.
2. Go to Insert > Pivot table.
3. Choose to create it in your existing "Quarterly Dashboard" sheet.
4. In the Pivot table editor:

This will instantly create a summary table showing each rep's contribution, which you can then use to create a bar chart for easy comparison.

Now that you've seen the manual work, let's see how AI can do most of this heavy lifting for you.

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

Enter AI: Supercharge Your Google Sheets Reporting

Artificial intelligence completely changes the dynamic of report building. Instead of needing to know the exact formula or pivot table configuration, you can describe what you want in plain English. AI acts as your data analyst-on-demand, working directly within your spreadsheet.

Level 1: Google Sheets' Built-in AI ("Explore")

Google Sheets has a surprisingly powerful built-in AI tool called Explore. You can find it in the bottom-right corner of your sheet (it looks like a small green icon in a speech bubble).

After clicking Explore, a new panel opens. You can type questions about your data in a search bar.

For example, if you ask:

"total revenue by sales rep for deals that were won"

The Explore feature will instantly generate a bar chart and pivot table summarizing exactly that. You can then drag that chart or table directly into your dashboard tab. It translates your natural language request into a finished analysis, no formulas required.

Level 2: Using Tools Like ChatGPT or Gemini to Write Your Formulas

Sometimes you need more control and want to build a metric card yourself, but you can’t remember the right formula syntax. This is a perfect use case for large language models (LLMs) like ChatGPT or Google's Gemini.

Previously, you would have to Google "how to sum cells based on two conditions and a date range," find a tutorial, and adapt it to your sheet. Now, you can just ask.

Example Prompt for ChatGPT/Gemini:

"I have a Google Sheet with a tab called 'Raw Data'. Column A has the close date, Column D has the deal stage (e.g., 'Won', 'Lost'), and Column E has the deal amount. Can you write me a Google Sheets formula to calculate the total deal amount for all deals marked 'Won' that closed in January 2024?"

The AI will give you the exact formula, ready to copy and paste:

=SUMIFS('Raw Data'!E:E, 'Raw Data'!D:D, "Won", 'Raw Data'!A:A, ">=2024-01-01", 'Raw Data'!A:A, "<=2024-01-31")

This turns a 10-minute research sidetrack into a 10-second copy-paste. It democratizes data analysis, allowing anyone to build sophisticated reports without having to memorize complex functions.

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

Level 3: AI-Powered Sheets Add-ons

For even tighter integration, the Google Workspace Marketplace offers various AI add-ons that work directly inside your sheets. These tools often connect to powerful APIs and give you a chat interface right in a side panel. You can ask them to analyze data, create charts, or even predict future trends based on your existing information without ever leaving your workflow.

Final Thoughts

Building your quarterly sales report in Google Sheets doesn't have to be a multi-day grind of exporting, cleaning, and formula-wrangling. By organizing your data correctly and leveraging AI tools like the built-in Explore feature or external models for formula generation, you can dramatically speed up your workflow and get to the insights faster.

While using helper AIs with Google Sheets is a huge step up from manual work, it still relies on you exporting CSV files and refreshing them every time you want an updated report. So when you’re ready to graduate from static spreadsheets to fully automated, live dashboards, we built Graphed for exactly that. It connects directly to your data sources like Salesforce, HubSpot, and Shopify, so there are no more exports. Instead of asking AI to help you write a formula, you can just ask Graphed to build the entire dashboard for you, in seconds, using plain English.