A table that captures the correlation coefficients between different variables
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
A correlation matrix is simply a table that displays the correlation coefficients for different variables. The matrix depicts the correlation between all the possible pairs of values in a table. It is a powerful tool to summarize a large dataset and to identify and visualize patterns in the given data.
A correlation matrix consists of rows and columns that show the variables. Each cell in a table contains the correlation coefficient.
In addition, the correlation matrix is frequently utilized in conjunction with other types of statistical analysis. For instance, it may be helpful in the analysis of multiple linear regression models. Remember that the models contain several independent variables. In multiple linear regression, the correlation matrix determines the correlation coefficients between the independent variables in a model.
How to Create a Correlation Matrix in Excel?
In order to understand the necessary steps in creating a correlation matrix in Excel, let’s consider the following example. You are the stock analyst in the investment bank. Your manager recently asked you to analyze the correlations between the prices of stocks that can be potentially added to the portfolio. You then analyze the stocks of the following companies: NVIDIA, Ford, Shell, and Alphabet.
The best way to analyze the correlations between the stock prices of the abovementioned companies is to create a correlation matrix. It can be done through the following steps:
Download the data into Excel and arrange the data into the columns.
Click Data -> Data Analysis -> Correlation.
Enter the input range that contains the name of the companies and the stock prices.
Ensure that Grouped By: Columns option is chosen (because our data is arranged in the columns).
Ensure that Labels in First Row option is chosen (the first rows of each column contain the names of the companies).
Choose the desired output option (i.e., the location on the spreadsheet where the correlation matrix will appear).
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.