In this article:

May 8, 2024

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.

Follow the steps below to make a correlation matrix 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.

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.

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.

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

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

We'll email you 1-3 times a week — and never share your information.

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