You can apply conditional formatting to tables or charts to highlight values in the data. This makes values above, below, or within a particular threshold stand out.
Understand conditional formatting
Many companies create pinboards with key metrics they want to track in daily or weekly staff meetings. Using conditional formatting, they can see at a glance how they are performing relative to these metrics.
You can add visual cues for KPIs (Key Performance Indicators) or threshold metrics to charts and tables, to easily show where you are falling short or exceeding targets. These visual cues are called conditional formatting, which applies color formatting to your search result. For tables, you can add conditional formatting to set the background color of cells in a table based on the values they contain. For charts, you can add conditional formatting to show the threshold(s) you defined, and the data that falls within them will be shown using the same color.
Apply conditional formatting to a table
You can apply conditional formatting to both table cells and column summaries. You can specify a background color, font color, and/ or font style: bold, italics, underlined, or strikethrough. You can create conditional formatting rules for both measures and attributes.
month of yearcolumn, with values such as
January, but not to date column where dates are in the format
1 Jan 2021.
If you create multiple conditional formatting rules for one measure or attribute, the first rule you create overrides the others, if there is a conflict. To change which rule overrides the others, simply drag and drop the rule to the top of the list of conditional formatting rules.
In the example below, the christmas table cell falls under both the
is christmas rule, and the
not winter rule. Currently, the
not winter rule overrides the
is christmas rule, so the christmas table cell does not have red text and a green background.
To apply conditional formatting to a table:
To create a conditional formatting rule:
Click the edit table configuration icon to the upper right of your table. The Edit table panel appears, on the Configure menu.
Select the measure or attribute you would like to add a conditional formatting rule for.
The Edit panel for that column appears. Under Conditional formatting, click + Add rule. If you would like to add conditional formatting for a column summary, select Summary under the column name, and click + Add rule.Note: When you create a rule for a column, it does not automatically apply to the column summary. You must create a separate rule for the column summary.
Select an operator. The valid options for attributes are
does not contain,
not empty. The valid options for measures are
less than or equal to,
greater than or equal to,
not equal to,
Specify the column value(s) that the formatting should affect.
Choose a background color, font color, and/or font style: bold, italics, underlined, or strikethrough.
In this example, if the
selling seasonis (=)
christmas, the background color for the table cell is green, and the font color is red.
Click Save. The system applies the rule to the column or column summary.
You can edit an existing rule from the Edit panel for the column by clicking on the rule.
You can delete a conditional formatting rule from the Edit panel for the column. Select the delete icon that appears when you hover over a rule.
Apply conditional formatting to a chart
You can use conditional formatting to show charts with a target value or range drawn as a line in the chart, and the legend colors determined by where values fall relative to the target.
To apply conditional formatting to a chart (in this example, Total Sales), follow these steps:
Click the Conditional formatting option in the axis menu.
The Conditional formatting window modal appears.
In the window modal, click + add formatting.
Select the conditional range.
The text area on the left specifies the lower limit of the condition, so the condition applies to all values that are greater or equal to it. And the text area on the right specifies the upper limit of the condition, so the condition applies to all lower values.
To show show values with a lower limit, enter a value in the left text box.
To show values with an upper limit, enter a value in the right text box.
To show values in a specified range, enter the appropriate values in both text boxes.
Here, we apply conditional formatting to values between
To specify a different color of the conditional format, select the new color from the color selector.
This option draws upper and lower limit lines on the chart, and colors the chart elements that meet the conditional requirements.
Alternatively, you can place a range band on the chart. Select the Fill chart option.
To add another condition, click +add formatting.
To remove a defined conditional format, click the delete icon (x) next to its definition.
Here, you can see a chart that highlights elements with conditional formatting on some elements. You can also see how the same chart appears with a background chart band.