Conditional formatting feature provides you to customize your tables or matrices as per the data. This helps you underscore the important apprising content to your report consumers.

Conditional formatting provides different types of formatting options such as font, background colour, icons, and data bars.

First, select the table or matrix visual for which you want to add conditional formatting.

Then from the formatting pane select the Conditional Formatting tab.

After selecting the table you will have to choose the table column. So select the column for which you want to apply the conditional formatting.

Once selected, you will be provided with  5 types of conditional formatting that you can add in your tables. They are – background colour, font colour, databars, icons and weburl.

Background colour – change the cell colour as per the value or the condition defined.

Font colour – change the font colour as per the value or the conditions.              

Data bars – Add the horizontal bars inside the cells depicting the value.

Icons – Add icons inside the table cells, to

Web URL – Set up the active link

Now, which ever formatting you wish to implement turn on the toggle button for it. Then you will be shown with a link for advance controls, select that and then you can proceed with your required formatting. 

Format background or Font Colour

To format cell background or font, select the required option. A dialog box opens as shown below along with the name of selected field to allow formatting.

The Background colour and Font colour options are the same, but affect the cell background colour and font colour, respectively.

Now, you can format the cells using colour scale, rules or field value. Let’s cover all in detail.

Colour Scale

You can select Colour scale from the Format by dropdown option.

For the based on field, select the column name based on which you want to decide the colour scheme for the selected field.  The formatting can be based on current field or on another field.

Under the Apply to dropdown list you can decide whether you want to apply conditional formatting to values, totals or both.

Under the summarization you can select aggregation type for the selected field. The default formatting specifies the default value for the selected field as there may be case when the fields are empty or have an invalid value.

After finalizing the formatting type and the variables pick the minimum and maximum value followed by the colour scheme. Drop down and select the desired colours for the minimum and maximum values. A centre colour can also be included by checking the diverging checkbox.

Background colour conditional formatting produces the following result:

The font colour formatting produces the following result:

Rules

You can select Rules from the Format by dropdown option.

For the based on field, select the column name based on which you want to decide the colour scheme for the selected field.  The formatting can be based on current field or on another field.

Under the Apply to dropdown list you can decide whether you want to apply conditional formatting to values, totals or both.

Under the summarization you can select aggregation type for the selected field.

Under Rules, enter one or more value ranges, and set a colour for each one. Each value range has an If value condition, a value condition, and a colour. Cell backgrounds or fonts in each value range are coloured with the given colour.

Rule based background colour formatting looks like this:

Field Value

You can select Field Value from the Format by dropdown option.

Under the Apply to dropdown list you can decide whether you want to apply conditional formatting to values, totals or both.

In this method, the colour scheme can be decided based on the column or measures which stores the colour codes or names.

In this example, the formatting is done based on the field Colour.

After applying the formatting based on field, the table looks as follows:

Data Bars

To show data bars based on cell values, select Conditional formatting for the desired field, and then select Data bars from the drop-down menu.

In the Data bars dialog, the Show bar only option is unchecked by default, so the table cells show both the bars and the actual values. To show the data bars only, select the Show bar only check box.

You can specify Minimum and Maximum values, data bar colours and direction, and axis colour.

 

 

Once all the required variables are decided, the output looks something like this:

Format as Icons

We can even add icons in the cells using conditional formatting.

The icons can be formatted by rules or field value from the selection option provided in Format by option.

Rules

By default the selection for the Format by option is by rules method.

 

To Format by rules, populate the field, Apply to, and summarization fields.

Under Rules, enter one or more value ranges, and set an icon for each one. Each value range has an If value condition, a value condition, and an icon. Further, you can finalize the icon alignment and placement in the table.

There are various styles of icon from which you can select the desired format. The style dropdown list provides the following icons:

 

After applying the icon based conditional formatting the table looks something like this:

Field value

To format by field values, just add the required measure in the based on field. Then select the Summarization method,  icon layout and alignment.

Add Web URLS

If there are any URLs in the dataset then it can be set as an active URL in the table/matrix visual using the Web URL conditional Formatting.

This is the URL for each country in the United Kingdom.

To set the above link as an active link in the visual is done by Web URL conditional formatting.

In the Web URL, under based on field dialog box, select the Website column and select OK.

Afterwards the visual looks like as follow:

 

Pragya Verma
Data Analyst
Addend Analytics