How to Create a Quarterly Sales Report in Excel with AI

Building a comprehensive quarterly sales report in Excel can feel like running a data marathon every three months. You pull data from your CRM, battle messy spreadsheets, and wrestle with formulas just to surface the insights your team needs. This article walks you through the traditional process and then shows you how leveraging AI in Excel can transform this chore into a quick, insightful, and strategic activity.

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

Why a Quarterly Sales Report is More Than Just a Scorecard

Before diving into the spreadsheet, it’s important to understand what makes a quarterly sales report so valuable. It’s not just a backward-looking document to see if you hit your number. A well-constructed report is a strategic tool that guides your entire sales motion for the next quarter.

Think of it as the playbook for your Quarterly Business Review (QBR). It helps you go beyond the final revenue number and understand the 'why' behind your performance. A great report allows you to:

• Track Performance Against Goals: Clearly see where you landed versus where you projected, identifying over-performance and shortfalls.
• Identify Top Performers: Recognize which reps, products, or channels are driving the most success and can be replicated.
• Spot Pipeline Risks and Opportunities: Uncover trends in your sales cycle, conversion rates, or deal stages that require attention.
• Improve Forecasting Accuracy: Use historical data to build more reliable and accurate forecasts for upcoming quarters.
• Make Data-Driven Decisions: Answer critical questions like "Should we invest more in a certain lead source?" or "Which reps need more coaching?".

Ultimately, your quarterly report is about finding patterns and insights that empower you to make smarter decisions, coach your team effectively, and build a more predictable revenue engine.

Gathering the Building Blocks: The Data You Need

The quality of your sales report depends entirely on the quality and completeness of your input data. Before you even open a blank Excel sheet, your first task is to gather the raw materials from your primary data sources. For most sales teams, this means exporting a report from your CRM (like Salesforce or HubSpot).

To build a robust quarterly report, make sure your data export (usually as a .CSV file) includes the following fields:

• Deal Name or ID: A unique identifier for each opportunity.
• Sales Rep: The deal owner responsible for the opportunity.
• Deal Stage: The current status of the deal (e.g., Won, Lost, Open).
• Close Date: The date the deal was marked as "Won" or "Lost." This is crucial for filtering your data to a specific quarter.
• Deal Value: The total contract value or ARR of the deal.
• Lead Source: Where the opportunity originated from (e.g., Organic Search, Paid Social, Partner Referral).
• Product/Service: The specific product or service line sold.
• Create Date: The day the opportunity was first created in your CRM. This helps you calculate sales cycle length.

Once you have this exported file, you're ready to start building.

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

The Traditional Excel Method: Building Your Report Manually

This is the classic, roll-up-your-sleeves approach to building a report in Excel. It works, and it will give you a solid understanding of your data structure, but it can be time-consuming.

Step 1: Clean and Structure Your Data

Raw data exports are rarely perfect. The first 15-20 minutes are usually spent on data hygiene.

• Standardize Dates: Make sure all date columns are formatted as "Short Date" or "Long Date" in Excel. Mixed formats will break your calculations.
• Check for Blanks: Scan key columns like 'Deal Value' or 'Sales Rep' for empty cells that could skew your totals.
• Remove Duplicates: Use Excel's "Remove Duplicates" tool on the Data tab to ensure you're not double-counting deals.
• Create a Table: Select your entire data range and press Ctrl+T (or Command+T on Mac) to turn it into an official Excel Table. This makes formulas easier to read and allows your charts to update automatically when you add new data.

Step 2: Calculate Key Metrics with Formulas

On a separate sheet (let’s call it "Summary"), you can now calculate your key performance indicators (KPIs) for the quarter. Let's assume your data table is named SalesData.

• Total Revenue: The sum of all "Won" deals in your date range.
• Win Rate: The percentage of closed deals that were won. You'll need helper cells to count won and lost deals first.
• Average Deal Size (Won Deals):

Step 3: Create Visualizations with PivotTables and Charts

PivotTables are the engine of manual reporting in Excel. They let you slice and dice your data without writing complex formulas.

1. Click anywhere inside your SalesData table, go to the "Insert" tab, and click "PivotTable."
2. Drag and drop your fields to analyze performance. Here are some common views:
3. Once you have a PivotTable, select it and go to "PivotTable Analyze" to insert a "PivotChart." Bar charts work great for rep leaderboards, line charts are good for trends over time, and pie charts can show the breakdown of lead sources.

You can then copy-paste these charts onto a dashboard sheet to present your findings.

The AI Upgrade: Smarter and Faster Reporting in Excel

The manual process is effective but slow. This is where AI drastically changes the game. Modern versions of Excel (especially with a Microsoft 365 Copilot subscription) and other AI tools can automate nearly every step.

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

AI for Data Cleaning and Formula Generation

Repetitive tasks are what AI does best. Instead of manually tidying up your spreadsheet, AI can accelerate the process. Features like Flash Fill recognize patterns to fix inconsistent data. Even better, tools like Copilot respond to natural language commands.

Instead of searching for the right formula syntax, you can simply ask:

"Calculate the total revenue from won deals in the SalesData table."

Or:

"Help me calculate the sales cycle length in days between the 'Create Date' and 'Close Date' for each won deal."

The AI will generate the correct formula, complete with structured references, saving you time and reducing the chance of errors.

AI for Instant Analysis and Dashboard Creation

This is where AI saves you the most time. Excel's built-in "Analyze Data" feature on the Home tab scans your dataset and offers dozens of suggested PivotTables, charts, and insights.

For more control, generative AI lets you give prompts like:

"Create a bar chart showing the total revenue by sales rep for won deals. Also, make a pie chart showing the count of deals won broken down by lead source."

The AI generates these visualizations and sometimes even suggests placing them on a new dashboard sheet — turning what might take 30 minutes of manual work into a 30-second prompt.

Beyond the Numbers: Adding Narrative with AI

A great report includes not just charts but also a summary of what they mean. Generative AI is excellent at helping craft this narrative. It provides a first draft that you can review and personalize.

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 1: Get Your Key Data Points

First, gather the main numbers from your AI-generated dashboards:

• Total Q2 Revenue: $550,000 (110% of $500k goal)
• Top Performer: Jane Doe, $210,000
• Highest Revenue from Lead Source: Organic Search ($180k)
• Win Rate this quarter: 25% (down from 30% in Q1)

Step 2: Prompt Your AI Assistant

Use a clear prompt to ask the AI to generate a summary. For example:

"Act as a sales manager drafting an executive summary for my upcoming quarterly business review. Based on this Q2 data, write a short, clear summary. Highlight our primary win (exceeding our revenue goal), call out a top performer, identify a potential risk (our declining win rate), and suggest one area to focus on for Q3."

Then, provide the data points.

Step 3: Review and Personalize

The AI will produce a concise, well-structured summary. But don’t just copy it — review and verify it against your data. Add context, such as a big deal closed by Jane Doe or insights into why the win rate is down, to make it more strategic.

Final Thoughts

Building a quarterly sales report in Excel has moved from a manual, formula-heavy chore to a strategic, insight-driven process. Combining structured data with AI tools allows you to spend less time wrangling spreadsheets and more time analyzing trends, coaching your team, and planning your next move.

While AI within tools like Excel simplifies calculations and charts, our aim is to remove even more friction. We built Graphed to connect directly to data sources like Salesforce, HubSpot, and Google Analytics. This eliminates manual export-import cycles, enabling you to create dashboards and get real-time answers by just asking questions in plain English—making sure you're always acting on the freshest data.