How to Create a Quarterly Sales Report in Excel

Creating a quarterly sales report in Excel can feel like a chore, but it’s essential for understanding your business's health and planning your next steps. The good news is you can build a powerful, insightful dashboard without getting lost in endless spreadsheets. This guide will walk you through the entire process, from structuring your raw data to creating clear, dynamic visualizations that tell a compelling story.

What Should a Quarterly Sales Report Include?

Before you jump into formulas and charts, it’s important to outline what you need to measure. A great sales report presents clear, relevant key performance indicators (KPIs) that align with your business goals. While the exact metrics will vary, a comprehensive report typically includes a mix of the following:

• Total Sales Revenue: The most fundamental metric. You'll want to see total revenue for the quarter and compare it to previous quarters and your sales goals.

• Sales Volume: The total number of deals closed or units sold.

• Sales by Rep or Team: Breaks down performance to identify top performers and areas needing support.

• Revenue by Product/Service: Highlights which offerings are driving the most revenue so you know what to focus on.

• Average Deal Size: Calculated by dividing total revenue by the number of deals closed. Tracking this helps you understand if you're closing larger or smaller deals over time.

• Win/Loss Rate: The percentage of deals won out of the total number of opportunities. This is a critical indicator of sales funnel effectiveness.

• Sales Cycle Length: The average time it takes to close a deal, from first contact to signed contract.

• Revenue from New vs. Returning Customers: Shows your balance between customer acquisition and retention efforts.

Think of this list as a starting point. Choose the metrics that best reflect your team's objectives and your company's strategic priorities.

Step 1: Get Your Sales Data Ready for Analysis

The quality of your report depends entirely on the quality of your data. This preparation phase is the most important step for accurate analysis, so don't skip it! Messy data leads to unreliable reports.

Gather All Your Data in One Place

Your sales information likely lives in a few different places. You'll need to export the relevant data for the quarter from sources like:

• Your CRM: Salesforce, HubSpot, Zoho, etc.

• E-commerce Platforms: Shopify, BigCommerce, etc.

• Payment Platforms: Stripe, PayPal, etc.

Export your data as a CSV or Excel file and compile it into a single worksheet. This sheet will be your "raw data" source for the entire report.

Structure Your Data in a Clean Table

For Excel to analyze data effectively, it needs to be organized in a "tidy" format. This means:

• Each row represents a single transaction or opportunity (e.g., one sale).

• Each column represents a single variable (e.g., Sale Date, Sales Rep, Product, Customer Name, Revenue, Status).

• There are no empty rows or columns within your data set.

A good data structure would look something like this:

Clean and Format the Data

Now, it's time to refine your data set.

• Format Dates: Ensure your "Sale Date" column is formatted as a date. Select the column, go to the Home tab, and choose "Short Date" or another consistent date format.

• Format Numbers: Make sure revenue figures are formatted as a number or currency, not text.

• Fix Inconsistencies: Use Excel’s Find and Replace feature (Ctrl + H) to correct misspellings or variations in names (e.g., change "J. Smith" to "Jane Smith").

• Remove Duplicates: Go to the Data tab and click "Remove Duplicates" to eliminate any accidental carbon copies.

Convert Your Data to an Excel Table

Once your data is clean, convert it into an official Excel Table. This makes it far easier to manage and reference in formulas. Simply click anywhere inside your data range and press Ctrl + T (or go to Insert > Table). This simple action unlocks self-expanding ranges, clear formatting, and easy structured references for your formulas.

Step 2: Calculate Your Key Metrics

With your data prepped, you can start building the analysis. Create a new worksheet and call it "Dashboard" or "Report." This is where your summary calculations and charts will live, keeping them separate from your raw data.

Here are the formulas you'll need to calculate your main KPIs.

Total Sales Revenue

This is the simplest calculation. In a cell on your Dashboard sheet, enter:

=SUM(Table1[Revenue])

(Replace Table1 with the name of your Excel Table and [Revenue] with the name of your revenue column.)

Revenue per Sales Rep

The SUMIFS function is perfect for this. It lets you sum values that meet specific criteria. First, list out your sales reps' names on your Dashboard sheet. Then, next to the first name (let's say it's in cell A2), enter:

=SUMIFS(Table1[Revenue], Table1[Sales Rep], A2)

This formula sums all revenue from your data table where the sales rep's name matches the name in cell A2. You can then drag this formula down for the rest of your reps.

Win Rate

To calculate your win rate, you first need to count your wins and your total opportunities. You'll use the COUNTIF and COUNTA functions for this. Assuming your data table has a "Status" column with "Won" or "Lost":

Wins:

=COUNTIF(Table1[Status], "Won")

Total Opportunities:

=COUNTA(Table1[Status])

Then, divide wins by total opportunities to get your win rate, and format the cell as a percentage.

=[Wins_Cell_Reference] / [Total_Opportunities_Cell_Reference]

Average Deal Size

Simply use the AVERAGE function:

=AVERAGE(Table1[Revenue])

You can also create a more specific version, like the average deal size for won deals only, using AVERAGEIF:

=AVERAGEIF(Table1[Status], "Won", Table1[Revenue])

Step 3: Visualize Your Data with Charts and PivotTables

Raw numbers are informative, but visualizations make the data easy to understand at a glance. Excel's chart tools and PivotTables are excellent for bringing your quarterly report to life.

Creating Charts from Your Summary Data

For the summary metrics you’ve already calculated, you can create straightforward charts.

• For Sales by Rep: Highlight the sales rep names and their revenue figures, then go to Insert > Column or Bar Chart. A bar chart is usually best for comparing individual contributions.

• For New vs. Returning Customers: After calculating revenue for each group with SUMIFS, use a Pie or Donut chart to show the revenue split. It's an easy and instant visual of your customer base.

Build a Powerful Dashboard with PivotTables

Instead of manually creating summary tables with formulas, PivotTables can do most of the heavy lifting for you automatically. They are the fastest way to summarize and analyze large data sets.

To create a PivotTable:

1. Click anywhere inside your cleaned data table.

2. Go to the Insert tab and click "PivotTable." Excel will automatically select your table and prompt you to place the PivotTable in a new worksheet.

3. A PivotTable Fields pane will appear on the right. Now, drag and drop the fields to analyze your data:

   • Drag Sale Date into the Rows area. Excel will automatically group the dates by month and quarter.

   • Drag Revenue into the Values area. It will automatically sum the revenue for each time period.

   • Drag Sales Rep into the Filters or Columns area to slice the data by individual rep.

In just a few clicks, you have a summary of revenue by quarter. From this PivotTable, you can easily create a PivotChart (Analyze > PivotChart) that dynamically updates as you change the PivotTable fields.

Add Slicers and Timelines for an Interactive Report

Want to let managers filter the report themselves? Slicers and Timelines are the answer.

1. Click on your PivotTable.

2. Go to the PivotTable Analyze tab.

3. Click Insert Slicer. Choose fields you want to filter by, like "Sales Rep" or "Product Category."

4. Click Insert Timeline. Choose your date field.

Now you have interactive buttons that allow anyone viewing the report to easily filter the data without breaking any formulas. This makes your report a powerful, self-service tool for your team.

Final Thoughts

Building a quality quarterly sales report in Excel comes down to a clear, repeatable process: organizing your source data, using functions like SUMIFS or a PivotTable to perform calculations, and then using charts to tell a story with the results. Once you’ve built your template, updating it each quarter becomes a matter of pasting in new data and hitting refresh.

While Excel is a powerful tool, manually exporting CSVs from Salesforce or Shopify every week can become a routine for teams. Instead, we allow you to natively connect with your data sources to get your dashboard live in real-time. With Graphed, we automate the manual reporting drudgery entirely, we connect directly to your marketing and sales platforms, so your dashboards are always up-to-date. You can create the exact report you need just by describing it in plain English, turning hours of tedious work into a 30-second task.