Use conditional formatting rules in Data Studio

Display your data in different colors according to conditions you define.

Conditional formatting lets you set the font and background colors used in your charts based on the data. For example, if Campaign is "Summer Sale," then set the entire table row font color to green, or if Clicks is less than 100, set the scorecard background color to red.

You can create 2 types of conditional formats:

  • Single color conditional formatting applies a specific font color and background color to your data.
  • Color scale conditional formatting applies gradations of colors that correspond to minimum, midpoint, and maximum thresholds.

The animation below shows you how to create a single color conditional format.

Single color conditional formatting demonstration.

In this article:

Set up single color conditional formatting

Single color conditions let you evaluate dimension and metric values. Single color format rules can be based on multiple conditions using AND/OR logic. To define a single color format rule:

  1. Edit your report.
  2. Select a table, pivot table, or scorecard.
  3. On the right, click STYLE.
  4. At the top, in the Conditional formatting section, click + Add.
  5. Under Color type, select Single color.
  6. Under Format rules, select a comparison dimension, metric, or the "any field" option (see below).
    1. For example, Campaign or Actual Sales.

      You can format pivot tables by selecting a metric, a row, a column, or any value.
  7. Select a comparison condition.
    1. For example, Equal to or Greater than.
  8. Enter an input value, or select another field to compare to.
    1. For example, "Summer Sale" or Forecasted Sales.
  9. Define the color and style to apply when the format rules are met.
  10. Click Save.

Select any field

You can also define a single color format rule based on any field in the chart:

  1. In step 6 above, scroll to the bottom of the field list, then click Select any field.
  2. The format rule displays "Values," indicating that the rule applies to any values found in the chart.
For scorecards, this is the only option available.

Format rules showing Select any field option,.

  1. Select any field.

Add more conditions to a rule

You can create format rules with multiple conditions. To do this, add a rule, then click Or or And.

For example:

Conditional formatting OR rules example
Source equal to "(direct)" OR Sessions greater than 2000 will format rows that match either condition.

Conditional formatting AND rules example

Source equal to "(direct)" AND Sessions greater than 2000 will format rows that match both conditions.

You can have up to 5 AND clauses. Each clause can have up to 5 OR conditions.

Format rule order

The order in which you specify format rules can be important. Multiple rules in a chart are evaluated in descending order, and the last set of true conditions found is the one that is applied. For example, say you've got three rules in a table:

  • Rule 1 sets both the font color and background color.
  • Rule 2 sets the font color.
  • Rule 3 sets the background color.

When all 3 rules are true, you'll get the font color from rule 2 and the background color from rule 3. Rule 1 will be ignored, as shown below:

Rule order example 1

You can reorder rules using the drag bars on the left. Here's what happens when we move rule 3 to the top:

Rule order example 2

In this example, the background color from rule 1 carries through the following rules and is applied to the table.

Set up color scale conditional formatting

Color scale conditions let you evaluate a single metric against its percentage or absolute numeric value.

To create a color scale:

  1. Edit your report.
  2. Select a table or scorecard.
  3. On the right, click STYLE.
  4. At the top, in the Conditional formatting section, click + Add.
  5. Under Color type, select Color scale.
  6. Under Format based on, select a field in the chart. You can only select a single metric as the basis for a color scale.
  7. Define the color and style to apply when the format rules are met.
  8. Click Save.

Color scale conditional formatting demonstration.

What you can format

You can apply conditional formatting to the following portions of your charts:

Format the entire row

The Entire row option applies the colors you select to every row in the table that matches the condition.

Format a specific field

To format a specific field in a table, select that field from the menu.

For scorecards, or tables using Select any field, this option appears as Cells, and is the only option available.

Color and style example

Drill down dimensions and optional metrics

You can apply formatting rules to drill down dimensions and optional metrics. However, the rules only take effect when those fields are visible in the chart or table.

How to select colors

As noted at the beginning of this article, you can use single colors or a color scale to format your charts.

Single colors

Use the color pickers to select the font color and background color to the target values for each condition. You'll see an example appear on the right.

Color scale

Data points determine the minimum, middle, and maximum values of your color scale. By default, a new color scale defines 3 data points based on percentages, with preset colors (red, yellow, green). You can base the scale on absolute numbers, and select different colors for each data point.

You can have a maximum of 5 data points and a minimum of 2 data points.

  • To add more data points, hover over an existing data point and click Add Add icon.
  • To remove a data point, hover over it and click Delete Remove.

Creating a color scale.

Use preset colors based on your current theme.

Click the down arrow to the right of the color scale preview to select a preset color scale. These preset scales are based on your report theme, making it easier to keep your report visually consistent. The bottom 2 color scales are optimized specifically for visually impaired users.
Preset color scales.
 

Reverse the color scale

To swap the order of the minimum and maximum colors, click Reverse color scale Reverse order.

Format rules, percentages, and decimal precision

When building rules that compare numbers, be sure to use the actual value in the data, rather than the value displayed in charts. For example, to build a format rule that selects values greater than 51.2%, use the decimal value:
 
Bounce rate > .512
 
Similarly, numbers displayed in your charts may be rounded up or down. Be sure to include as many digits as necessary in your rule to properly match the underlying data. For example, a rule such as Bounce rate = .512 wouldn't apply if the actual value was .5119, even though that number appears as .512 when rounded to 3 decimal places.
You can adjust metric decimal precision in the chart's STYLE tab.

Edit and delete conditional formatting rules

  1. Edit your report.
  2. Select the chart with the rule you want to edit.
  3. On the right, Click STYLE.
  4. At the top, in the Conditional formatting section, click Edit Edit , then for the rule you want to change, click Edit Edit or Delete Remove.

Copy conditional formatting rules

There are 2 ways to duplicate conditional formatting rules:

  • Copy and paste a chart with conditional formatting to create a new chart with those same rules.
  • Copy a chart with conditional formatting, then use Edit > Paste special > Paste style only to apply that chart's conditional formatting to an existing chart.

Limits of conditional formatting

  • Single color conditional formatting is currently available for tables, pivot tables, and scorecards.
  • Color scale conditional formatting is currently available for tables and scorecards.
  • Conditional formatting on pivot tables that use Expand-Collapse only works if the rule is based on "Any value."
  • Format rules can only use fields currently contained in the chart.
  • You can have up to 20 format rules per chart.
  • If you switch a chart's visualization to a different type (for example, a table to a scorecard), conditional formatting will continue to work as long as the fields and values used in the formatting rules still exist in the new chart. Rules based on fields or values that aren't in the new visualization are ignored.
Was this helpful?
How can we improve it?

Need more help?

Sign in for additional support options to quickly solve your issue