• Overview
  • How to create a heatmap?
    • Choosing color ranges
      • Two colors
      • Three colors
    • Applying rules to chart
    • Multiple rules
    • Using other series for formatting
• Example
• Questions
  • Can I format within a row instead of a column?

Overview

You can create a Heatmap by modifying Bar chart in a way that presents values by assigning them a color from defined range of colors - using Bar chart conditional formatting rules.

How to create a heatmap?

To create a Heatmap first you need to have Bar chart in a form of Dynamic table - this is just a simple table but the values will change after series update. By settings color rules you create Heatmap.

Rules panel is available under Chart properties > Chart elements > Rules. In new window simpy press 'Add rule' and start the process:

You can highlight either 'Text' or 'Background' for each cell. For more information about types (Range, Condition, Rank, Average) see Bar chart conditional formatting rules.

Choosing color ranges

Two colors

After adding a rule, select colors for backgrounds and check in 'Include' to which columns should this be applied. Then press OK. See below paragraphs to learn more about the process.

Three colors

Let's say you want to set three colors on your Heatmap, for high, middle and low values. You need to define two ranges of colors.

For example: 0 to 50^th percentile as a range from red to yellow and 50^th to 100^th percentile as a range from yellow to green. This way you will receive a heatmap showing the highest percentile in green, lowest in red and all others in the colors range accordingly to their values.

Applying rules to chart

You may apply a rule to all visible values in the table, or for each column separately.
You may also decide to which columns apply the rule. Note that first column is the the one with descriptions. It's not possible to do apply rules by rows.

Multiple rules - different rules for different columns

In one Bar chart you can have different rules for different columns. Each column is a separate entity, and a rule will apply to each column’s values range separately.

Using other series for color formatting

You may use other series to define the range of values of plotted series, even the one not used in the chart, by setting Value series to this series.

Example: You have one series with the values and another with a condition. Here it's a formula:

if(fx;s1<0, 0, 1)

And you want to show the actual values but color them using the formula series. '0' value if the change was negative and '1' if it was positive. This would look like this:

Example

Heatmap

In this example, we applied conditional formatting rules based on range of colors to the Bar chart creating a heatmap.

Questions

Can I format within a row instead of a column?

No, formatting works only within columns or whole chart.