How do I create a correlation matrix in Google Sheets?

By /

To create a correlation matrix in Google Sheets, first organize your data in a table with columns representing different variables and rows representing different observations. Then, select all the data and go to the “Data” tab, click on “Create a Filter” and then click on the funnel icon in the column heading to open the filter options. From there, select “Correlation” and a correlation matrix will be generated showing the relationship between each variable. This matrix can be customized by changing the variables or adjusting the calculation method. The correlation matrix is helpful in identifying patterns and relationships between variables, making it a useful tool for data analysis.

Create a Correlation Matrix in Google Sheets

One way to quantify the relationship between two variables is to use the Pearson correlation coefficient, which is a measure of the linear association between two variables. It has a value between -1 and 1 where:

  • -1 indicates a perfectly negative linear correlation between two variables
  • 0 indicates no linear correlation between two variables
  • 1 indicates a perfectly positive linear correlation between two variables

The further away the correlation coefficient is from zero, the stronger the relationship between the two variables.

But in some cases we want to understand the correlation between more than just one pair of variables. In these cases, we can create a correlation matrix, which is a square table that shows the the correlation coefficients between several pairwise combination of variables. 

This tutorial explains how to create and interpret a correlation matrix in Google Sheets.

How to Create a Correlation Matrix in Google Sheets

Suppose we have the following dataset that shows the average numbers of points, rebounds, and assists for 10 basketball players:

To create a correlation matrix for this dataset, we can use the CORREL() function with the following syntax:

 COVAR(data_y, data_x) 

The covariance matrix for this dataset is shown in cells B15:D17 while the formulas used to create the covariance matrix are shown in cells B21:D23 below:

Correlation matrix in Google Sheets

How to Interpret a Correlation Matrix

The values in the individual cells of the correlation matrix tell us the Pearson Correlation Coefficient between each pairwise combination of variables. For example:

Correlation between Points and Rebounds: -0.0464. Points and rebounds are slightly negatively correlated, but this value is so close to zero that there isn’t strong evidence for a significant association between these two variables.

Correlation between Points and Assists: 0.1219. Points and assists are slightly positively correlated, but this value also is fairly close to zero so there isn’t strong evidence for a significant association between these two variables.

Correlation between Rebounds and Assists: 0.7137. Rebounds and assists are strongly positively correlated. That is, players who have more rebounds also tend to have more assists.

Notice that the diagonal values in the correlation matrix are all equal to 1 because the correlation between a variable and itself is always 1. In practice, this number isn’t useful to interpret.

Additional Resources

How to Read a Correlation Matrix
How to Create a Correlation Matrix in Excel

x

Related terms:

  1. How to Create a Correlation Matrix in Google Sheets
  2. How can I create a confusion matrix in Google Sheets?
  3. How do I create a covariance matrix in Google Sheets?
  4. How can I create a correlation matrix in Stata?
  5. How do I create and interpret a correlation matrix in Excel?
  6. How can I create a correlation matrix in SPSS?
  7. How can I create a correlation matrix in Python?
  8. How to Create a Covariance Matrix in Google Sheets
  9. How can I use corrplot in R to create a correlation matrix?
  10. How do I create a correlation matrix in PySpark?