How to Analyze Sales Data in Google Sheets

Analyzing your sales data doesn't require expensive, complex software. Google Sheets, a tool you already have, is perfectly capable of transforming raw sales numbers into clear, actionable insights. This guide will walk you through preparing your data, using key formulas, building pivot tables, and creating charts to understand and improve your sales performance.

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 Things First: Prepare Your Data

Before you can analyze anything, your data needs to be clean, consistent, and structured. This is the most important step, otherwise, your analysis will be based on flawed information. Think of it as setting a solid foundation before building a house.

Standardize Your Data Format

Inconsistent data is a common headache. One sales rep might enter "USA," another "United States," and a web form might input "US." To a spreadsheet, these are three different countries. Take a few minutes to standardize your key fields.

• Dates: Ensure all dates are in the same format (e.g., MM/DD/YYYY). Select the column, go to Format > Number > Custom date and time, and choose a single format.
• Text and Categories: Review columns like country, product category, or sales rep name. Use Find and Replace (Ctrl/Cmd + H) to make them uniform. For instance, find all instances of "US" and replace them with "United States."
• Numbers: Make sure revenue is formatted as currency and units sold are formatted as a number. This prevents calculation errors down the road. Select the column and use the Format > Number menu.

Clean Up Messy Data

Raw data exports are often messy. You'll likely encounter extra spaces, inconsistent capitalization, or duplicate entries. Here are a few simple tools in Google Sheets to fix these issues:

• Remove extra spaces: The TRIM function is your best friend. If you have " Product A " with leading or trailing spaces, it can cause issues. In a new column, use the formula =TRIM(A2) (where A2 is the cell with the messy data) and drag it down. Then copy this new, clean column and paste it as values (Edit > Paste special > Values only) over the original.
• Fix capitalization: Use UPPER(), LOWER(), or PROPER() to standardize case. PROPER() is especially useful for names, as it capitalizes the first letter of each word (e.g., "john smith" becomes "John Smith").
• Split text into columns: Sometimes, information is bundled into a single cell, like "First Name Last Name." To separate them, select the column, then go to Data > Split text to columns. Google Sheets will let you choose a separator, like a space, to split the content.
• Remove duplicates: If you have duplicate orders in your list, click anywhere in your data table and go to Data > Data cleanup > Remove duplicates. You can choose which columns to check for identical values.
• Prevent future errors: Use Data Validation to create dropdown menus for fields like Sales Rep or Region. This forces users to choose from a predefined list, preventing typos and inconsistencies from the start. Just highlight the cells where you want the dropdown, go to Data > Data Validation, and choose "List from a range" or "List of items."

Key Formulas for Sales Analysis

Once your data is clean, you can start asking questions. Google Sheets formulas are the tools you'll use to get answers. Here are the most valuable ones for sales analysis.

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

Calculating Totals and Averages: SUM, COUNT, and AVERAGE

These are the fundamental building blocks of almost any report.

• SUM(): Adds up all numbers in a range. Use it to find your total revenue.
• COUNT() or COUNTA(): Counts the number of cells in a range. COUNT() only counts cells with numbers, while COUNTA() counts all non-empty cells. Use COUNTA() to count the total number of orders.
• AVERAGE(): Calculates the average of a range of numbers. A perfect way to find your Average Order Value (AOV).

Conditional Calculations: SUMIF(S), COUNTIF(S), and AVERAGEIF(S)

This is where analysis gets interesting. These functions perform a calculation only for cells that meet a certain condition. The "S" at the end (SUMIFS) means you can use multiple criteria.

• SUMIF(range, criterion, [sum_range]): Adds values together based on a single condition.
• COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]): Counts the number of rows that meet one or more conditions.
• AVERAGEIFS(average_range, criteria_range1, criterion1, ...): Calculates the average value for rows that meet multiple conditions.

Looking Up Data: VLOOKUP and XLOOKUP

Sometimes your data lives in different sheets. For example, you might have a sales log on one sheet and a customer contact list on another. A lookup function helps you pull data from one sheet into another.

VLOOKUP is the classic function, but the newer XLOOKUP is more powerful and flexible. We recommend using XLOOKUP if you can.

How XLOOKUP Works

The syntax is: =XLOOKUP(search_key, lookup_range, result_range)

Imagine you have an order log with a "Customer ID," and on a separate sheet called "Customers," you have the "Customer ID," "Name," and "Email." To pull the customer's email into your orders sheet:

=XLOOKUP(A2, Customers!A:A, Customers!C:C)

• A2 is the Customer ID you want to look up from your sales log.
• Customers!A:A is the column where it looks for the ID (the lookup_range in your Customers sheet).
• Customers!C:C is the column containing the email you want returned (the result_range).

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

Unlock Insights Fast with Pivot Tables

Formulas are great, but for quickly summarizing and rearranging large amounts of data, nothing beats a pivot table. A pivot table lets you slice and dice your data to find trends without writing a single formula.

For example, let's say you want to see total revenue broken down by sales rep and by product category. Doing this with formulas would be complicated. With a pivot table, it takes about 30 seconds.

How to Create a Pivot Table:

1. Click anywhere inside your dataset.
2. Go to the menu and select Insert > Pivot Table. Choose "New sheet" to keep things organized.
3. A new sheet will open with the Pivot Table editor on the right side. This is where you build your report.
4. Drag and drop fields into the editor sections:

Instantly, you'll have a table showing you each rep’s sales totals for every product category. You can add a filter (e.g., "Region") to the "Filters" section to narrow your view even further.

Bring Your Data to Life with Charts

Numbers and tables are great, but charts make insights easy to spot at a glance. Visualizing your data is the key to presenting your findings to others (and to yourself!). Google Sheets makes creating charts straightforward. Once you have a summary table (either one you built with formulas or a pivot table), select it and go to Insert > Chart.

Here are some of the best chart types for sales data:

Bar and Column Charts

These are perfect for comparing distinct categories. Use them to show:

• Total sales revenue per sales rep.
• Number of units sold by product.
• Customer count by region.

Line Charts

Line charts excel at showing trends over time. Nothing illustrates growth, seasonality, or a sudden dip better. Use them for:

• Monthly or quarterly revenue trends.
• Tracking sales of a specific product over time.
• Comparing year-over-year performance.

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

Pie Charts

Use pie charts to show the composition of a whole. They work best when you have just a few categories (ideally fewer than five). More than that becomes difficult to read. Good use cases include:

• Percentage of revenue from each marketing channel.
• Market share broken down by product category.

Scorecard Charts

If you want to highlight a single, key metric (a KPI), use a scorecard chart. Go to Insert > Chart and select "Scorecard chart." It displays a single large number, making it perfect for dashboard-style summaries of metrics like:

• Total quarterly revenue.
• Year-to-date sales.
• Current Average Order Value.

Final Thoughts

By cleaning your data, applying lookup and conditional formulas, summarizing with pivot tables, and visualizing with charts, you can turn Google Sheets into a powerful sales analysis dashboard. These techniques empower you to move beyond simply collecting data and start using it to make smarter decisions about where to focus your efforts.

For all of the power Google Sheets provides, it still requires manual work - cleaning data, writing formulas, and building reports from scratch. This process can quickly eat up your time. At Graphed , we automate all of it. We connect directly to your data sources like Shopify, Salesforce, HubSpot, or even Google Sheets itself, and let you create entire dashboards and get instant answers in plain English. Instead of building pivot tables, you can just ask, "Show me last quarter's sales by rep and region," and get a live, interactive chart in seconds.