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 learn more about the process of creating this table.

## How to Make a Correlation Table in Excel: 5 Handy Ways

Here, we have the following dataset containing sales records of ** Laptops**,

**, and**

*Mobile***for different years. In the following**

*Routers***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 the ** 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 **to createÂ a correlation table automatically. But prior to creating 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 Make Correlation Heatmap in Excel

__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 names 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 Calculate Autocorrelation in Excel

__Method-3__: Use of CORREL and TRANSPOSE Functions

In this method, we will use the combination of the **TRANSPOSE** and **CORREL **functions to create 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.

**Read More:** How to Calculate Partial Correlation in Excel

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

**Read More:** Find Correlation Between Two Variables in Excel

__Method-5__: Utilizing ADDRESS and INDIRECT Functions with CORREL Function in Excel

Here, we will use the **ADDRESS**, **INDIRECT**, and **CORREL **functions to do 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 **represents **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 on the right side of each sheet.

**Download Practice Workbook**

## 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 a Correlation Scatter Plot in Excel
- How to Calculate Cross Correlation in Excel
- How to Calculate Correlation between Two Stocks in Excel
- How to Do Correlation and Regression Analysis in Excel
- How to Interpret Correlation Table in Excel
- How to Make Correlation Graph in Excel

**<< Go Back to Excel Correlation | Excel for Statistics****Â |Â Learn Excel**