How to Create a Sales Report in Google Sheets

Creating a sales report is one of the most effective ways to understand your business performance, but you don’t need complex business intelligence software to get started. Google Sheets offers a powerful, free, and collaborative way to organize your sales data and turn it into actionable insights. This article will guide you through the process of building a dynamic, shareable sales report from scratch in Google Sheets.

Why Use Google Sheets for Sales Reporting?

Before jumping into the steps, it’s worth noting why Google Sheets is such a fantastic tool for this job. It's accessible from anywhere, allows real-time collaboration with your team, and is completely free. You can easily connect it to other tools (like Google Forms for data entry) and build surprisingly powerful dashboards that update as your data grows.

Step 1: Gather and Structure Your Sales Data

The foundation of any good report is clean, well-organized data. A messy dataset will lead to inaccurate summaries and confusing charts. Your goal is to create a raw data ledger where each row represents a single sale and each column represents a specific attribute of that sale.

Create a new Google Sheet and dedicate the first tab to your raw sales data. We recommend naming it something simple like "Sales Data" or "Raw Data."

Your data table should include columns for essential information. Here's a standard structure that works for most businesses:

• Transaction ID: A unique identifier for each sale.

• Date: The date the sale was closed.

• Sales Rep: The name of the salesperson responsible for the deal.

• Customer Name: The name of the client or company.

• Product/Service Tier: The specific item or service package sold.

• Region: The geographic location of the customer.

• Units Sold: The quantity of the product sold. (If not applicable, you can omit this).

• Unit Price: The price for a single unit.

• Total Sale Value: The total revenue from that specific transaction.

A Quick Tip on Formulas

You can automate the "Total Sale Value" column. If "Units Sold" is in column G and "Unit Price" is in column H, you can enter the following formula in cell I2:

=G2*H2

Then, simply click the small blue square (the fill handle) in the corner of cell I2 and drag it down to apply the formula to the entire column. Now, your total sale value will calculate automatically.

Step 2: Define and Calculate Your Key Sales Metrics

With your data organized, you can now build the reporting side. Create a new tab and name it "Dashboard" or "Sales Report." This is where you will summarize and visualize your data.

First, let's identify the most important metrics (KPIs) you want to track. On your new dashboard tab, create a small area to display these high-level figures. Here are a few essential sales KPIs and the formulas to calculate them:

Total Revenue

This is the most straightforward metric. It's the sum of all your sales. Assuming your "Total Sale Value" is in Column I of your "Sales Data" tab, the formula is:

=SUM('Sales Data'!I:I)

Average Deal Size

This tells you the average value of each transaction, which is helpful for forecasting and understanding customer value. The formula is:

=AVERAGE('Sales Data'!I:I)

Total Deals Won

This is a simple count of how many sales you've made. The formula counts all the entries in your "Total Sale Value" column:

=COUNTA('Sales Data'!I2:I)

Arrange these on your dashboard tab for an at-a-glance overview of your sales performance.

Step 3: Create Dynamic Summaries with Pivot Tables

Pivot tables are the secret to creating dynamic sales reports in Google Sheets. They allow you to quickly summarize large datasets without writing complex formulas. You can use them to answer questions like, "Who is my top-performing sales rep?" or "Which service is selling the best?"

Let's create two pivot tables: one to analyze sales by rep and another for sales by product.

How to Create a Pivot Table

1. Go to your "Sales Data" tab and select all your data (you can click cell A1 and press Ctrl + A or Cmd + A).

2. In the menu, go to Insert > Pivot table.

3. In the dialog box that appears, select "Existing sheet" and then click the grid icon to select a spot for your pivot table on your "Dashboard" tab.

This will create an empty pivot table and open the Pivot table editor sidebar.

Example 1: Sales by Rep

In the Pivot table editor:

• Click "Add" next to Rows and choose "Sales Rep."

• Click "Add" next to Values and choose "Total Sale Value." Ensure it's set to "SUM."

Instantly, you'll see a clean summary of total revenue generated by each member of your sales team. You now have a mini leaderboard!

Example 2: Sales by Product/Service

Create a second pivot table next to the first one. This time, configure it as follows:

• Rows: "Product/Service Tier"

• Values: "Total Sale Value" (summarized by SUM)

This table shows you which of your products or services contribute the most to your revenue, helping you identify what’s most popular with customers.

Step 4: Visualize Your Data with Charts

Numbers and tables are great, but charts make your data much easier to digest. Google Sheets makes it simple to create visualizations directly from your pivot tables or summary data.

Creating a "Sales by Rep" Bar Chart

1. Highlight the data in your "Sales by Rep" pivot table (including the headers).

2. Go to Insert > Chart.

3. Google Sheets will automatically suggest a chart type. A column chart or bar chart is perfect for comparing performances.

4. Use the Chart editor on the right to customize titles, colors, and labels to make it clear and professional.

Move this new chart onto your dashboard. Now, you have a visual representation of your top performers.

Creating a "Revenue Over Time" Line Chart

A line chart is ideal for showing trends. To do this, you first need a pivot table that groups sales by date.

1. Create another pivot table.

2. Set Rows to "Date."

3. Set Values to "Total Sale Value" (as SUM).

4. Right-click on any of the dates in the pivot table and select Create pivot date group > Year-Month.

5. Now, highlight this new time-based data and insert a chart. Select the line chart type.

This chart will show your revenue trend, making it easy to spot growth, seasonal slumps, or the impact of a recent marketing campaign.

Step 5: Make Your Dashboard Interactive with Slicers

Slicers are filters that let you (or anyone you share the report with) interact with your data without breaking anything. For example, you can add a slicer to filter your entire dashboard by a specific sales rep, region, or time frame.

How to Add a Slicer

1. Make sure you’ve selected a cell within one of your pivot tables. This is important as the slicer needs to be connected to the dataset.

2. Go to Data > Add a slicer.

3. An interactive filter box will appear. In the Slicer settings sidebar, under "Column," choose the field you want to filter by, such as "Region" or "Sales Rep."

4. Customize the look of your slicer if you wish.

Now, place the slicer on your dashboard. When you select a value from the slicer (e.g., the "North" region), all connected pivot tables and the charts based on them will automatically update to show data for only that region. This turns your static report into a powerful analytical tool.

Step 6: Putting It All Together

The final step is to arrange all your elements - KPIs, pivot tables, charts, and slicers - into a clean and organized layout on your "Dashboard" tab. A good practice is:

• Top Row: Place your key summary metrics like Total Revenue and Average Deal Size using Scorecard charts (Insert > Chart > Scorecard chart).

• Main Body: Arrange your most important charts, like "Sales by Rep" and "Revenue Over Time."

• Side or Top Bar: Place your slicers so they are easily accessible for filtering.

Your finished dashboard gives you a comprehensive, single-page view of your sales performance that updates automatically whenever you add new information to the "Sales Data" tab.

Final Thoughts

Building a sales report in Google Sheets equips you with a powerful way to monitor performance, identify trends, and make data-driven decisions for your business. By structuring your data correctly and leveraging tools like pivot tables, charts, and slicers, you can create a professional-grade dashboard without spending a dime on specialized software.

While Google Sheets is fantastic for manual reporting, the process of exporting data from your CRM or sales platform and formatting it weekly can become tedious. At Graphed we automate this entire workflow. By connecting directly to your data sources like Salesforce, HubSpot, or Shopify, we allow you to build live, powerful dashboards simply by asking questions in a conversational chat. This turns hours of spreadsheet work into seconds and ensures you're always looking at real-time data, not last week's export.