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.
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.
Let's highlight the quarters where the Actuals exceed the Plan and the Previous year's actuals.
3. Calculated comparisons
In the condition, you can apply simple calculations like addition, subtraction, multiplication, and division.
Let's highlight the bullets where the Actuals exceed 98% of the Plan.
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.
In the example, we've highlighted the sales measure for the past week with a hatched fill and a different color.
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'.
As a practical example, let's create a conditional formatting rule that compares the enhanced profit with the grand average profit.
Notice how we've highlighted the regions with enhanced profits greater than the grand average across segments.
We'll look at color scales in the next section.
Last updated