You can apply conditional formatting to most chart types, except waterfall. Use the Appearance Settings tab to define how to format data that meet certain conditions. Conditional formatting is rule-based that evaluates to true or false. If true, the condition is met and the data is conditionally formatted in the color specified. Rules must reference a data series in the chart.
Suppose you want to highlight good liquidity in a chart when the quick ratio is higher than 2. You can create an expression:
[quick Ratio] > 2 that evaluates true or false. If true, the data point is formatted in green.
Charts display data as a numeric value or graphically such a bar or column. The display style for a conditionally formatted data point will vary based on the chart type.
Charts with a Numeric Display Style
Conditional formatting is applied to the numeric value on these charts:
For example, this KPI is highlighted in green indicating that 4.4 million in revenue is above expectations:
Charts with a Graphic Display Style
Conditional formatting is applied to the graphical representation of data to these charts:
- Area and Line
- Bar and Column
- Doughnut and Pie
- Funnel and Pyramid
- Gauge and Meter
For example Gauge charts conditionally format the scale and doughnut charts conditionally format data slices in pink when gross margin or revenue falls below expectations:
Conditional Formatting Rule Order
You can create many conditional formatting rules in a chart. These rules are evaluated from the bottom up. The order of rules matter if you have rules that evaluate true on the same data point. The order of the rule determines the conditional formatting to apply.
You can reorder rules by dragging the handler icon to the new position.
To get a more predictable outcome, use comparison operators to establish boundaries. Arrange the rules in the order you want these rules to be evaluated.
Create a Conditional Formatting Rule
You can create a conditional formatting rule on new or existing charts.
- Create a chart or edit a chart.
- Click in the right panel to open the Appearance Settings tab. Expand the Conditional Formatting section.
- Click Add rule. The Conditional Formatting dialog displays.
- Define the rule:
- Provide a brief description, such as Good Liquidity.
- Build the expression. Click for syntax rules and list of operators and functions.
[Quick Ratio] >= 19, where Quick Ratio is the name of a data series in the chart.
- Pick the highlight color to format data points when the condition is true.
- Choose the series on which to evaluate the rule.
- Click Save.
Edit a Conditional Formatting Rule
You can edit any conditional formatting rule. Hover over the rule. Click to open the more menu and choose Edit.
The Conditional Formatting dialog displays. Edit the rule and save. The change is reflected in the chart.
Delete a Condition Formatting Rule
You can delete any conditional formatting rule. Hover over the rule. Click to open the more menu and choose Delete. A confirmation message displays. Click OK to delete.