How to use Google Sheets for data analysis

Are you overwhelmed by endless rows and columns of spreadsheet data? Do you wish you could quickly find the answers hidden in those rows and columns?

Imagine being able to:

• Instantly see which product category drove the most sales last quarter.

• Quickly identify your top-performing customers.

• Create a clean, shareable report in minutes, not hours.

• Make informed decisions based on actual numbers, not just a gut feeling.

This isn’t just for analysts. This is a necessary skill for everyone these days, be it marketers, small business owners, project managers, or students. The good news? You already have the most accessible and powerful tool at hand to leverage this skill, that is, Google Sheets.

In this blog, we will walk through a practical Google Sheets data analysis tutorial from start to finish. In the process, we’ll take a raw dataset and turn it into actionable insights.

Five steps to use Google Sheets for data analysis#

Jumping into data analysis without structure can feel overwhelming and messy. That’s why breaking the process into clear, repeatable steps is essential—and with Google Sheets, you can follow these five simple steps to go from raw data to valuable insights.

Step 1: Import and organize your data#

You can’t build a house without a foundation; similarly, you can’t analyze data without a solid base. Whether you’re working with customer feedback, ad campaign results, or a list of inventory, the first step is to organize your information in Google Sheets.

• Create a new sheet: Open your browser and type sheets.new in the address bar. This will instantly open up a new, blank spreadsheet.

• Import or paste: You can copy your data from another source and paste it into cell A1 of your new sheet. If you have a CSV or Excel file, go to “File” > “Import.” Then, upload the file from your local setup or drive.

• Organize:

  • Freeze rows: To keep the first row (your header row) visible as you scroll through your data, go to “View” > “Freeze” > “1 row.” This will freeze the first row so you can always see what each column represents, no matter how far down you go.

  • Data types: Check that your data is formatted correctly. For example, columns with prices should be set to a currency format, and dates should be in a date format. This prevents calculation errors down the line. To check, select a column and go to “Format” > “Number.”

Sample data#

For this blog, we will use a dataset representing a small e-commerce business’s sales. This data contains common issues you’ll encounter in the real world, like inconsistent formatting and duplicates. Our goal is to transform this messy data into clear insights.

Step 2: Clean and prepare the data#

Raw data is never ready for analysis. This is where you step into your analyst role and start tidying things up. Before we start, let’s get familiar with a key tool: the formula bar.

It’s the long bar located right above your spreadsheet columns. Whenever you want to use a formula, you simply select a cell, and type your formula directly into this bar.

Now, let’s look at a few common issues you might run into.

• Fixing case and spaces: Notice JOE SMITH in cell C8 and furniture in cell E8? Inconsistent data can break your analysis.

  • Use =PROPER(C8) to capitalize names correctly.

  • Use =TRIM(C8) to remove any extra spaces.

  • Combine them into one as =PROPER(TRIM(C8)) is a powerful combination.

• Working with duplicates: Duplicate data is a common challenge that can distort analysis by inflating counts and totals. You have two main ways to deal with this, depending on your goal.

  • Handling duplicates (creating a unique list): If you want to create a clean list of unique items from a column (like a list of all customer names) without altering the original data, use the UNIQUE() function. Simply type the  =UNIQUE(C2:C13) formula into an empty cell to get a unique list of customer names. The function returns the first instance it finds, so the order of your original data matters.

  • Removing duplicates: If you have completely identical rows that you want to eliminate, use Google Sheets’ built-in tool. For example, in our sample dataset, rows with order IDs 1008 and 1008 are exact duplicates. Select the entire data range (e.g., B2:G13), then go to “Data” > “Data cleanup” > “Remove duplicates.” This will permanently remove the duplicate rows from your sheet, leaving you with a clean dataset.

• Splitting data: If Customer Name was Jane Doe, you could split it into First Name and Last Name using “Data” > “Split text to columns.” This is often used for full names or addresses.

Step 3: Essential functions for analysis#

With our data now free of duplicates and inconsistent formatting, it’s ready for analysis. We’ve laid the groundwork for accuracy, and now it’s time to explore the core formulas that will bring our data to life.

• Counting: While the simple COUNT() function can tell you the total number of items in a column, COUNTIF() is far more powerful because it allows you to count based on a specific condition. To find how many times Jane Doe placed an order, use the formula: =COUNTIF(C2:C12, "Jane Doe"). This formula counts how many times Jane Doe appears in the range C2:C12.

• Summing: Just like COUNTIF(), the SUMIF() function lets you ask a more specific question. While SUM() adds up all values in a range, SUMIF() allows you to add up values that meet a specific condition. For example, find the total revenue from a specific product category. To do this, use =SUMIF(E2:E12, "Electronics", F2:F12) formula. This will add up all the values in F2:F12 (Total Price) where the corresponding cell in E2:E12 (Product Category) matches Electronics.

• Averaging: While AVERAGE() calculates the average for all numbers in a range, AVERAGEIF() allows you to find the average of only the values that meet your criteria. To find the average order value for all orders from New York, use the formula: =AVERAGEIF(G2:G12, "New York", F2:F12). This formula averages all values in the F2:F12 range (Total Price) where the corresponding cell in G2:G12 (City) is New York.

• Looking up values: While SUMIF() and AVERAGEIF() let you calculate based on conditions, VLOOKUP() is used when you want to retrieve information from a table. Instead of manually searching, VLOOKUP() scans the first column of your range and returns the value from another column in the same row. For example, to look up a customer’s total order value, use: =VLOOKUP("Chris Miller", C2:G12, 4, FALSE). Let’s break this down:

  • Searches for Chris Miller in the first column of C2:G12 (Customer Name).

  • Moves across to the 4th column of that range (Total Price).

  • Returns the value, in this case, $1200.00.

  • The FALSE argument ensures only an exact match is returned. If you use TRUE instead, VLOOKUP() looks for an approximate match.

Step 4: Summarize data with pivot tables#

Pivot tables are a game-changer. They let you instantly summarize large datasets without writing a single formula. With just a few clicks, you can group orders by customer, calculate total revenue by product category, or see sales broken down by city and date.

• Create a pivot table: Go to “Insert” > “Pivot table.” At this point, Google Sheets will ask whether you want the pivot table on a new sheet or in the existing sheet. Choose whichever makes sense for your workflow. Once inserted, the “Pivot table editor” appears on the right.

Let’s find the total sales per product category:

• Select Product Category into the Rows section in the “Pivot table editor” section.

• Select Total Price into the Values section. Google Sheets will automatically set it to SUM.

• Add a filter: Want to see this for just a single city? Select City into the Filters section of the “Pivot table editor” and select a specific city (London).

Step 5: Visualize the insights with charts#

Numbers tell a story, but charts make that story easy to understand. Before you create a chart, ask yourself what you want to show. Want to compare categories? Use a bar chart. Want to show a trend over time? Use a line chart. Let your question guide your choice!

Once you know your goal, it’s time to turn your pivot table data into a visual story. Follow these steps:

• Select the pivot table data.

• Go to “Insert” > “Chart.”

• Choose the right chart:

  • Bar/column chart: This is great for comparing categories (e.g., sales per city). Let’s select a bar chart.

Once you’re comfortable with charts and pivot tables, the next step is to engage with your data on a deeper level. Use powerful descriptive statisticsDescriptive statistics help summarize and understand your data. Key functions include AVERAGE(), MEDIAN(), MODE(), STDEV(), STDEVP(), MIN(), MAX(), COUNT(), COUNTA(), QUARTILE(), SKEW(), and KURT(). These functions provide quick insights into the distribution, central tendency, variability, and shape of your dataset. functions to identify outliers, or create scatterplots to explore relationships between variables, such as how a customer’s location may correlate with their total spending.

These are the kinds of powerful insights that can inform your biggest business decisions.

Common mistakes and quick fixes#

When working with Google Sheets, even small mistakes can throw off your analysis or lead to misleading results. Understanding common pitfalls and how to address them can save time, and keep your data reliable.

• Pitfall: Formulas return an error (#N/A, #VALUE!).

  • Fix: Errors often happen because of typos, incorrect range selections, or mixed data types (like text in a number column). For example, summing a column with numbers stored as text will return an error or an unexpected result. Always check the cell format, and consider using functions like VALUE() to convert text to numbers.

• Pitfall: Data is inconsistent (for example, NY vs. New York).

  • Fix: Inconsistent data makes analysis tricky, especially when counting, summing, or filtering. Standardize your data using “Find and replace,” or create a helper column with functions like PROPER() to normalize text casing and TRIM() to remove extra spaces. For larger datasets, consider using ARRAYFORMULA() to apply cleaning across multiple rows at once.

• Pitfall: Formulas are becoming too complex.

  • Fix: Long, nested formulas are hard to debug and maintain. Break calculations into smaller steps using helper columns. For instance, calculate subtotals or intermediate metrics first, then reference these in your final formula. This approach not only reduces errors but also makes your workflow easier to understand and share with others.

By proactively addressing these common issues, you can ensure your Google Sheets analyses are accurate, efficient, and easy to maintain. This, in turn, helps you turn raw data into actionable insights with confidence.

Final thoughts#

You’ve seen how what starts as raw data in a spreadsheet can evolve into meaningful, strategic insights that inform real-world outcomes. From simple functions to pivot tables, filters, and charts, you now have a strong foundation to explore your data with confidence.

Data analysis isn’t just for experts; it’s a skill for anyone aiming to make smarter decisions, whether managing projects, tracking budgets, or growing a business. The real learning begins when you start practicing with your own data; the more you explore, the more insights you’ll uncover.