How to Create a Monthly Sales Report in Google Sheets
Wrangling sales data into a coherent monthly report can feel like a chore, but it’s one of the most powerful habits for understanding your business performance. A clear, well-structured report helps you spot trends, celebrate wins, and tackle challenges before they become major problems. This guide will walk you through building a dynamic and insightful monthly sales report directly in Google Sheets, step-by-step, using raw data, basic formulas, and charts.
What Belongs in a Monthly Sales Report?
Before you start building, it's helpful to know what you're building towards. A great sales report isn't just a data dump, it tells a story about your team's performance over the last month. While the specifics can vary based on your business, a solid report usually tracks a few key metrics.
Here are some of the most common and valuable metrics to include:
Total Sales Revenue: The most straightforward metric. How much money did you generate this month?
Number of New Leads: How many new potential customers entered your pipeline?
Number of Deals Won: How many of those leads or opportunities did you successfully close?
Conversion Rate: What percentage of leads or deals turned into customers? (Deals Won / Total Leads)
Average Deal Size: The average revenue per closed deal. (Total Revenue / Deals Won)
Sales by Rep: Who are your top performers? This helps recognize success and identify coaching opportunities.
Sales by Product or Service: Which offerings are selling the best?
Sales Cycle Length: How long does it take, on average, to close a deal from first contact to signed contract?
You don't need to track all of these on day one. Start with the three or four that matter most to your business right now. You can always add more complexity later.
Step 1: Get Your Data Organized
Every effective report starts with clean, organized data. Your final dashboard will pull from a single source of truth, which we'll set up in its own tab. Create a new Google Sheet and dedicate one tab to your raw sales data. Let's call it "Sales Data."
Structure this sheet like a simple database. Each row represents a single deal or transaction, and each column represents a piece of information about that deal. Here’s a good starting point for your columns:
Date: The date the deal was closed.
Sales Rep: The name of the salesperson responsible.
Customer Name: The client or company name.
Product/Service: What was sold.
Sale Amount: The value of the deal.
Lead Source: How the customer found you (e.g., Organic, Paid Ad, Referral).
Data Cleaning Best Practices
Garbage in, garbage out. If your data is messy, your report will be inaccurate. Pay attention to a few things:
Consistency is King: Ensure names and categories are spelled the same way every time. "Stacy Johnson" and "stacy johnson" are two different things to a spreadsheet. Use Data Validation (Data > Data validation) to create dropdown menus for columns like "Sales Rep" or "Product/Service" to prevent typos.
Correct Formats: Make sure dates are formatted as dates (Format > Number > Date) and sales amounts are formatted as currency (Format > Number > Currency).
No Blank Rows: Avoid leaving entire rows empty within your data set, as this can break formulas and pivot tables.
Step 2: Build Your Summary Tab with Formulas
Now for the fun part. Create a new tab in your Google Sheet and name it "Monthly Report" or "Dashboard." This is where you'll display your key metrics and visualizations. We'll start by using formulas to pull summary figures from your "Sales Data" tab.
To make our date-based formulas easier, let's add one "helper" column to our "Sales Data" sheet. In column G, title it "Month" and enter this formula in cell G2:
Drag this formula down for all your rows. This will give you a clean month identifier (e.g., "2024-05") for every deal, which is perfect for our summary formulas.
Back in your "Monthly Report" tab, set up some basic labels in Column A:
A1: Total Revenue
A2: Deals Won
A3: Average Deal Size
Now, let's add the formulas in Column B to calculate these figures for May 2024.
Calculating Total Revenue (SUMIF)
The SUMIF function adds up numbers in a range that meet a specific criterion. We want to sum all sales where the month is "2024-05."
In cell B1, enter:
Counting Deals Won (COUNTIF)
Similar to SUMIF, COUNTIF counts the number of cells that meet a criterion. We want to count how many deals happened in our target month.
In cell B2, enter:
Calculating Average Deal Size
This one's easy! We just divide our Total Revenue by the number of Deals Won.
In cell B3, enter:
Just like that, you have your top-level KPIs automatically calculated. When you add new data for May to the "Sales Data" tab, these numbers will update instantly.
Step 3: Dive Deeper with Pivot Tables
Formulas are great for single KPIs, but what about breaking down performance by rep or product? That's where Pivot Tables come in. A Pivot Table is a powerful tool that lets you summarize large amounts of data without writing a single formula.
Let's create a table that shows total sales for each representative.
Click on your "Sales Data" tab and select all of your data (an easy shortcut is to click cell A1 and press Ctrl+A or Cmd+A).
Go to the menu and select Insert > Pivot table.
In the pop-up, choose "Existing sheet" and then click the grid icon to select a spot in your "Monthly Report" tab (e.g., cell D1). Click "Create."
The Pivot Table editor will appear on the right. Now, we just tell it what to do:
Under Rows, click "Add" and select "Sales Re p."
Under Values, click "Add" and select "Sale Amount." Ensure it’s summarized by "SUM."
Under Filters, click "Add" and select your "Month" helper column. Click on the filter that appears in your pivot table, uncheck all months except "2024-05," and click OK.
You now have a clean summary table showing the total revenue generated by each sales rep for the selected month. You can repeat this process to create another pivot table for Sales by Product or Deals by Lead Source.
Step 4: Visualize Your Data with Charts
Numbers and tables are good, but charts are better for quickly understanding performance. Google Sheets makes it incredibly easy to turn your summary data into visuals.
Create a "Sales by Rep" Bar Chart
Bar charts are perfect for comparing performance across different categories, like salespeople.
Highlight the data in your "Sales by Rep" pivot table (including the headers).
Go to the menu and select Insert > Chart.
Google Sheets will automatically suggest a bar chart or column chart, which is exactly what we want.
Use the chart editor on the right to customize it. Give it a clear title like "Monthly Sales Performance by Rep," ensure the axes are labeled, and adjust the colors if you'd like.
Create a "Sales by Product" Pie Chart
Pie charts are excellent for showing the composition of a whole, like how different products contribute to total revenue.
First, create a pivot table summarizing "Sale Amount" by "Product/Service" for the month.
Highlight the data in that new pivot table.
Select Insert > Chart.
If it doesn't default to a pie chart, you can change the chart type in the editor.
Give your chart a title like "Revenue Contribution by Product."
Drag and resize your charts to arrange them neatly on your "Monthly Report" tab. Just place them next to your KPI summaries and pivot tables to create a clean, comprehensive dashboard.
Final Thoughts
Building a monthly sales report in Google Sheets is a fantastic way to get hands-on with your data and turn raw numbers into actionable business intelligence. By organizing your data correctly and using a mix of formulas, pivot tables, and charts, you can create a powerful dashboard that keeps you and your team informed and focused on what drives growth.
Once you’ve built this report, the next step is refreshing it monthly, which still involves a manual export-and-paste process from your CRM or sales platforms. In building our own tool, Graphed, we wanted to eliminate that final, time-consuming step entirely. We enable you to connect directly to platforms like Salesforce, HubSpot, or Shopify and automate the whole process. Instead of manually updating a spreadsheet, you can simply ask, "show me a monthly sales dashboard," and get a live report that updates in real-time, letting you focus on strategy instead of report-building.