How to Get Data Analysis in Google Sheets

Google Sheets is much more than a digital ledger for organizing lists and numbers. It’s a surprisingly powerful and accessible data analysis tool that can help you uncover trends, track performance, and make smarter decisions for your business. This tutorial will walk you through the essential techniques for conducting data analysis in Google Sheets, starting with basic data prep and moving through formulas, pivot tables, and visualizations.

First Things First: Prepping Your Data for Analysis

Before you can find any meaningful insights, your data needs to be clean and organized. Messy data - with inconsistent formatting, typos, or extra spaces - can lead to inaccurate results and a lot of frustration. Taking a few minutes to prepare your dataset is the most important step in the process.

Common Data Cleaning Tasks

Here are a few essential steps to ensure your data is ready for analysis:

Remove Duplicates: Duplicate entries can skew your sums and counts. To find and remove them, select your entire dataset, go to the menu bar, and click Data > Data cleanup > Remove duplicates. Google Sheets will ask you which columns to check for duplicates before removing them.
Ensure Consistent Formatting: Make sure columns have a consistent data type. Dates should be formatted as dates (Format > Number > Date), and numerical values like revenue or quantity should be formatted as numbers or currency. Inconsistent data formats can break your formulas.
Handle Blank Cells: Decide how you want to treat empty cells. Should they be zero? Should the row be deleted? Having a consistent approach is key. You can quickly find blank cells by applying a filter (which we'll cover next) and filtering a column to only show "(Blanks)".
Clean Up Extra Spaces: Often, data copied from other sources comes with unwanted leading or trailing spaces. The TRIM function is your best friend here. It removes these extra spaces automatically. If a cell like A2 contains " Product A ", you can use the formula below in a new column to clean it up:

Once your data is clean, you can start digging for insights.

Essential Formulas for Your Analysis Toolkit

Formulas are the building blocks of data analysis in Google Sheets. They allow you to perform calculations, summarize information, and answer specific questions. While there are hundreds of functions, mastering a few core ones will cover most of your analytical needs.

The Basics: SUM, AVERAGE, COUNT, MAX, and MIN

These are the fundamental functions for getting a high-level overview of your data.

SUM: Adds up all numbers in a range. e.g., SUM(B2:B100) gives you the total of all values in column B.
AVERAGE: Calculates the average of numbers in a range. e.g., AVERAGE(B2:B100) finds the average sale value.
COUNT: Counts how many cells in a range contain numbers. e.g., COUNT(B2:B100) tells you the total number of sales recorded.
MAX / MIN: Finds the highest or lowest number in a range, respectively. e.g., MAX(B2:B100) shows you your largest single sale.

Conditional Logic: SUMIF(S) and COUNTIF(S)

This is where real analysis begins. These functions let you sum or count data based on specific criteria. Let's say you have sales data with columns for Product Category (Column C) and Sale Amount (Column D).

COUNTIF: Counts a range if it meets one condition. To count how many sales were for "Electronics":
SUMIF: Sums a range if it meets one condition. To calculate total revenue from "Electronics":
This formula looks in column C for "Electronics" and, when it finds a match, adds the corresponding value from column D to the total.
COUNTIFS & SUMIFS: These powerful functions let you use multiple criteria. For example, to count the number of sales from the "Electronics" category (Column C) that were over $500 (Column D):

Quick Wins: Sorting and Filtering Data

Sometimes you don't need a complex formula to find what you're looking for. Sorting and filtering are simple but effective ways to quickly organize your data and spot patterns.

To get started, select a cell within your dataset and go to Data > Create a filter. You'll see small dropdown arrows appear in your header row.

With filters, you can:

Sort Data: Click the arrow in a column header and choose "Sort A → Z" or "Sort Z → A". For example, sorting your Sale Amount column from Z to A instantly shows you your highest-value transactions.
Filter by Condition: This allows you to hide data that doesn’t meet certain criteria. In a Date column, you can filter to show only sales from last month. For a Product Category column, you can uncheck everything except "Apparel" to see performance for that category alone.

Getting Serious with Pivot Tables

When you need to summarize large datasets to compare categories and find trends, nothing beats a pivot table. A pivot table is an interactive tool that lets you group and cross-reference data in minutes, producing a clean summary table without writing a single formula.

How to Create a Pivot Table: A Step-by-Step Example

Imagine your data has four columns: Date, Sales Rep, Region, and Revenue. You want to know which sales rep is performing best in each region.

Select your entire data range (e.g., A1:D500).
Go to the menu and click Insert > Pivot table.
In the dialogue box, choose "New sheet" and click "Create."
A new sheet will open with the Pivot table editor on the right side. Now, just drag and drop your data fields into the four areas:

Rows: Drag Sales Rep here. This will create a unique row for each sales rep.
Columns: Drag Region here. This will create a unique column for each sales region.
Values: Drag Revenue here. Google Sheets will automatically summarize it by SUM. This is the numerical data that will fill your table.

Instantly, you'll have a perfectly summarized table showing the total revenue generated by each sales rep in each region. From here, you can easily add a Filter (e.g., filter by Date to see performance for just Q4) or change your rows and columns to slice the data differently.

Turning Numbers into Narratives: Creating Charts

While tables are great for precise numbers, charts and graphs are much better for communicating insights visually. They make it easy for anyone to quickly grasp trends, compare values, and spot outliers.

It's often best to build charts from a summary table, like the one you created with your pivot table. To create a chart:

Highlight the summary data you want to visualize (including headers).
Go to the menu and click Insert > Chart.
Google Sheets will suggest a chart type, but you can change it using the Chart editor that appears on the right.

Choosing the Right Chart for Your Data

Line Chart: Use a line chart to show a trend over a continuous period, like monthly website traffic or weekly sales figures.
Bar or Column Chart: Perfect for comparing values across different categories, such as sales per product or leads per marketing channel.
Pie Chart: Use a pie chart to show the parts of a whole, like the percentage of sales coming from different regions. Use them with caution - they become hard to read with more than five or six slices.

Final Thoughts

Performing data analysis in Google Sheets gives you a powerful and cost-effective way to make informed decisions without needing expensive software. From basic functions and filtering to advanced pivot tables and charts, these skills turn your raw data into actionable insights that can help guide your business strategy.

While analyzing data in Sheets is a fantastic skill, the process of manually preparing reports across different platforms can still be time-consuming. At Graphed we streamline all of this. We allow you to connect all your data sources - like Google Analytics, Shopify, Facebook Ads, and Salesforce - in one click. From there, you can ask for dashboards and reports in plain English, like "Create a dashboard comparing Facebook Ads spend vs. revenue by campaign," and watch it appear in seconds. We turn hours of spreadsheet work into a simple conversation, so you can get straight to the insights.