6 Ways to do Data Analysis in Google Sheets

Last Updated on February 24, 2024 by Jake Sheridan

Need to make some decisions backed up with actual data? In this tutorial you will learn how to do data analysis in Google Sheets

Can Google Sheets do Data Analysis?

Yes, Google Sheets can be used to perform data analysis. It is in fact a popular tool for data analysis since it runs completely within your browser and allows you to collaborate with other users.

As a spreadsheet tool, it offers a variety of built-in tools and functions for making sense of your data. These include formulas such as SUM and AVERAGE and tools such as pivot tables and a variety of charts and graphs.

For more advanced data analysis, Google Sheets also supports third-party add-ons and custom scripts through the Google Apps Script editor.

So, how can spreadsheets be used for data analysis?

Spreadsheet are an easy-to-use interface to view, edit, and aggregate data. Google Sheets is no different.

It includes a variety of functions and tools you can use to perform various data analysis tasks such as:

  • Sorting and filtering data
  • Finding and removing duplicate values
  • Summarizing and aggregating large datasets
  • Arranging, adding, and deleting columns and rows from your data
  • Creating appropriate data visualization in the form of charts and graphs
  • Using statistical functions to find trends and patterns in your dataset.

Let’s look at some practical ways to use Google Sheets for analyzing data:

6 Ways to Use Google Sheets for Data Analysis

1. Using Charts and Graphs

Using charts and graphs in Google Sheets is an effective way to visualize and present your data.

To begin adding a chart into Google Sheets, first open the document containing the data range you wish to create a chart from. Select the entire range containing your data.

In this example, we want to visualize the trend of two different metrics across a single year.

Head to the “Insert” menu at the top of the Google Sheets interface and select Chart from the dropdown menu. Google Sheets will automatically insert a chart based on your selected data. By default, it tries to choose the chart type it thinks best fits your data.

In this case, Google Sheets generated a combo chart based on my selected range.

To change the chart type, we can head to the Setup tab in our chart editor pane on the right-side of the screen. If you can’t find the pane, double-click on the generated chart to bring it up.

You’ll see a dropdown menu labeled “Chart type.” Click on it to see all the available chart types (e.g., Column, Line, Pie, Bar, Scatter, etc.).

Select the chart type that best represents your data. Your chart will automatically update to reflect the selected type.

In the “Chart editor” pane, switch to the “Customize” tab to access various customization options.

Here, you can modify elements such as the chart title, axis titles, legend, font size, colors, and more, depending on the type of chart you’ve chosen.

To move your chart, click on it and drag it to the desired location within your sheet.

To resize the chart, click on it, then click and drag one of the square handles that appear around the chart’s border.

2. Using the Power of Conditional Formatting

Conditional formatting is a feature in Google Sheets that allows you to automatically apply formatting—such as colors, fonts, or borders—to cells based on the contents of said cells. Adding conditional formatting rules allows users to visually emphasize important information, identify trends, and make comparisons at a glance.

Types of Conditional Formatting in Google Sheets

Google Sheets offers two main conditional formatting options to help you visually organize and analyze your data: single-color formatting and color scale formatting.

With single-color formatting, we follow boolean logic where a cell is formatted a certain way depending on whether a condition evaluates to true or false. We can use conditions such as whether the cell contains values that are greater than, less than, or equal to a certain number, or matching a particular text or date. This type of conditional formatting is best used when you want to highlight cells that meet a certain condition.

With color-scale formatting, we use a gradient or range of colors that correspond to a range of colors. The colors typically range from one extreme (e.g., red for low values) to another (e.g., green for high values). Users can also set up a color for some midpoint value. It’s best used when you want to visualize a range of values to quickly identify high, medium, and low values in your data.

Setting Up Single-color Conditional Formatting

Let’s try setting up a single-color formatting rule to a range of cells.

In this example, we want to highlight all values in a range that are lower than 75.

First, select the range of cells you want to add conditional formatting to. You can click and drag to select multiple cells, or use Ctrl+A (Cmd+A on Mac) to select the entire table if necessary.

In this example, we want to select the range of scores found in the cell range A2:D10.

Next, head to the “Format” menu at the top of Google Sheets, and then click on “Conditional formatting” from the dropdown list.

This will open the Conditional Format Rules panel on the right side of your screen.

In the Single color tab, ensure that the range under Apply to Range is correct.

In the Format cells if dropdown menu, select the condition that you want to use for your rule. Since we want to highlight cells that have values below 75, we’ll choose the option Less than.

When selecting a comparison condition like “Less than” or “Text contains,” you’ll need to specify the value or text to compare in the field that appears below the dropdown. In this case, we’ll specify we want to check if the cell’s contents is less than 75 by typing 75 in the provided field.

Once we’ve set the condition, we’ll need to choose the formatting style you want to apply when the condition is met. You can change the text color, cell background color, and other formatting options like bold or italic text.

For this example, we’ll just change the cell’s background color to a red background.

Click Done to add the new conditional formatting rule to the selected range.

After setting up your conditional formatting rule, the changes will apply automatically as you adjust the data in your sheet. You can close the Conditional Format Rules panel by clicking the “X” in the top right corner or clicking anywhere outside the panel.

If you need to edit or delete existing rules, simply reopen the Conditional Format Rules panel by going to “Format” > “Conditional formatting”, and then find the rule you want to modify or delete.

Click on the Trash icon to delete an existing rule.

Setting Up Color Scale Conditional Formatting

To add color scale or gradient formatting to a range, we’ll need to use the Color scale tab in the Conditional format rules panel.

You can click on the Preview color scale to browse through several preset color scale options.

Alternatively, you can create your own gradient by adjusting the colors of the minpoint, midpoint, and maxpoint.

Each point can be calculated in one of four ways:

  • min/max value – The minpoint or maxpoint is automatically decided based on
  • Specific number – The point is selected based on a user-defined number.
  • Percent – The bottom x or top x percent will be formatted a certain way
  • Percentile – the bottom x or top x percentile will be formatted a certain way.

After setting up the data points and their corresponding colors, click Done to apply the new rule.

The range should now be formatted as a gradient, allowing the user to see at a glance the low, mid, and high values in the given range.

3. Creating Filters

Have you ever needed to narrow down a dataset so that you can only see a specific subset of records? For example, given a table of order data, you may want to check all orders from a specific region or time period.

Filters in Google Sheets are a powerful feature that allows users to view and analyze data more efficiently by displaying only the rows that meet certain criteria. They enable users to hide data that doesn’t match your filter conditions without actually deleting or altering the original data.

Setting Up a Filter in Google Sheets

Start by opening the Google Sheet that contains the data you want to filter.

In this example, we have a table containing daily revenue broken down by region and date.

Select the cell range where you want to apply a filter.

In this example, we’ll select the top-most cell in our table and use the keyboard shortcut Ctrl+A (Cmd+A for Mac users) to select the range A1:C363.

Next, go to the “Data” menu at the top of your screen. Click on “Create a filter.”

You’ll notice filter icons (which appear as an upside-down triangle made of horizontal lines) on the top cell of each column in your selected range, indicating that a filter has been applied to that column.

Click on the filter icon in the header of the column you wish to filter by. This will open a dropdown menu.

To filter by specific values, click on Clear, then scroll through the list and check the values you want to display. You can also use the provided search box to look for specific data points.

Click “OK” to apply the filter.

For example, we can use the filter dropdown menu to filter out all records from Los Angeles.

When a filter criteria is active, the funnel icon will now appear as a solid green funnel.

You can sort the data alphabetically or numerically by choosing “Sort A to Z” (in ascending order) or “Sort Z to A” (in descending order).

For more advanced filtering options (such as filtering by conditions like “greater than,” “less than,” “contains,” etc.), select the “Filter by condition” option, choose your condition, and specify any necessary values or criteria.

To remove the filter, right-click on any cell in the filtered range and select Remove filter.

Creating a Filter View

A filter view in Google Sheets is a feature that allows users to apply filters to a dataset without affecting how other users view the data. It creates a personalized view where you can sort or filter data according to your needs, and this view can be saved for future access.

The key aspect of filter views is that they are individual to each user; multiple users can create their own filter views in the same spreadsheet without interfering with each other’s work.

Let’s try to create a new filter view for a sample dataset.

Open the Google Sheets document containing the data you wish to create a filter view from. Select Data > Filter views > Create new filter view.

After creating the filter view, you can now adjust the filter criteria for your data.

We recommend including a descriptive name for your filter view to allow other users to know the purpose of that particular filter view.

You can click on the Close button above the filter view to close the filter view and return to the original unfiltered data.

You should now be able to access the filter view you’ve created by clicking Data > Filter views.

4. Using the SUM Function in Google Sheets

A common way to aggregate data for analysis is finding the sum of a range of values. This operation can help understand the overall magnitude of our data such as finding the total sales or expenses.

Google Sheets includes a built-in SUM function which allows us to find the total of one or more cell ranges containing numerical data.

In addition, Google Sheets includes the SUMIF and SUMIFS functions that allow the user to sum a range based on certain conditions.

Using the SUM Function

The SUM function calculates the total of all numerical values in a range or list of arguments.

It follows the following syntax:

=SUM(value1, [value2, …])

The arguments for the SUM function can be a cell reference, cell range, or a valid numerical value.

For example, when evaluating the formula =SUM(100,150,120), we’re able to return a value of 370.

Instead of using numbers as input, we can instead input a cell range to total.

In the example above, we used the formula =SUM(A1:A3) to find the sum of the numerical values in the range A1:A3.

To add non-adjacent cells, we can include additional cells or ranges by typing a comma in our SUM function and adding a new cell reference argument.

In the example above, we used the SUM function to total the ranges A1:A3 and C1:C3.

Using the SUMIF Function

In some instances, we may only need to find the total of a specific subset of a range. For example, we may want to find the total amount of all transactions on a specific date or from a specific customer.

Luckily, Google Sheets comes with a SUMIF function that allows us to only add values that fit a specific condition.

The SUMIF function uses the following syntax:

=SUMIF(range, criterion, [sum_range])

Let’s look into each of these arguments:

  • Range refers to the range of cells that you want to apply the condition to.
  • Criterion refers to the condition that cells in the specified range have to meet for their corresponding cells in the sum_range argument to be summed. The criterion argument can be a number, expression, or text.
  • sum_range refers to the range of cells to sum together. If omitted, Google Sheets will automatically find the total of the cells specified in range.

Let’s look into a simple example where we can use the SUMIF function.

Suppose we have a table containing different products and their corresponding sales in the past week.

We want to find the total number of sales for products under the Laptop category. To do this, we can use the following formula:

=SUMIF(B2:B16,”Laptop”,C2:C16)

The formula above finds the sum of all sales values in column C as long as the corresponding value in column B is equivalent to “Laptop”.

For SUMIF, the criterion can include logical operators (like >, <, >=, <=, <>) for numeric conditions or wildcards (*, ?) for text conditions.

For example, we can use the criterion “*Accessory*” to only add sales coming from categories containing the word “Accessory”.

If a sum range is not provided, Google Sheets will automatically use the range input as the cell range to sum up. This often comes up when the sum range is also the range you’re checking the condition of.

For example, the table above uses the formula =SUMIF(C2:C16,”>20”) to add up all sales from items with over 20 sales in the given time period.

Using the SUMIFS Function

The SUMIFS function allows you to specify two or more conditions to check when calculating the sum of a range.

The SUMIF function uses the following syntax:

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

Let’s look into each of these arguments:

  • sum_range refers to the range of cells to sum together.
  • criteria_range1 refers to the range to check against criterion1
  • criterion1 refers to the pattern or test to apply to criteria_range1
  • criteria_range2, criterion2, and so on are optional arguments that specify the additional ranges and criteria to check.

For example, we can use the following formula to find the the sales of all laptop products that are currently out of stock:

=SUMIFS(D2:D16,C2:C16,TRUE,B2:B16,”Laptop”)

The first argument specifies that we want to find the sum of values under the sales field of our table. The next pair of arguments specify we want to only add sales from products that are out of stock (where the value in column C is TRUE). The last pair of arguments then specify that we are only concerned with sales from laptops (where the value in column B is “Laptop”).

5. Using the COUNT, COUNTIF, and Other COUNT functions

One of the first steps in analyzing data in Google Sheets is understanding the magnitude or size of your dataset. The simplest way to check is to count how many records there are in your spreadsheet.

Google Sheets offers a suite of COUNT functions that allow you to count cells. These functions are useful for data analysis, especially when you need to quantify entries based on various conditions.

Let’s go over each of the available Google Sheets functions you can use to count cells in Google Sheets.

Using the COUNT Function

The COUNT function counts the number of cells that contain numbers in a given range.

The function follows the following syntax:

=COUNT(value1, [value2, …])

Let’s try using the COUNT function to count the number of numbers in a specific column of our spreadsheet.

First, click on the cell where you want to display the count.

Type “=COUNT(“ followed by the range of cells you wish to count. Press Enter to see the count.

For example, =COUNT(A1:A16) counts the number of cells with numbers in the range A1 through A16.

Using the COUNTA Function

The COUNTA function counts the number of values in a specific range. Unlike COUNT, the COUNTA function counts all values in a dataset (including text values).

The function follows the following syntax:

=COUNTA(value1, [value2, …])

Let’s try using the COUNTA function to count the number of numbers in a specific column of our spreadsheet.

In the example above, we used the formula =COUNTA(A2:A16) in cell D2 to count the number of values.

Notice that compared to the COUNT function in cell C2, the COUNTA function included the two cells with text values in the total count.

Using the COUNTUNIQUE Function

In some cases, we only want to know the total number of unique values that appear in a given range. For example, if we had a sales dataset with a product_id column, we may want to know the total number of unique product_ids appear in our data.

We can use the COUNTUNIQUE function to count the total number of unique values in a list of values or range. The function follows the following syntax:

COUNTUNIQUE(value1, [value2, …])

Each argument in COUNTUNIQUE could be a specific value or range to consider for uniqueness.

In the example above, we used the formula =COUNTUNIQUE(B2:B216) to determine the total number of unique colors in our dataset of T-shirt orders.

Using the COUNTBLANK Function

While the prior COUNT functions allow us to determine how many values appear in our dataset, we can use the COUNTBLANK function to determine the number of empty cells in a given range.

This could be useful during the data cleaning step since it’s important to know if a certain field has too many missing values.

The COUNTBLANK function uses the following syntax.

COUNTBLANK(value1, [value2,…])

Let’s look at a sample use case for the COUNTBLANK function.

Suppose we have a column in our orders tracker that contains the status of a particular order. Since our tracker is manually updated, there may be cases where the status has not been filled out. We want to return the total number of records containing an empty cell in the status field.

In cell F2, we’ll use the formula =COUNTBLANK(D2:D16) to determine the number of records in our tracker have missing status values.

Using the COUNTIF Function

The COUNTIF function allows the user to calculate a conditional count across a range.

For example, you may want to count the total number of orders from a particular location or with a specific order status.

Here’s the syntax for the COUNTIF function:

=COUNTIF(range, criterion)

The range argument refers to the range that is tested against a specific criterion. The criterion argument refers to the pattern or condition to apply to range.

What criterion to use for COUNTIF will depend on whether your range contains numerical data or text data.

For example, we can use the COUNTIF function to find the total number of orders in our tracker with a status of “Complete”.

Using the formula =COUNTIF(D2:D16,”Complete”), we find out that there are seven complete orders in our dataset.

We can also use COUNTIF to handle ranges with numerical data.

In the example above, we used the function =COUNTIF(C2:C16,”>=15″) to determine the number of orders with a quantity greater or equal to 15.

Using the COUNTIFS Function

Similar to COUNTIF, the COUNTIFS function allows you to perform a conditional count on a range. However, the COUNTIFS function enables you to use two or more criteria or conditions.

The function uses the following syntax:

Syntax: =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, …])

Let’s look at a sample use case for the COUNTIFS function.

Suppose we want to know the total number of completed orders where the value in quantity is greater than or equal to 15.

In the example above, we’ve determined that there are two orders that meet our criteria using the following formula:

=COUNTIFS(D2:D16,”Complete”,C2:C16,”>=15″)

The first two arguments set the condition that the order must have a complete status in column D. The next two arguments set the condition that the order must have a quantity greater than or equal to 15.

6. Using the SUBTOTAL Function

The SUBTOTAL function in Google Sheets is a versatile built-in function that allows you to perform various calculations, such as SUM, AVERAGE, COUNT, etc., on a range of cells. This function is particularly useful in data analysis and reporting, as it provides dynamic calculations that automatically adjust to the visible data set.

Given a filtered dataset, we can use the SUBTOTAL function to summarize our data while ignoring data that has been filtered out. As the user adjusts the filter criteria, the SUBTOTAL function will automatically update to reflect what’s currently visible.

The syntax for the SUBTOTAL function is as follows:

=SUBTOTAL(function_code, range1, [range2, …])

The function_code argument controls what type of aggregate we want to calculate. Here’s the available aggregate functions we can use and their corresponding function code:

  • AVERAGE (1)
  • COUNT (2)
  • COUNTA (3)
  • MAX (4)
  • MIN (5)
  • PRODUCT (6)
  • STDEV(7)
  • STDEVP (8)
  • SUM (9)
  • VAR (10)
  • VARP (11)

Do note that you can choose to skip hidden values by increasing the number by 100. For example, a function code of 102 will run the COUNT function while skipping hidden cells.

Let’s take a look at a simple use case for the SUBTOTAL function.

In the spreadsheet above, we have a dataset of credit card transactions. We have applied a filter to the range to only show transactions made after January 1st, 2022. Using this filter criteria, we want to know the total amount of all visible transactions.

We can use the following formula to find this sum:

=SUBTOTAL(9,B2:B26)

We’ve selected the function code of “9” to specify that we want to aggregate the range as a sum.

When we try changing the filter criteria of our filtered dataset, the SUBTOTAL function will automatically recalculate the sum.

We can change the function code to find different aggregate values for the range. For example, setting the function_code input to “2” will allow us to find the total count. This is useful when we need to know the total number of records that match a particular criteria.

You can also use SUBTOTAL to perform calculations on multiple ranges by adding more ranges after the first range, separated by commas.

In the example above, we were able to calculate the total number of records from two separate tables. This setup can be helpful when handling multiple ranges with different filter criteria.

What is the advantage of using Google Sheets for data analysis?

Are you still deciding whether Google Sheets is the right tool for your data analysis needs? Here are some of the advantages you can expect when choosing Google Sheets as your data analytics tool.

  • User-friendly: Google Sheets uses a familiar and intuitive interface that makes it easy for users of any level of expertise to start performing data analytics with their data. Google Sheets users will also benefit from the extensive documentation and tutorials and templates from the community if they’re stuck on a particular issue or need help setting up a particular workflow.
  • Accessibility: Since your Google Sheets documents are stored in the cloud, your data and insights can be accessed from anywhere on any device. This makes it the perfect tool for remote setups and for use cases where you’ll need to access your data on multiple devices or offices.
  • Collaboration: Google Sheets is the leading spreadsheet tool for online collaboration. Sharing your documents allows multiple users to work on the same sheet simultaneously.
  • Cost-effective: For individuals or small teams, Google Sheets is a great option since it is free to use. Businesses who subscribe to a Google Workspace plan will also have access to Google Sheets for their organization.

Don’t forget about integrations…

Google Sheets has built-in integration with other Google Workspace apps such as Google Drive, Google Forms, and Google Docs. Google Sheets also provides a variety of ways to import data with built-in functions like IMPORTDATA, IMPORTXML, and IMPORTFEED.

The platform also supports a wide range of third-party add-ons through the Google Workspace Marketplace.

More advanced users can create custom functions and automation scripts for their sheets using the Google Apps Script editor.