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

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.

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.

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.

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).

‍

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.