Apply conditional formatting

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.

If advanced conditional formatting is enabled in your environment, you can apply conditional formatting rules to tables and charts to compare values to another column, as well as to a particular value. To enable this feature, contact your administrator.

Understand conditional formatting

Many companies create Liveboards 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. For pivot tables, you can only create conditional formatting rules for measures.

Pivot tables follow the same conditional formatting rules as tables, even though they fall under ThoughtSpot’s chart category. However, you cannot set different conditional formatting rules for pivot table cells and pivot table column summaries.

You can only apply conditional formatting to numbers and strings. For example, you can apply conditional formatting to a month of year column, with values such as January, but not to date column where dates are in the format 1 Jan 2021. For pivot tables, you can only apply conditional formatting to measure, not attributes.

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 following example, 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.

Drag and drop the rule you would like to override other rules

To create a conditional formatting rule:

  1. Select the edit table configuration icon Table configuration icon image to the upper right of your table. The Edit table panel appears, on the Configure menu.

  2. Select the measure or attribute you would like to add a conditional formatting rule for.

  3. The Edit panel for that column appears. Under Conditional formatting, select + Add rule. If you would like to add conditional formatting for a column summary, select Summary under the column name, and click + Add rule.

    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.
    Conditional formatting — add rule

  4. Select an operator. The valid options for attributes are is, not, contains, does not contain, starts with, ends with, empty, and not empty. The valid options for measures are less than, greater than, less than or equal to, greater than or equal to, equal to, not equal to, between, empty, and not empty.

  5. Specify the column value(s) that the formatting should affect.

  6. Choose a background color, font color, and/or font style: bold, italics, underlined, or strikethrough.

    In this example, if the selling season is (=) christmas, the background color for the table cell is green, and the font color is red.

    Conditional formatting example

  7. Select Save. The system applies the rule to the column or column summary.

  8. You can edit an existing rule from the Edit panel for the column by selecting the rule.

  9. 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.

    Delete a conditional formatting rule
If you change to a chart type, you must apply conditional formatting again. Conditional formatting is tied to the specific visualization.

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 Revenue by Supplier Country), follow these steps:

  1. Select the edit chart configuration icon Conditional formatting icon to the upper right of your chart. The Edit chart panel appears, on the Configure menu. Alternatively, you can select the Conditional formatting option in the axis menu for the measure you would like to add a conditional formatting rule for. If the new Answer experience is off in your environment, you can only access chart conditional formatting from the axis menu.
    If the advanced conditional formatting feature is on in your environment, you can compare the values of your chosen column to other columns as well as to static thresholds.

  2. From the Edit chart menu, select the measure you would like to add a conditional formatting rule for.

  3. The Edit panel for that column appears. Under Conditional formatting, select + Add rule.

  4. Select an operator. The valid options for measures are less than, greater than, less than or equal to, greater than or equal to, equal to, not equal to, and between.

  5. Select the conditional value, or in the case of the between operator, the conditional range. Here, we apply conditional formatting to revenue values between 100 million and 125 million.

    Conditional formatting: add rule

  6. To specify a different color of the conditional format, choose 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.

    Change conditional format color

    Alternatively, you can place a range band on the chart. Select the Fill chart option.

    Add conditional format band

  7. To add another condition, select + Add rule below the rule(s) you already created.

  8. To remove a defined conditional format, navigate to the Edit panel for the measure. Select the delete icon that appears when you hover over a rule.

  9. Select Done.

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.

Conditional formatting applied

Limitations

The following chart types do NOT support conditional formatting:

  • Funnel

  • Geo area

  • Geo bubble

  • Geo heatmap

  • Heatmap

  • Donut

  • Radar

  • Sankey

  • Treemap

Early Access

Advanced conditional formatting

Rather than simply using conditional formatting comparing a column’s measures to a single value (for example, sales > 10000), you can now use conditional formatting to compare a column’s measures to another column. For example, if you search for sales this year versus sales last year, you can highlight where sales this year were less than last year. You can set multiple conditional formatting rules to a single table or chart. The new Conditional formatting window allows you to manage all conditional rules in one place.

To enable this feature, contact your administrator.

To create a conditional formatting rule on a chart or table, follow these steps:

  1. On a table, select the more options menu icon ellipsis icon to the right of your selected column and choose Conditional formatting from the dropdown menu. On a chart, open the edit chart configuration menu gear icon, select the tile for your selected column, and click Edit under Conditional formatting.

  2. In the Conditional formatting pop-up window, select + Add condition.

  3. Choose whether to set a Value based or Column based condition.

    Value-based conditions let you compare the column’s values to a certain numerical value. Column-based conditions let you compare the column’s values to the values of another column.

  4. From the left dropdown menu, you can choose whether to change the column you’re basing the condition on.

  5. From the center dropdown menu, select an operator. The valid options for attributes are is, not, contains, does not contain, starts with, ends with, empty, and not empty. The valid options for measures are less than, greater than, less than or equal to, greater than or equal to, equal to, not equal to, between, empty, and not empty.

  6. For value based conditions, use the left option to enter your desired value. For column based conditions, use the left dropdown menu to select the comparing column.

  7. Choose a background color, font color, and/or font style: bold, italics, underlined, or strikethrough.

  8. [Optional] For rules on a table, you can choose to highlight the entire row for all values that fit the condition.

  9. Select Apply. The system applies the rule to the column.

  10. You can edit an existing rule from the Edit panel for the column by selecting the rule.

  11. You can delete a conditional formatting rule from the Edit panel for the column. Select the rule from the Edit panel and click Remove.

Related information

Was this page helpful?
×