Advanced Excel Charts – Heat Map

Warmth Map is often used to discuss with the coloured distinction of areas in a two dimensional array, with every shade related to a unique attribute shared by every space.

In Excel, Warmth Map could be utilized to a spread of cells primarily based on the values that they comprise through the use of cell colours and/or font colours. Excel Conditional Formatting comes useful for this objective.

What’s a Warmth Map?

A Warmth Map is a visible illustration of information in a desk to focus on the information factors of significance. For instance, when you’ve got month smart information on sale of merchandise during the last one 12 months, you may venture during which months a product has excessive or low gross sales.

A Warmth Map appears to be like as proven under.

Benefits of Warmth Maps

Warmth Map can be utilized to visually show the completely different ranges of information with distinct colours. That is very helpful when you might have massive information units and also you wish to shortly visualize sure traits within the information.

Warmth maps are used to −

• Spotlight the highest few and the underside few of a spread of values.
• Painting a development within the values through the use of shade shades.
• Establish clean cells – say in a solution sheet or a questionnaire.
• Spotlight the standard ranges of the merchandise.
• Spotlight the numbers in provide chain.
• Spotlight unfavorable values.
• Spotlight zero values.
• Spotlight outliers outlined by thresholds.

Preparation of Knowledge

Organize the information in a desk.

As you may see, the information is for a fiscal 12 months, April – March, month-wise for every product. You’ll be able to create a Warmth Map to shortly establish throughout what months the gross sales had been excessive or low.

Making a Warmth Map

Following are the steps to create a Warmth Map −

Step 1 − Choose the information.

Step 2 − Click on Conditional Formatting on the Ribbon. Click on Handle Guidelines and add guidelines as proven under.

The highest 5 values are coloured with inexperienced (fill) and the underside 5 values are coloured with crimson (fill).

Creating Warmth Map with out Displaying Values

At instances, the viewers could be simply have an interest within the info and the numbers won’t be obligatory. In such a case, you are able to do a little bit of formatting as follows −

Step 1 − Choose the information and choose the font shade as white.

As you may see, the numbers should not seen. Subsequent, you could spotlight the highest 5 and backside 5 values with out displaying the numbers.

Step 2 − Choose the information (which isn’t seen, in fact).

Step 3 − Apply Conditional Formatting such that the highest 5 values are coloured with inexperienced (each fill and font) and the underside 5 values are coloured with crimson (each fill and font).

Step 4 − Click on the Apply button.

This offers a fast visualization of excessive and low gross sales throughout the 12 months and throughout the merchandise. As you might have chosen the identical shade for each fill and font, the values should not seen.