Applying conditional formatting rules

In this section, we'll look at a few practical applications of conditional formatting.

1. Number threshold

You can compare your measures against a threshold value and apply formatting if the condition is satisfied. In this case, we've changed the font color to green if AC exceeds 25m or the previous year's variance exceeds 4m.

Setting a rule based on a number threshold

2. Column comparisons

You can compare two columns from your data, E.g. Sales versus Forecast, and apply conditional formatting if the condition is satisfied.

Charts

Let's highlight the quarters where the Actuals exceed the Plan and the Previous year's actuals.

Tables

3. Calculated comparisons

In the condition, you can apply simple calculations like addition, subtraction, multiplication, and division.

Charts

Let's highlight the bullets where the Actuals exceed 98% of the Plan.

Conditional formatting based on column comparisons in a chart

Tables

4. Conditional formatting for relative time periods

For date-based reports, apply conditional formatting to highlight data for specific time periods - like the past week, past month, next week, or previous day. This helps direct users' focus to recent activity.

Charts

In the example, we've highlighted the sales measure for the past week with a hatched fill and a different color.

Conditional formatting for the past week

Tables

5. Conditional formatting based on grand average

Gain deeper insights in small multiples mode with the grand average option in conditional formatting. You can apply formatting based on the average values across all panels and highlight categories greater or less than the overall average.

Before we can use the grand average in a conditional formatting rule, we need to enable the Average Panel toggle from trellis settings. The average panel is the last one, titled '3 Segments'.

Enabling average panel

As a practical example, let's create a conditional formatting rule that compares the enhanced profit with the grand average profit.

Conditional formatting rule based on grand average

Notice how we've highlighted the regions with enhanced profits greater than the grand average across segments.

Conditional formatting based on grand average

We'll look at color scales in the next section.