Heat Maps

Heat Maps allow you to display large volumes of data in a comprehensive and concise way. Because of their intuitive nature, Heat Maps are great way to visually compare data.

Using a sample data set of video game sales, we will walk through the steps creating a Heat Map using only 1 formula, the “$”, and conditional formatting.

To follow along, please download the sample data set. This data set was originally downloaded from Kaggle

Using the sample data set, we will compare the units sold per video game genre over the years

X

Let’s Begin

First thing we do is create a new sheet to display our heat map.
There are several ways to create a new sheet, but i find it easier to just click on the “New Sheet” tab at the bottom

You can also use the keyboard short cut keys

Phase 1

Preparation

Since we want to compare the genres, I simply copied the genre column from the vgsales tab and pasted them in column A on Sheet1

Then with column A still highlighted, I clicked on the “Remove Duplicates” button in the “Data” ribbon

Next we should enter the years we would like to compare. If you do a quick Min() and Max() for the Year column, you’ll see the years go from 1980 to 2020. For our purposes, we don’t need so many years.
So starting in cell B1 and ending in cell AA1, I entered the years 1990 to 2015

Your spreadsheet should look like this


I sorted the genres alphabetically and resized the cells to give them a square appearance.

Phase 2

Calculating

We will enter the formula above into cell B2 of the newly created sheet. Your spreadsheet should look like the image below.

I will break down this formula and explain what is going on with the $’s.
The Countifs() formula will count the number of times specified criteria occurs in a given cell range. Click here to learn more about Countifs()

Excel can be very helpful, and when you copy and paste a formula from one cell to another cell, Excel will adjust your formula accordingly. The “$” tells Excel to lock down that column or row. The $ turns the reference into an absolute reference. That means that no matter where we copy the formula to, it will always reference the same column and/or row.

In cell B2, we want to know the number of times the “Action” genre occurred for the year 1990. With the Countifs() formula and the $, we are telling Excel to count the number of times the year in D2:D16599 is equal to the data in B1and the genre in E2:E16599 is equal to the data in A2

In our table, the genres are going from A2 to A13. So we want to always reference the A column. Likewise, the years are going from B2 to AA2 and we always want to reference row 2.

Copy cell B3 and then highlight cells B3 to AA13, then paste. Your worksheet should loook like the image below after you have finished copying and pasting.

Phase 3

Formatting

To start formatting the Heat Map, highlight the cells B2 to AA13. Click on the “Condiontal Formatting” button in the Home ribbon.

From the drop down menu, click on “New Rule…” it will be towards the bottom of the list. You should now see this form

You could easily click “OK” and you would have a basic heat map. To elevate your Heat Map to the next level, its going to take a few more clicks.

Formatting – Boss Level

With the “New Formatting Rule” form still open, change the Format Style to 3-Color Scale. Excel will now color code the values in you cells according to the formatting criteria we enter here.

The first section is the Minimum and Excel automatically chooses the lowest value. You can change it to percentile or base it off a formula. For now, let’s leave it at Lowest Value. To customize our color selection,

1. Click on the the drop down arrow for color.
2. Click on More Colors
3. Click on the Custom tab and enter the following values.
• Red: 255
• Green: 255
• Blue: 204
4. Click OK

The second section is the Midpoint, again Excel will automatically set this for you. The default is Percentile and the precentile value is 50. Again, let’s leave this as is. To customize the color, repeat the steps we did for the Minimum section. This time, the colors will be:

• Red: 254
• Green: 178
• Blue: 76

The third section is the Maximum, as before Excel will automatically set this for you. The default is the highest value in our range. To customize the color, repeat the steps we did for the Minimum section. This time, the colors will be:

• Red: 227
• Green: 26
• Blue: 28

Awesomeness! Your heatmap is ready to present.

Click below to see a finished heatmap and a couple of examples with other color combinations.

X

X

X