Correlations can offer insight into whether there is a predictive relationship between x and y planes, but they do not necessarily indicate causality. Here’s how you can use Google Sheets to identify correlations in your data.

What Is Correlation, and What Can You Use It For?

When two variables are correlated, one can determine how a variable impacts the other. But, it’s crucial to remember that correlation doesn’t imply reliance in this case; it just shows how closely and quickly two variables compare or relate.

Statistical metrics aid in the comprehension of underlying trends in data analysis. Correlation is among the most often used statistical metrics, and it determines how closely related or dependent two variables are.

Understanding Correlation Coefficient in Google Sheets

In Google Sheets, a correlation coefficient is calculated using the CORREL function. The correlation coefficient serves as a gauge of how closely connected the sets of data are. It may be used to get the Pearson product-moment correlation coefficient (r) if you have two variable sets of data. If you’d like to learn more about this coefficient, you can read more in this guide from Statistics at Leard.

One could come across one of three correlation metrics. Every measurement defines the relationships between variables differently. Its value is between -1 and +1:

-1 denotes a perfect negative correlation: When a correlation does have a coefficient of correlation that is equal to or below -0. 9, it’s stated to be strongly negative. It’s an indication that data is correlating. However, the variable x keeps increasing, whereas variable y keeps decreasing. 0 denotes no connection: Variables are considered not to correlate if the correlation coefficient is greater than 0. 01 but less than 0. 1 since there isn’t a discernible relationship between each variable. They are independent of one another. +1 denotes a perfect positive correlation: When a correlation’s coefficient falls between 0. 9 and 1, it is deemed highly positive. It indicates that there has been an increase in the two variable sets.

The highest value of a coefficient may have been a correlation coefficient of 1. When the correlation value is 1, it implies that if you were to graph the data, it would be completely aligned to create a straight line.

If you’re still a little lost, don’t worry. We will explain the syntax of the CORREL function and then dive into some real-world examples to help you understand it better. Understanding lines of best fit and how to make trendlines in Google Sheets will help you with this.

CORREL Function Syntax in Google Sheets

Let’s break this down into its parts and better understand what every phrase means:

=CORREL: This is the Google Sheet function that determines r, (the dataset’s Pearson product-moment correlation coefficient). data_y: This refers to the group of cells containing the dependent data or the range of values for those cells. data x: This is either a reference toward the array of cells with the independent data or the range of values for those cells.

If you were to graph the data points, data_y would be the Y axis and data_x the X axis. You’ll notice that there’re two different ways to enter the range of data. Either a reference cell range or straight data entry in the function are options.

In most cases, the use of reference cells range is preferable. That’s because the spreadsheet most likely already contains your data. Using a reference cell range can avoid excessive typed input that may result in user error.

CORREL Function Examples in Google Sheets

Let’s check out a few examples to comprehend how to use the CORREL function in Google Sheets.

Example 1: A Strong Positive Correlation

For this first example, let’s pretend we are working in the real estate industry. In the spreadsheet below, we have partitions of hectares of land that you are selling and the number of units sold of those different lands’ data in your Google Sheet.

If you’re following along in a sheet, you’ll start by keying in the data of the variables into your spreadsheet, as shown below: Click cell C2 Type =CORREL( You will then proceed to type data_y, which in our case, is the referenced cell range A2:A6, then enter a comma. Proceed to type data_x, which in our case, is referenced as B2:B6. End with a closing bracket, as shown below: Lastly, press Enter to return the correlation coefficient of the two pieces of data in cell C2.

Using the example just shown above, you have obtained a correlation coefficient of 0.90, which is a strong positive correlation since its value falls between 0.9 and 1. Therefore, this indicates that as y changes, x also changes in a substantially comparable manner.

Below is a representation of our example sample data on an XY scatter graph.  As you can see, the line of best fit is close to the data points on the graph, which supports the idea that the figures are heavily correlated.

You can learn more about creating XY scatter graphs in Google Sheets in our other article.

Example 2: A Weak Negative Correlation

This time, we will use a more generic example of “variable x and y” in our spreadsheet. We have purposefully included figures to demonstrate a negative correlation, which the CORREL function demonstrates below:

There is not a strong relationship between variables y and x, so the result we get is a less significant correlation coefficient than in the previous example. The result we achieved is -0.47. This does not, however, imply that there is no correlation at all. Let’s look at a line of best fit again to make sense of it.

As you can see by the scatter plot, the data points are further from the line of best fit. There is, therefore, less correlation than in the first example, but not none. You’ll also notice that the line of best fit is decreasing. This shows a negative correlation, one value decreases as the other increases.

Example 3: No Connection

Here we have a set of completely random numbers. Let’s quickly touch on how to use the CORREL function again:

Type in Cell C2 the CORREL formula Our arguments are A2:A10 and B2:B10 Hit Enter

The value returned on C2 is 0.02. If a correlation coefficient falls between 0.01 and 0.1, it is determined that the variables in question do not correlate since there is no discernible connection between them. The relationships between variables are entirely independent.

Below is the representation of the same on a scatter graph. The line of best fit is almost flat, indicating little correlation between the two data sets.

Easily Correlate Your Data in Google Sheets

Correlation could be a challenging topic if you didn’t work much with it in high school. This guide covered all the basics, but you’ll need to keep using the CORREL function in Google Sheets to keep it fresh in your mind.

This is a powerful function as it helps avoid building scatter graphs and can quickly find trends in your data. Still, don’t be afraid to add charts to help other users better understand the data in your spreadsheets.