In Excel you can format cells depending on a condition. Most of the settings are evident. I recommend checking out the different options of conditional formatting by playing around with it. This post will give you an introduction to this feature.

You find this button in the ribbon’s “start” section. When you click on it, you are offered a lot of different types of conditions to choose from. Before choosing a conditional formatting you should have already selected the cells you want to apply it to. But this can of course also be set or changed afterwards.

(Sorry, that I am using a german Excel 2010 version. But most of the keywords are very similar to the english counterparts and the layout of the dialogs are independant of the language. Hence I don’t think it is handicapping too much.)

Formatting the biggest 10% of the values.

Formatting a range of values.

Formatting the values relative to the maximum cell value. 90% of the maximum evaluates to red, 40 % of it to yellow and 10% of it to green.

Formatting in this case is (or should be) determined depending on the closeness of a value to the respective quantile.

Formatting just depending on vicinity to the entered absolute number.

Formatting if the given formula evaluates to true for a cell value.

In the above example I check wether the remainder of a division by three is zero – i.e. the cell’s value is divisible by three. In the English version of Excel the formula would be:

(Using German Excel one is forced to use German formulas and German number formatting – meaning that the comma is reserved for decimal numbers – hence the semicolon in the screenshot to separate parameters)

E8 is just one cell from the area the formatting is applied to. But Excel will apply the formatting to each cell in this area as it is to E8.

More sophisticated heatmaps

For generating heatmaps with precise control over how the coloring is applied and in general for visualizing densities on a continuous space it is recommended to go with R. There will be articles on that in the future for sure. Excel is good for coloring finite sets of values to increase the understanding of how one value relates to the whole set.

Green and Red

The colors green and red are signal colors which are automatically associated with good and bad – especially when used in combination. I observed for example in business reports that negative percentages are colored red – even when a negative percentage is actually something good for the business – like a week on week for cancellation of orders.

Also for heatmaps – let’s take for example the one for character sequences I covered in the previous article. It would be confusing to choose a color scale from red to green / green to red – as if 0 observed cases for a character combination would be something unwanted / desirable. In a context where the consumer is supposed to make use of the brain this false interpretation could be easily mentally compensated though – in a business context I would refrain from that.