In this article:

How to Make a Correlation Matrix in Google Sheets (2024 Update)

May 8, 2024

Correlation Matrix in Google Sheets

A correlation matrix is a table showing correlation coefficients between sets of variables. Each cell in the table shows the correlation between two variables. The value of a correlation coefficient ranges from -1 to 1, where:

  • 1 indicates a perfect positive correlation, meaning that as one variable increases, the other variable increases at a constant rate.
  • -1 indicates a perfect negative correlation, meaning that as one variable increases, the other variable decreases at a constant rate.
  • 0 indicates no correlation, meaning that there is no linear relationship between the two variables.

The matrix is symmetric, with the same correlation coefficients appearing above and below the diagonal, and the diagonal itself always contains 1s, since each variable is perfectly correlated with itself.

How to Make a Correlation Matrix in Google Sheets

Follow the steps below to make a correlation matrix in Google Sheets. 

1. Enter Your Data in Google Sheets

First, ensure all the variables you want to analyze are listed in Google Sheets. Arrange each variable in its column, and make sure each column has a header. For a correlation matrix, you'll need at least two variables.

correlation matrix google sheets

2. Select a Starting Cell for the Correlation Matrix

Select an empty cell where your correlation matrix will begin. Ensure there's enough space from your dataset to avoid overwriting any data. For this dataset, you might start in cell E1 or A13.

correlation matrix in google sheets

3. Input the CORREL Function for One Pair of Variables

To calculate the correlation between "Hours Studied" and "Test Score", type =CORREL(B2:B11, C2:C11) into your chosen cell. This formula computes the correlation between these two variables.

google sheets correlation matrix

4. Fill in the 3x3 Grid with CORREL Formulas for All Variable Pairs

Copy the first formula across and down to fill a 3x3 grid. Adjust the formula for each cell to match the variable pairs you're comparing. For instance, to compare "Hours Studied" and "Test Score", the formula in the next cell would be =CORREL(B2:B11, C2:C11). Repeat this process for each pair, ensuring each variable is compared with every other variable, including itself (which will always give a correlation of 1).

how to create a correlation matrix in google sheets

5. Analyze the Correlation Coefficients in the Matrix

Look at the correlation coefficients. A value close to 1 or -1 means a strong positive or negative correlation, respectively. Values around 0 indicate a weak correlation. Analyze how variables like "Hours Studied" relate to "Test Score" and "Breaks Taken".

We hope that you now have a better understanding of how to make a correlation matrix in Google Sheets. If you enjoyed this article, you might also like our article on how to insert Google Sheets Harvey Balls or our article on how to use the Google Sheets inflation formula.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->