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.
