Views > Excel charts > How to create a heatmap in excel

Color intensity—one of the pre-attentive attributes of visual perception—comes in handy for certain visualization purposes. One of those visualizations that rely on hues for visual encoding is the heatmap.

A heatmap is a matrix of cells with each cell color-coded to display a quantitative value, as per the graph below. The heatmap shows the total number of fatal motor vehicle crashes—for the years 2014 to 2018—by day of week and time of day. The darker the color the higher the number of crashes and vice versa. The key on the top right corner of the graphic indicates which colors signified what level of crashes. No doubt, we can’t make a precise comparison for the number of crashes, but sometimes these approximate indications are all we need to see what’s going on in the data. For instance, crashes seem to peak on weekends between 5:00 pm and 3:00 am.

The heatmap below was inspired by the work of Nathan Yau. Lets build it in Excel.

The data source for the above chart is generated through the National Highway Traffic Safety Administration (NHTSA) Motor Vehicle Crash Data Querying and Reporting system and looks like this:

HOW TO CREATE A HEATMAP IN EXCEL

4. Next, I need to reorganize the data in a way that will show a clearer pattern in the data. For that, I’ll reorder the columns of day of week so that the week starts on Monday instead of Sunday. Next, I’ll reorder the rows of time of day so that the day starts at 5 00 am as per the screenshot below:

5. Highlight the range F6:L31 and then on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then select New Rule.
6. In the New Formatting Rule dialog box, under Edit the Rule Description, click on the Format Style option and select 3-Color Scale from the dropdown list.
7. Choose a color of your choice for the Minimum, Midpoint and Maximum as per the following screenshot:

8. Click the Ok button to close the New Formatting Rule dialog box and the worksheet should look like this:

9. To hide the numbers inside the cells, select the range F6:L31 in the worksheet. Next, on the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list.
10. In the Format Cells dialog box, under Category, click Custom.
11. In the Type list, type ;;;. Next, click the Ok button to close the Format Cells dialog box. The worksheet should look like this:

12. Lets do some formatting. Change the worksheet font to Trebuchet MS and its size to 9.
13. Change the day of week headers to Mo, Tu, etc. from Monday, Tuesday, etc.
14. Split the time of day labels into 3 columns so that you have control over spacing and alignment of the labels. Add labels to indicate morning, noon and midnight.
15. On the worksheet, select columns F to L. Right-click over the selected range and then, on the shortcut menu, click Column Width and set it to 2.27.
16. On the worksheet, select rows 5 to 31. Right-click over the selected range and then, on the shortcut menu, click Row Height and set it to 15.
17. Similarly, set Column Width of column I to 0.21 and Row Height of row 30 to 7.1. The worksheet should look like this:

18. On the worksheet, select cells F6:L31. On the Home tab, in the Font group, click the arrow next to the Borders icon, and then click More Borders.
19. In the Format Cells dialog box, on the Border tab, under Line and Color, choose the white color from the dropdown list. Then, under Presets, click the Outline and Inside buttons to indicate the border placement. Click the Ok button to close the Format Cells dialog box.
20. Adjust the Column Width of columns C, D and E. The worksheet should look like this:

21. On the worksheet, highlight the range N6:N31 and then on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then select Data Bars > Blue Data Bar (under Solid Fill).
22. Next, on the worksheet, highlight the range F34:L34 and then, on the Insert tab, click Insert > Insert Column or Bar Chart > Clustered Column in the Chart group. Turn off the worksheet gridlines. The worksheet should look like this:

23. Highlight the range N6:N31 and then on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then select Manage Rules.
24. In the Conditional Formatting Rules Manager dialog box, click on the rule followed by a click on the Edit Rule button.
25. In the Edit Formatting Rule dialog box, check the Show Bar Only option and change the Fill color to light grey as per the screenshot below:

26. Click the Ok button twice in a row to close the Edit Formatting Rule and the Conditional Formatting Rules Manager dialog boxes.
27. Next, make formatting changes to the Column Chart such as deleting the horizontal gridlines, the chart title and the chart border. Adjust the vertical axis scale maximum limit and its number formatting. Resize the chart and align it with the heatmap as per the below screenshot:

28. Add the chart title, sources and annotations. Add scale for Total bar chart and a legend key. The chart should look like this:

Five years of vehicle crashes data is reported by day of week and time of day. The totals of the 5 years is also reported at the bottom of the table.

1. I’m only interested in the totals and need to transpose the data so that the day of week is on the horizontal axis and time of day is on the vertical axis. To do that, select the totals data (range B49:AB56) and press Ctrl + C to copy the data to the clipboard. Next, on a new worksheet, select cell F5 and then, on the Home tab, in the Clipboard group, click the Paste icon and select Paste Special.
2. In the Paste Special dialog box click Transpose. Next, click the Ok button to close the Paste Special dialog box.
3. Do the same for the time of day headers and the worksheet should look like this: