Heat Maps with Individual Color Scales in Excel

How to create Heat Maps in Microsoft Excel using individual Color Scales to support the analytical insights into the distribution of the data

Heat Maps with individual Color Scales IntroIn his brilliant guest post Analytical Color Scales for Heat Maps, Ron Whale explained, how color scales can efficiently support the process of analyzing data.

Applying different, thoroughly designed color schemes can help to gain deeper insights into the distribution of data, to identify outliers, to focus on special points of interest, to find similar groupings and more.

In a nutshell: switching between color scales of a Choropleth or Heat Map helps you to understand your data.

Whilst Ron was developing his great palette of color schemes for using them on a Choropleth Map, they are equally helpful for a much simpler visualization technique: a Heat Map of a table (or range) of numbers.

Microsoft Excel does provide a built-in feature to create a Heat Map on a range of numbers: Conditional Formatting. However, this is limited regarding the configuration of the color scales and it does not provide the option to easily switch between color schemes for supporting the data analysis process.

Today’s post provides a technique to overcome this shortcoming of Excel: a VBA-based solution to easily apply any given color scale to an Excel range with only two mouse clicks. As always, the post comes with the workbook for free download.

The Common Approach: Excel’s Conditional Formatting

Excel provides a standard functionality for color-coding a cell range based on the values of the cell: Conditional Formatting with Color Scales. You even have a few options to configure the color-coding:

Edit Formatting Rule WindowYou can choose between a 2- and 3-Color Scale, define colors for minimum, midpoint and maximum and type and value for minimum, midpoint and maximum.

Select the options you find appropriate, apply the rule and you will get something like this:

Conditional Formatting Heat MapI am sure most of you know this already. If this is new to you, I recommend checking out these articles from my friends over at Excel Easy: Color Scales or Heat Map.

Limitations of Conditional Formatting

In the previous post Analytical Color Scales for Heat Maps, Ron Whale explained, how color scales can be used to highlight details and reveal specific aspects of data sets. Ron developed the following 23 color scales:

Analytical Color ScalesThere is no way to create all these color scales with Excel’s standard Conditional Formatting.

Furthermore, if you want to take advantage of the scales when exploring and analyzing data, you need a fast way to switch between these color schemes. And this is definitely not possible with Excel’s Conditional Formatting.

The Objective

So, the objective is obvious. What we need is the following:

  • Provide the option to easily define different color scales (the fill color and the font color, by the way), structured in bins. The number of the color scales and the number of bins shall also be configurable
  • Allow the user to easily change the color-coding of the Heat Map without changing the conditional formatting

The user should be enabled to get from this

Heat Map - Center Loadedto that

Heat Map - Outlier Loadedwith only two mouse-clicks.

The Implementation

Whenever Excel’s native features do not provide what you need, you can either try to build some kind of work-around (often works for charts) or you have to overcome Excel’s shortcomings with VBA code. The latter is what we will do for today’s challenge.

The heart of the solution is a VBA sub called ColorCodeHeatMap. The sub expects 3 mandatory input parameters and has 2 additional optional parameters:

Private Sub ColorCodeHeatMap( _   
                                            rngData As Range, _
                                            rngColorScales As Range, _
                                            intSelection As Integer, _
                                            Optional blnHideNumbers As Boolean, _    
                                            Optional blnCreateLegend As Boolean)

  • rngData is the range of your heat map, i.e. the cell range where your data lives
  • rngColorScales is the range where the color schemes are defined.
  • intSelection is an index, specifying which color scheme shall be used, i.e. 1 for Green to Red contrasts, … , 23 for Green Smear in the example posted above
  • with the boolean optional parameter blnHideNumbers, you can specify whether the numbers shall be shown. If set to False, they will be shown, if set to True, they won’t. If not specified, it will default to False
  • blnCreateLegend defines whether or not a legend shall be created right to Heat Map. If you want a legend, set this to True, otherwise to False. If not specified, it will default to False

Since the sub requires input parameters, you can’t directly call it from an Excel worksheet. Instead, you need another sub without parameters, which only calls ColorCodeHeatMap passing the parameters you want, like this:

Sub ReColorHeatMap_1()

    ColorCodeHeatMap Range(“myHeatMap_1”), _
                                 Range(“myColorScales”), _
                                [mySelectedScale_1], _ 
                                True,_
                                True

End Sub

This example expects to find three named ranges called myHeatMap_1, myColorScales and mySelectedState_1 in your workbook. You could also use hard-coded values there (like Range (“C7:L16”), but this is not recommended. It always pays off to address cell ranges in VBA via a named range, because this makes the code independent from structural changes on the worksheets, like inserted or deleted rows or columns.

This sub can then be called from Excel, e.g. by assigning it to a combo box or a command button.

The sub ColorCodeHeatMap is not very complicated and most parts are commented in the code. I won’t go into the details. If you are interested, have a look for yourself.

Finally: you can easily adjust the color schemes Ron defined on the worksheet [Color Scales]: you can change the fill and font colors as you like, you can increase or decrease the number of bins by inserting or deleting rows and you can add your own schemes by inserting a column. All you have to take care of is the reference of the named range myColorScales. Make sure that the reference there covers the entire range of your collection of scales.

Download Link

Download Individual Color Scale Heat Maps (zipped Microsoft Excel Workbook 109K)

How to transfer this technique to your own workbook

  1. Download the workbook posted above
  2. Open the downloaded workbook and your own workbook
  3. Copy the entire sheet [Color Scales] to your workbook
  4. Select the range with your data and assign the name “myHeatMap_1”
  5. Insert a combo box from the Developer Tab in your workbook
  6. Change the settings of the combo box inserted in step 5: assign the list of the color scales on worksheet [Color Scales] as the input range and the name “mySelectedScale_1” as the cell link
  7. Go to the VBE (ALT-F11), select the module modHeatMap in the Project Explorer Window and drag it to the VBA Project of your workbook. Close the VBE
  8. Assign the macro “ReColorHeatMap_1” to the combo box inserted in step 5
  9. Save your workbook as a Macro-Enabled Excel Workbook

That’s it. Looks like a lot of effort, but please trust me, it only takes a few minutes.

Disadvantages

Just like any other VBA-based enhancement of Excel’s standard functionality, the individual color scaled Heat Maps come with a price, too:

  • It is a VBA-based solution, not Excel standard, i.e. it takes some time to transfer the code to another workbook and define the named ranges (see the section above)
  • The color-coding does not change automatically, when the values change. However, this could easily be overcome: a short additional code snippet in the event driven sub Worksheet_Change could check if the data range of the Heat Map changed and if so, call the sub to update fill and font colors. This is not included in the workbook above, but is not a big deal to do
  • Last, but not least the performance: the code works very fast for smaller range sizes like 10 rows and 10 columns or 100 rows and 10 columns. Even with 10,000 cells (100 rows, 100 columns), the code is fast enough for a dashboard in production.

    However, if you considerably increase the range size (e.g. to 1 million cells), the sub will take several minutes to re-color the range. Unfortunately, this is inevitable, because the range has to be reformatted cell by cell and “walking across the border” between VBA and Excel is very time-consuming.

    On the other hand, I do not really see a value in creating a cell range Heat Map for so many cells. If you look at the workbook posted for download above, you already have to decrease the zoom factor to 35% to see the entire Heat Map on the screen. And this is only 100’*100, so still a size with a reasonably fast performance.

    In an nutshell: performance can become an issue, but not for the majority of the use cases.

Have a look at the workbook and let me know what you think.

Stay tuned.

Add a Comment

Your email address will not be published. Required fields are marked *