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 scales can be applied to 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 steps below.

Select Color scale in the Format by dropdown.

Choosing color scale

Let's go with the default options. Click Apply. The color scale is applied to the cell background color of the measure selected in the Apply to field.

Basic color scale

Let's explore the configuration options for color scales.

1. Based on

Let's 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

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

Enabling the 2021 Actuals - 2021 Plan column

Notice how, International – EMEA – Tea & Coffee is displayed in a darker shade due to a higher positive variance, while United States – East – Juices is prominently highlighted because of a substantial negative variance.

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

2. Color scale for

Now, let's apply a color scale to the font. Select Font in the Color scale for dropdown. Click Apply.

Color scale for font

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

Color scale for font

3. Heat map type

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

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

Table wise formatting

4. Color scale type

Inforiver Table supports different types of color scales such as Sequential, Diverging, Qualitative, Continuous, etc. You can click on the Color scale type dropdown to view the entire list.

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

Color scale types

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

Diverging color scale

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

Continuous-Range scale

5 Colors

5.1. Color scheme

For color scales 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

5.2. Min/max/center

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

Color selection

5.3. 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 image below. 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

5.4. Reverse color

Color scales 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

5.5. 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

5.6. 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

5.7. 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. It can be turned off if not required by unchecking the Auto font color checkbox.

Auto font color disabled

5.8. 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 image below, the 2024 Actuals measure has null values. To apply conditional formatting for these cells as well, check the Include null checkbox.

The color scale is applied to cells with null values

Conditional formatting is applied only to non-null cells

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.

STEP 1: Select Data bars in the Color scale for dropdown.

Selecting color scale for data bars

STEP 2: In the side panel, you can see the customization options available.

Data bar properties

STEP 3: 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

STEP 4: 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

STEP 5: 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

STEP 6: Data bars can be aligned left or right using the Alignment field. Right alignment is shown in this example.

Data bars alignment

STEP 7: 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