How to Create a Correlation Heatmap in Excel – Quick Steps

The dataset contains students’ marks in different Exams.

Make Correlation Heatmap in Excel


Step 1 –  Enable the Data Analysis Tool

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

Enable Data Analysis Tool Add-in to make correlation heatmap

  • Click Options.

Enable Data Analysis Tool Add-in to make correlation heatmap

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

Enable Data Analysis Tool Add-in to make correlation heatmap

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

Use Data Analysis Tool to Create a Correlational Matrix

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

Apply Conditional Formatting to make dynamic correlation heatmap in Excel

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

Apply Conditional Formatting to make dynamic correlation heatmap in Excel

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

Apply Conditional Formatting to make dynamic correlation heatmap in Excel

 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.

That is how you can make a dynamic heatmap with a correlation dataset in Excel

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

That is how you can make a dynamic heatmap with a correlation dataset in Excel


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


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

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo