Color scale & data bars

Another commonly used type of conditional formatting is the 'color scale,' which applies a gradient fill to a range of cells based on their values. This is a useful way to quickly visualize and compare values in a table. Color scale can be applied for the background, font color or data bars. It can also be applied row-wise, column-wise, or table-wise.

Refer to create rule to get started. Once the rule is created and you can see the 'Conditional formatting' side panel, follow the below steps.

Choose 'Color scale' in the 'Format by' dropdown. There are several options enabled as shown in the below image.

Choosing color scale

1. Font/Background

Let's go with the default. Click 'Apply'.

Basic color scale

i. Based on

a) Let's now apply formatting for 2021 Actuals based on the absolute variance between AC and PL. Select '2021 Actuals - 2021 Plan' from the 'Based on' dropdown. Click 'Apply'.

Conditional formatting based on a different measure

b) Let's hide the 2021 Plan column and show the variance column. Click on 'Manage columns' and check/uncheck the checkboxes as highlighted.

Enabling the 2021 Actuals - 2021 Plan column

c) You can see that the color scale has been updated to highlight values based on the variance. For eg., even though East -> Tea & coffee is 3.75m, the formatting is dark blue because of the high variance of +3.99m.

Conditional formatting for 2021 Actuals based on absolute variance AC vs PL

ii. Color scale for

a) Let's now apply color scale for font. Select 'Font' in the 'Color scale for' dropdown. Click 'Apply'.

Color scale for font

b) You can see that the font for 2021 Actuals is formatted sequentially based on the variance.

Color scale for font

c) Let's revert back to background formatting based on 2021 Actuals. Data bars are covered in the next section.

Background formatting

iii. Heat map type

a) Notice that the formatting is applied column-wise. That is the maximum and minimum values in a column are formatted using the darkest and lightest gradients.

Column wise formatting

b) Also, notice the value of East -> Soda across quarters. The highlighted values are around 15m, but for Q4 it is a darker gradient compared to the first two quarters. This is because of the column-wise formatting.

Column wise formatting

c) Let's now apply row-wise formatting. Select 'Row wise' in the 'Heat map type' dropdown and click 'Apply'. You can see that the maximum and minimum values in a row are formatted using the darkest and lightest gradients.

Row wise formatting

d) Also, notice the highlighted values in Q1. East -> Mineral water(16m) is greater than Pacific -> Soda (10m) but is formatted using a lighter gradient. And East -> Juices and Pacific -> Soda are formatted using the same gradient even though there is a huge difference. This is because of the row-wise formatting.

Row wise formatting

e) The below image shows table-wise formatting. Notice that similar values are formatted using the same gradient throughout the table.

Table wise formatting

iv. Color scale type

There are a number of types such as Sequential, Diverging, Qualitative, Continuous, etc. You can click on the 'Color scale type' dropdown to see the entire list.

a) Let's apply a 'Diverging' color scale. Select from the dropdown and click 'Apply'.

Color scale types

b) You can see that the backgrounds are formatted using the Diverging scale.

Diverging color scale

c) Below is an example where the Continuous-Range scale is used.

Continuous-Range scale

v. Colors

a) Color scheme

For color scale of types Sequential, Diverging, Diverging-color safe, Qualitative and Qualitative-color safe, there are several default color schemes to choose from. You can click on the 'Color scheme' dropdown and choose the desired color scheme.

Color schemes

b) Min/max/center

For color scale of types Continuous-Range, Continuous-Diverging Range, Continuous and Continuous-Diverging, you can define the min/max colors and center color if applicable.

Color selection

c) Custom color scale

The 'Custom' color scale type can be used to define custom color ranges based on value or percentage. On selecting the custom option, you can see the fields highlighted in the below image. You can define the values/percentages for the ranges, add or delete ranges, define colors and reverse the order. Changes can also be reset to default.

Custom color scheme

d) Reverse color

Color scale can be reversed for cases where the minimum and maximum values need to be denoted with the highest and lowest gradients. To achieve this, check the 'Reverse color' checkbox.

Reversing color scale

e) Number of bands

By default, the number of bands is defined as 5. But it can be increased or decreased using the 'Number of bands' field. The color scheme field also gets updated to show the gradients.

Number of bands

f) Hide value

You can choose to show only the color scale and hide the values. To do this, check the 'Hide value' checkbox.

Hiding values

g) Auto font color

When you apply background conditional formatting in Inforiver, the font colors are automatically adjusted to be in contrast with their backgrounds to enhance readability. But it can be turned off if not required by unchecking the 'Auto font color' checkbox.

Auto font color disabled

h) Include null

In cases where there are null values in your table, you can choose whether to include or exclude them from conditional formatting. In the below image, the 2022 Forecast for Pacific is empty. To show conditional formatting for Pacific as well, check the 'Include null' checkbox.

Conditional formatting for null values

2. Data bars

In the previous section, we covered color scales for fonts and backgrounds. In this section, we'll look at data bars. Note that you can either create a new rule as shown here or edit the data bars inserted using the one-click option explained here.

a) Select 'Data bars' in the 'Color scale for' dropdown.

Selecting color scale for data bars

b) In the side panel, you can see the customization options available.

Data bar properties

c) Let's go with the default settings. Click 'Apply'. The data bar gets added as shown in the below image.

Data bar added for 2021 Actuals

d) Let's now look at each of the customization options. Check the 'Hide value' checkbox and click 'Apply'. You can now see only the data bars and the values are hidden.

Hiding the values

e) By default, the data bars are applied to all the values (values and totals if selected in the row hierarchy levels). If you only want to use data bars for a particular range of values, you can enter a minimum and maximum value as shown in the below image. Data bars are enabled only for values between 8m and 20m in this case.

Data bars for a min-max range

f) Data bars can be aligned left or right using the 'Alignment' field. Right alignment is shown in this example.

Data bars alignment

g) Note that when there are positive and negative values in the column for which you want to use data bars, the options differ as shown in the below image. You can choose to hide the values and select colors for positive and negative values.

Data bars for positive and negative values

In the next section, we'll take a look at Classification.