Tanjima Hossain
If you are trying to make a correlation table in Excel, then this article will help you a lot. A correlation table defines the relationship between different variables over a time period. It ranges between 1 and -1. A value of 1 represents the strongest relationship, whereas -1 represents the weakest relationship between the two variables.
So, let’s start with the main article to know more about the process of creating this table.
Download Practice Workbook
5 Ways to Make a Correlation Table in Excel
Here, we have the following dataset containing sales records of Laptops, Mobile, and Routers for different years. In the following 5 methods, we will show the ways to create a correlation table for demonstrating a relationship between sales values among these products.
For creating this article, we have used Microsoft Excel 365 version. However, you can use another version according to your necessary.
Method-1: Using Analysis Toolpak to Make a Correlation Table in Excel
In this method, we will use analysis toolpak for creating a correlation table automatically. But prior to making this table, you have to activate this toolpak.
Steps:
- Go to File.
- Select Options.
Then, the Excel Options dialog box will open up.
- Click on the Add-ins tab, then select Excel Add-ins from Manage options, and finally press Go.
In this way, you will have the Add-ins window.
- Check the Analysis Toolpak option, and press OK.
Following this process, you will be able to enable the toolpak.
- Go to the Data tab >> Analyze group >> Data Analysis.
Afterward, the Data Analysis wizard will pop up.
- Choose the Correlation option, and press OK.
Then, the Correlation dialog box will appear.
- Select the Input Range as $C$3:$E$8, Output Range as $B$10.
- Choose the option Columns under Grouped By, check Labels in first row option, and finally, press OK.
After that, you will get a correlation table below your original dataset.
Read More: How to Create Table from Data Model in Excel (With Easy Steps)
Method-2: Applying CORREL Function to Make a Correlation Table in Excel
Here, we will be using the CORREL function to create the correlation table for the following dataset. For having the values of this table, we have created a format of this table below the dataset where the name of the products are written as column headers and row headers.
Steps:
- Type the following formula in cell C11.
=CORREL($C$4:$C$8, C$4:C$8)Here, $C$4:$C$8 is the first array that we have referenced absolutely to fix this range for this Laptop row. And C$4:C$8 is the second array which will be fixed by rows but varies with columns.
- Drag the Fill Handle tool to the right.
In this way, we completed the first row for the product Laptop.
- For determining relationships between Mobile and the other products use the following formula.
=CORREL($D$4:$D$8,C$4:C$8)
- Similarly, use the following formula for the row of the product Router.
=CORREL($E$4:$E$8,C$4:C$8)
Read More: How to Create a Table Without Data in Excel (2 Easy Methods)
Method-3: Use of CORREL and TRANSPOSE Functions
In this method, we will use the combination of the TRANSPOSE and CORREL functions for creating the correlation table of the products of the following dataset.
Steps:
- Press CTRL+C after selecting the dataset to copy it.
- Select the cell where you will paste (here, it is B11), right-click here, and choose Transpose from the Paste Options.
Finally, our dataset will be transposed like the following figure.
- Type the following formula in cell G4.
=CORREL(C$4:C$8,$C12:$G12)Formula Breakdown
C$4:C$8 → it is the first array from the main dataset, where we have fixed the rows.
$C12:$G12 → This range represents the second array situated in the transposed dataset, where we have fixed the columns.
- Finally, drag the Fill Handle tool to the right and down.
As a result, the following correlation table will show up.
Similar Readings
- How to Create a Sensitivity Table in Excel (2 Effective Ways)
- Create a Table with Merged Cells in Excel (4 Easy Methods)
- How to Create a Table with Headers Using Excel VBA (2 Methods)
- Create a Table in Excel with Multiple Columns
- How to Make a Table in Excel with Lines (with Easy Steps)
Method-4: Combination of CORREL and OFFSET Functions to Make a Correlation Table in Excel
For creating the correlation table in another way, we will be using a combination of the OFFSET, and CORREL functions here.
In the table, where we will calculate the correlated data, we have put down the serial numbers from 1 to 3 above the column headers and beside the row headers.
Steps:
- Write down the following formula in cell D12.
=CORREL(OFFSET($B$3:$E$8,1, D$10,5,1), OFFSET($B$3:$E$8,1,$B12,5,1))Formula Breakdown
OFFSET($B$3:$E$8,1, D$10,5,1) → it is the first array, where $B$3:$E$8 is the main range, 1 is the row number by which the range will go down, D$10 is the column number by which the range will go to the right, 5 is the total row height to be extracted, and 1 is the column number to be extracted.
OFFSET($B$3:$E$8,1,$B12,5,1) → It will extract a range for the second array.
- Finally, drag the Fill Handle tool to the right and down.
In this way, you will create the correlation table.
Method-5: Utilizing ADDRESS and INDIRECT Functions with CORREL Function in Excel
Here, we will use the ADDRESS, INDIRECT, and CORREL functions for doing the same task as the previous methods.
For this method, we have to define the start range and end range of the three columns for the products.
Steps:
- Put down the serial numbers of the columns for the products- Laptop, Mobile, and Router.
- Write down the following formula in cell H4.
=ADDRESS(4, G4)Here, 4 is the starting row number with a value in the Laptop column, and G4 is the column number.
- Drag down the Fill Handle.
- After having the starting ranges, put down the following formula in cell I4.
=ADDRESS(8, G4)Here, 8 is the ending row number with a value in the Laptop column, and G4 is the column number.
- Drag down the Fill Handle.
Finally, we have completed the ranges.
- Now, use the following formula in cell C11.
=CORREL(INDIRECT($H4&":"&$I4), C$4:C$8)Formula Breakdown
INDIRECT($H4&”:”&$I4) → it is the first array, where $C$4:$C$8 will be the range.
C$4:C$8 → This range represents the second array where we have fixed the rows.
- Drag the Fill Handle tool to the right and down.
Eventually, we created the following correlation table.
How to Make a Correlation Matrix with Colors in Excel
A correlation matrix is basically similar to the correlation table created in Method-1. But in this matrix, the cells are colored depending on the ranges.
- To highlight the cells of this table, select the range.
- Go to the Home tab >> Conditional Formatting dropdown >> New Rule.
Later, the New Formatting Rule dialog box will appear.
- Select the Format all cells based on their values
- Choose the Format Style as a 2-Color Scale (because we have a range between 0 and 1).
- Select the following options for Minimum and Maximum values and press OK.
Finally, the colors will appear, where Yellow is representing 1 and the values between 0 and 1 are represented through the gradient colors.
Practice Section
To practice by yourself, we have created a Practice section n the right side of each sheet.
Conclusion
In this article, we have discussed various ways to make a correlation table in Excel. Hope these examples will help you a lot. If you have any further queries, then leave a comment below.
Related Articles
- How to Make 3D Table in Excel (2 Suitable Ways)
- How to Make a School Time Table in Excel (with Easy Steps)
- Create a Table in Excel Based on Cell Value (4 Easy Methods)
- How to Create a Descriptive Statistics Table in Excel
- How to Create a League Table in Excel (4 Easy Methods)
- Excel VBA to Create Table from Range (6 Examples)
