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

**Table of Contents**hide

## Download Practice Workbook

## 5 Ways to Make a Correlation Table in Excel

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