How to create a Heat map in Excel- 4 Easy Methods

A heat map is a graphical representation of data in different colors. It is used to visualize and highlight patterns, trends, and correlations in large and complex datasets. Heat maps typically display data as a matrix, with rows and columns, and the color intensity of each cell represents the magnitude of the corresponding data value.

Heat maps are commonly used in finance, biology, and sports analytics to understand and interpret data more effectively. Thus, learning and understanding how to create and customize them using your Excel application are necessary. This article will discuss common steps and ways to create Excel heat maps.

Method 1: Using the Conditional Formatting tool

Conditional Formatting allows you to apply specific Formatting to cells based on conditions that you specify. It highlights or visualizes data more meaningfully, making it easier to analyze and understand.

Follow these steps while using this option to create Heat Maps in your Excel:

1. Open Excel Application

Open the existing or new Excel document that will host your Heat Map. Double-click on the Excel Icon and press CTRL + N to create a new document. Rename and save your document on your device.

2. Prepare the dataset

Once the dataset is ready, enter your dataset on the empty cells. If you’re using tables, organize the data into a table with rows and columns.

3. Highlight the dataset

Using your cursor, highlight or select the dataset you wish to convert to heat maps. Alternatively, select the entire data table, including the headers, if you use tables. Then, locate the Home Tab on the Ribbon and click on it. By default, the home is opened after launching the Excel application.

4. Select the Conditional Formatting

On the Home tab, locate the Styles section and click the Conditional Formatting drop-down button. From the menu, hover the cursor over the Color Scales button.

5. Choose a Color Scale

From the menu, choose a color scale that best represents the data values based on the data range and the desired visualization. On clicking, the selected dataset will give you a heat map.

6. Adjusting the Color scales of your Heat Map

One of the greatest advantages of creating Heat maps with excel is its ability to alter with coloring. To achieve this, select Color Scales again and choose More Rules.

On clicking, the New Formatting Rule dialogue box will open. In the Select a Rule Type section, choose the rule formatting you wish to use. Then, in the Edit the Rule Description section, choose the formatting styles and types you need to apply to your Heat Map. Finally, click the Ok button to close the dialogue box.

7. Customizing the Heat Map

After you’ve created the heat map, you can edit and customize it. To do so, you need to follow these steps:

  • Using your cursor, highlight or select the Heat Map created.
  • On the Home tab, locate the Styles section and click the Conditional Formatting drop-down button.
  • Then, from the menu, choose the Manage Rule option.

  • In the dialogue box, click the Edit Rule button.

  • Finally, edit the color scales in the Edit Formatting Rule dialogue box.

Similar Read: How to do a Correlation Graph in Excel- With Examples

Method 2: Using Pivot Tables

Do you know you can use Pivot Tables to create Heat maps? A pivot table in Microsoft Excel is a tool that allows you to summarize, analyze, and explore large amounts of data. Pivot tables arrange, reorganize, and manipulate data more meaningfully.

Steps:

1. Open Excel Application

Open the existing or new Excel document that will host your Heat Map. Double-click on the Excel Icon and press CTRL + N to create a new document. Rename and save your document on your device.

2. Prepare the dataset

Once the dataset is ready, enter your dataset on the empty cells.

3. Insert the Pivot Table

Select the entire data range you want to include in the pivot table. In the Insert tab, select PivotTable, and choose a location for the pivot table. Then, add the fields: Drag and drop the fields into the pivot table to specify the data to be summarized.

4. Select the Conditional Formatting

On the Home tab, locate the Styles section and click the Conditional Formatting drop-down button. From the menu, hover the cursor over the Color Scales button.

5. Choose a Color Scale

From the menu, choose a color scale that best represents the data values based on the data range and the desired visualization. On clicking, the selected dataset will give you a heat map.

6. Adjusting Color scales on the Pivot Table

On the Home tab, locate the Styles section and click the Conditional Formatting drop-down button. Select Color Scales again and choose More Rules. On clicking, the New Formatting Rule dialogue box will open. In the Select a Rule Type section, choose the rule formatting you wish to use. Then, in the Edit the Rule Description section, choose the formatting styles and types you need to apply to your Heat Map. Finally, click the Ok button to close the dialogue box.

Method 3: Creating Heat Maps without numbers

Sometimes, you may need to create a Heat Map that doesn’t have numbers or values in the cells. This is possible with Excel. You can hide the cells with your dataset without having to delete them. Below are two methods you can use to achieve this:

i. Using the Font Tool

Below are the steps to do so:

1. Open the document with Heat Map

Locate the heat map that you wish to hide its values. Then, highlight the heat map cells.

2. Go to the Home Tab

On the Ribbon, locate the Home tab and click on it. Then, locate the Font section on the left-hand side of the Ribbon.

3. Click the Font dialogue-launcher

On the Font section, click on the Font dialogue box launcher to open the Format Cells dialogue box.

4. Choose the Custom Option

On the opened dialogue box, click on the Number tab and locate the Custom Option in the Category menu. Edit the Type section, and add three commas (;;;). Finally, hit the Ok button, and all the values in your Heat map will be hidden.

ii. Using Keyboard Shortcuts

Steps:

1. Open the document with Heat Map

Locate the heat map that you wish to hide its values. Then, highlight the heat map cells.

2. Press CTRL + 1 Keys

After selecting the Heat map, press the CTRL + 1 keys to open the format cell dialogue box. On the opened dialogue box, click on the Number tab and locate the Custom Option in the Category menu. Edit the Type section, and add three commas (;;;). Finally, hit the Ok button, and all the values in your Heat map will be hidden.

Method 4: Creating Dynamic Heat Map Using Checkbox

Sometimes, you don’t need a Heat amp on your dataset. Creating a dynamic heat map using a check allows you to choose when to show or hide it. Let’s discuss the steps you must follow when creating a dynamic heat map using Checkbox.

Steps:

1. Open Excel Application

Open the existing or new Excel document that will host your Heat Map. Double-click on the Excel Icon and press CTRL + N to create a new document. Rename and save your document on your device.

2. Prepare the dataset

Once the dataset is ready, enter your dataset on the empty cells.

3. Insert a Checkbox

Choose a location next to your dataset and insert the Checkbox. Follow these steps:

  • Click the Developer Tab on the Ribbon.
  • Locate the Controls section, and click on the Insert drop-down button.
  • In the insert menu, choose the Checkbox icon.

4. Link the Checkbox with the cell

After inserting the Checkbox, you need to link it with the document’s cell. Therefore, to do so, follow these steps:

  • Right-click on the added Checkbox. Choose the Format Control option.

  • In the Format Control dialogue box, click on the Control tab.
  • Locate the Cell Link section and add the reference of the cell you wish to link the Checkbox with.

  • Finally, hit the OK button to close the dialogue box.

5. Set Up Conditional Formatting

On the Home tab, locate the Styles section and click the Conditional Formatting drop-down button. Upon clicking, a drop-down menu opens. Then, choose the More Rules option.

On clicking, the New Formatting Rule dialogue box will open. In the Select a Rule Type section, select the “Format All cells based on their Values” . Then, in the Edit the Rule Description section, choose the formatting styles; 2-color scale or 3-color scale.

In the Type Section, click on the type drop-down button, and add the following values:

For Minimum: =IF($K$2=TRUE, MIN($B$3:$M$5), FALSE)

For Midpoint: =IF($K$2=TRUE, AVERAGE($B$3:$M$5), FALSE)

For Maximum: =IF($K$2=TRUE, MAX($B$3:$M$5), FALSE)

NOTE:

K in the above formulas is the cell reference that is linked to your Checkbox

B and M: These are the cell references of the cells that contain your dataset.

6. Choose the Heat Map colors

Choose the colors you wish to apply to your Heat Map in the color section. Finally, Hit the Ok button.

Now, you can use the Checkbox to set on or off the heat map in your excel document.

 

Leave a Reply

Videos

Discover more from Excel Wizard

Subscribe now to keep reading and get access to the full archive.

Continue reading