How to Do Data Analysis in Google Sheets

Your Google Sheet is more than just a grid of cells, it's a surprisingly powerful platform for doing serious data analysis. Far from just storing lists, you can use it to clean data, uncover trends, and create compelling visuals to understand your business performance. This guide will walk you through the essential steps for turning raw numbers in Google Sheets into clear, actionable insights.

Start with a Clean Slate: How to Prepare Your Data

Before you can find any insights, you need reliable data. The reality is, most raw data is a bit messy. It has duplicates, typos, blank cells, and inconsistent formatting. Taking a few minutes to clean it up - a process called data wrangling - is the most important thing you can do for an accurate analysis.

Imagine you have a simple sales report with columns like Date, Product, Category, Units Sold, and Total Sale. Here's how to get it ready.

1. Get Rid of Duplicates

Duplicate rows can inflate your numbers and skew your results. Thankfully, Google Sheets makes them easy to find and remove.

Select the entire data set you want to check.
Go to the menu and click Data > Data cleanup > Remove duplicates.
A dialog box will pop up. Choose which columns to check for duplicates. If you want to delete a row that is a perfect match of another, just check "Select all."
Click "Remove duplicates," and Sheets will tell you how many duplicate rows it found and removed.

2. Handle Blank Cells and Typos

Blank cells can throw off calculations, and inconsistent naming (e.g., "T-shirt," "tshirt," "T-Shirt") splits your data across multiple categories when it should be just one.

Fixing Inconsistencies: The quickest way to fix typos is with "Find and replace." Go to Edit > Find and replace. You can, for example, find all instances of "tshirt" and replace them with the standardized "T-Shirt."
Tackling Extra Spaces: Use the TRIM function to remove pesky extra spaces before or after text, which can also mess up your groupings. Create a new column and use the formula =TRIM(A2) (where A2 is the cell with text you want to clean). Then drag it down for the whole column and copy-paste the values back into the original column.
Finding Blanks: To find blank cells, create a filter (Data > Create a filter). Click the filter icon in the column header, deselect "Select all," and then select "(Blanks)" to see only rows with missing information in that column. You can then decide whether to delete these rows or manually fill in the missing data.

3. Standardize Your Data Formats

Make sure your numbers and dates are formatted correctly so Sheets can perform calculations on them.

Select the column you want to format.
Go to Format > Number.
From here, you can set the format to Currency, Date, Percent, or just Plain text. This ensures your $50.00 is treated as a number, not just text characters.

Unleash the Power of Functions

With clean data, you can start asking questions. Google Sheets functions are your tools for getting answers. While there are hundreds, a handful will power most of your analysis.

Basic Aggregate Functions

These functions give you a quick overview of your data.

=SUM(E2:E100): Adds up a range of cells. e.g., calculates the total sales.
=AVERAGE(E2:E100): Calculates the average of a range. e.g., gives you the average sale amount.
=COUNT(A2:A100): Counts how many cells in a range contain numbers. e.g., tells you how many sales you've made.
=MAX(E2:E100) / =MIN(E2:E100): Finds the highest or lowest value in a range. e.g., finds your largest single sale.

Conditional Functions for Deeper Insights

What if you want to analyze only a segment of your data? That’s where conditional logic comes in.

SUMIF and COUNTIF: These are incredibly useful for segmenting data. SUMIF adds up numbers that meet one criterion, while COUNTIF counts entries that meet a criterion.

Example: To get the total sales just for the "Apparel" category (assuming categories are in column C and sales totals are in column E), you'd use:=SUMIF(C2:C100, "Apparel", E2:E100)

SUMIFS and COUNTIFS: These are the supercharged versions that let you apply multiple criteria.

Example: To get total sales for "Apparel" sold in the past 30 days (assuming dates are in column A):=SUMIFS(E2:E100, C2:C100, "Apparel", A2:A100, ">"&TODAY()-30)

VLOOKUP: Your Data Matchmaker

VLOOKUP (Vertical Lookup) finds a piece of information in one table and brings it into another. For example, if your sales log has a ProductID but not the product's Cost, you could use VLOOKUP to pull in the cost from a separate "Product Details" sheet.

The syntax looks like this: =VLOOKUP(search_key, range, index, [is_sorted])

search_key: The value you're looking for (e.g., the ProductID).
range: The data range where you want to search. Your search_key must be in the first column of this range.
index: The column number (from your range) that contains the value you want to return.
is_sorted: Almost always set this to FALSE for an exact match.

Summarize Everything with Pivot Tables

Pivot tables are arguably the single most powerful data analysis tool in Google Sheets. They let you quickly summarize massive amounts of data with a simple drag-and-drop interface, all without writing a single formula.

For example, you could take your sales data spanning thousands of rows and, in less than a minute, create a summary table showing total sales for each product category, broken down by month.

How to Create a Pivot Table:

Select your entire dataset.
Go to Insert > Pivot table. Sheets will typically create it on a new tab.
The Pivot table editor will appear on the right side. Now you just have to configure it:
- Rows: Drag a field here to group your data vertically. Example: Drag the 'Category' field here.
- Columns: Drag a field here to group your data horizontally. Example: Drag 'Date' here, then right-click a date in the table and choose 'Create pivot date group' > 'Year-Month'.
- Values: This is where you put the metric you want to calculate. Example: Drag 'Total Sale' here, and make sure it is summarized by 'SUM'.
- Filters: Add a field here to filter your entire table. Example: Add a filter for the 'Product' field to only see data for certain items.

In just a few clicks, you have a dynamic, powerful summary that automatically updates as you add more data to your original source sheet.

Tell Your Story with Charts

Numbers and tables are great, but visuals are better for spotting trends and communicating your findings. Google Sheets makes it simple to create clean, effective charts.

To create a chart, select the data you want to visualize (a range on your main sheet or your pivot table) and click Insert > Chart. The Chart editor pane lets you customize everything.

Choose the Right Chart for Your Data:

Column or Bar Charts: Perfect for comparing values between a few distinct categories. Use it to compare total sales across different product categories.
Line Charts: The best choice for showing a trend over a continuous period, like time. Use it to track your monthly sales throughout the year.
Pie Charts: Use these to show how different parts make up a whole - the proportional contribution. Best for when you have just a few categories, like showing the percentage of sales from each region.
Scatter Plots: Ideal for showing the relationship between two different numeric variables. Use it to see if there's a correlation between a product's price and the number of units sold.

Don’t forget to give your chart a clear title and label your axes. The goal is for someone to understand the key message within a few seconds of looking at it.

Final Thoughts

From cleaning and preparing raw data to using sophisticated formulas or pivot tables, Google Sheets provides a comprehensive and accessible toolkit for data analysis. By mastering these core features, you can move away from guesswork and start making informed, data-driven decisions for your business without needing expensive or complex software.

While Sheets is incredibly versatile for analyzing a single CSV, the real challenge often comes from manually gathering all that data in the first place. Exporting reports from Google Analytics, Shopify, Facebook Ads, and your CRM just to copy-paste them into a spreadsheet takes hours. That's why we built Graphed . We connect directly to all your data sources, keeping everything in one place and updated in real-time. Instead of wrangling CSVs, you just ask questions in plain English - like "show me which ad campaigns are driving the most Shopify sales this month" - and get a dashboard instantly.