The dataset contains students’ marks in different Exams.

### Step 1 – Enable the Data Analysis Tool

- In the
**Data**tab, enable**Data Analysis**. - Click
**File**and choose**Options.**

- Click
**Options.**

- Go to
**Add-ins,**select**Excel Add-in**in**Manage**. - Click
**Go.**

- In the new window, check
**Analysis ToolPak.** - Click
**OK.**

### Step 2 – Use the Data Analysis Tool to Create a Correlation Matrix

- Go to the
**Data**tab and select**Data Analysis**.

- Select
**Correlation**. - Click
**OK**.

- In the
**Correlation**window, choose the input range: your dataset. - Click
**Columns**in**Grouped By**to group the data by columns. - Check
**Labels in the first row**if your data has table headers or labels. - Choose where to place your output in
**Output Options.**Here, a new worksheet: enter the sheet name. - Click
**OK**.

- A new worksheet will open. The dataset is displayed with the correlation values.

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

### Step 3 – Using the Conditional Formatting to Create a Correlation Heatmap

- In the
**Home**tab, select**Styles**. - Click
**Conditional Formatting**and select**New Rule.**

- In the new window select
**Format all cells based on their values**in**Select a Rule Type.** - Change
**Format Style**from 2 to 3.

- In Minimum, select
**Number**. - In
**Values**, enter -1. - Choose
**Red.** - In Midpoint, select
**Number**. - In
**Values**, enter 0. - Choose
**White.** - In Maximum, select
**Number**. - In
**Values**, enter 1. - Choose
**Blue.** - Click
**OK**.

A color-coded heatmap is displayed.

**Read More:** How to Make Correlation Graph in Excel

### Step 4 – Output Interpretation

The correlation coefficient indicates how the variables relate to each other. The heatmap offers an overview of the coefficients distribution and their intensity.

The more positive the value towards +1, the better the relation between variables. If one of those values increases, the other value also increases. The color code is as blue: the bluer the cell, the better the symmetrical relation.

The more negative value toward -1, the worse the relation between variables. If a variable increases, the other is likely to decrease. The color code is red. The worse the correlation value, the redder the cell color. **E4** and **E9 **show this type of negative relation.

**Read More: **How to Interpret Correlation Table in Excel

## How to Create a Dynamic Correlation Heat Map in Excel

### Step 1 – Create a Correlation Dataset

- Use the correlated dataset created in the previous method.

### Step 2 – Add a Checkbox

- In the Developer tab, click
**Insert**. - Click the
**Check Box**icon.

- A
**Check Box**will be displayed. - Edit and resize it.

- Select the box and right-click.
- Click
**Format Control****.**

- In the new window, enter the cell that will be linked to the box.
- Click
**OK**.

- “
**FALSE**” is displayed - Clicking the check box will toggle the value of
**L4**between**TRUE**and**FALSE**.

### Step 3 – Apply the Conditional Formatting

- Select the dataset.
- In the
**Home**tab, select**Style.** - Click
**Conditional Formatting.** - Choose
**New Rule.**

- In the new window, select
**Format all cells based on their values**in**Select a Rule Type.** - Change
**Format Style**from 2 to 3. - In Minimum, select
**Formula**. - Enter the following formula in
**Value (Minimum)**:

`=IF($L$4=TRUE,MIN($C$5:$I$11),FALSE)`

- Choose
**Red** - In Midpoint, select
**Number**. - Enter
**0**in**Values**. - Choose
**White.** - In Maximum, select
**Formula**. - Enter the following formula in
**Value (Maximum)**:

`=IF($L$4=TRUE,MAX($C$5:$I$61),FALSE)`

- Choose
**Blue.** - Click
**OK**.

- The
**Conditional Formatting Rules Manager**window shows an overview of all rules. - Click
**OK**.

** Formula ****Breakdown**

**MIN($C$5:$I$11)**: returns the minimum value in**$C$5:$I$11**.**IF($L$4=TRUE, MIN($C$5:$I$11), FALSE):**If the value of**L4**is true, the minimum value of**$C$5:$I$11**will be the input value. Otherwise, there will be no value available to input and no formatting.**MAX($C$5:$I$61):**returns the minimum value in**$C$5:$I$11****IF($L$4=TRUE, MAX($C$5:$I$61), FALSE):**If the value of**L4**is true, the maximum value in**$C$5:$I$11**will be the input value. Otherwise, there will be no value available to input and no formatting.

**Read More:** How to Make a Correlation Matrix in Excel

### Step 4 – Interpreting the Output

The Correlation Heatmap is dynamic. When the cell value of **L4 **is **TRUE**, conditional formatting will be displayed.

- Clicking the check box, will hide the heat map, as shown below.

## Potential Problems with Correlation in Excel

There is only a linear relationship between two variables in the** Pearson Product Moment Correlation**. Your variables may be strongly related in another way (e.g. curvilinearly), and still have a correlation coefficient close to or equal to zero.

Pearson correlation cannot differentiate dependent from independent variables. If you have a correlation value of -0.413 ( indicating a negative correlation between variables), and shuffle the variable, the same correlation value is returned.

**The Pearson correlation coefficient **can provide a misleading overview of the relationship between two variables.

To rank two variable values, perform the **Spearman Correlation Coefficient **analysis.

**Download Practice Workbook**

Download the practice workbook.

## 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 Calculate Partial Correlation in Excel
- How to Make a Correlation Table in Excel

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