Notification

This article is about Looker Studio. For Looker documentation, visit https://cloud.google.com/looker/docs/intro.

Use conditional formatting rules in Looker Studio

Color your data based on logical conditions.

You can 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 apply conditional formatting rules only to table charts, pivot table charts, and scorecard charts

A user adds conditional formatting rules for a table chart to highlight, in orange, rows that contain the term Chrome in the Browser column.

In this article:

Single color conditional formatting

Single color conditional formatting applies a specific font color and background color to your data. Single color conditions can include:

  • Dimensions
  • Metrics
  • Multiple conditions using AND/OR logic.

Use single color formatting on a table

  1. Edit your report.
  2. Select a table.
  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 option:
    • Dimension - compare to a dimension value.
    • Metric - compare to a metric value.
    • Select any field - compare to any value in the chart (see below).
  7. Define a comparison condition.
    • For example, Contains, Equal to, Empty.
  8. Enter an input value.
    • For metrics, you compare to a literal value or another metric in the chart.
    • For example, "Summer Sale" or Forecasted Sales.
  9. Define the colors to apply when the format rules are met.
  10. Click Save.

Use single color formatting on a pivot table

Pivot tables only support single color formatting.

  1. Edit your report.
  2. Select a pivot table.
  3. On the right, click STYLE.
  4. At the top, in the Conditional formatting section, click + Add.
  5. Under Format rules, select a comparison option:
    • Row - compare to a row dimension value.
    • Column - compare to a column dimension value.
    • Metric - compare to a metric value.
    • Select any field - compare to any value in the chart (see below).
  6. Define a comparison condition.
    • For example, Contains, Equal to, Empty, or Greater than.
  7. Enter an input value.
    • For metrics, you compare to a literal value or another metric in the chart.
  8. Define the colors to apply when the format rules are met.
  9. Click Save.

Use single color formatting on a scorecard

  1. Edit your report.
  2. Select a 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, the Values option is selected and can't be changed. This option automatically uses the current scorecard metric as the basis of the formatting rule.
  7. Define a comparison condition.
    • For example, Equal to, Is True, or Greater than.
  8. Enter an input value.
  9. Define the colors to apply when the format rules are met.
  10. Click Save.

Select any field

The "Select any field' option lets you define a single color format rule based on any field in the chart.

The Format rules field selector on the Create rules conditional formatting panel displays available fields, including the Select any field option.

  1. Select any field.

Color scale conditional formatting

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

Use color scale formatting on a table or pivot table

  1. Edit your report.
  2. Select a table or pivot table.
  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. Select the part of the chart to format. 
    1. For tables, you can format individual fields, or the entire row.
    2. For pivot tables, you can only format one of the metrics in the chart.
  8. Define the colors to apply when the format rules are met.
  9. Click Save.

A user adds conditional formatting rules for a table chart to color the Pageviews column cell values along a color gradient.

Use color scale formatting on a scorecard

  1. Edit your report.
  2. Select a 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 rules, the Values option is selected and can't be changed. This option automatically uses the current scorecard metric as the basis of the formatting rule.
  7. Define the colors to apply when the format rules are met.
  8. Click Save.

Compound format rules

Conditional formatting rules can include multiple conditions using OR or AND logic.

For example:

The Format rules section on the Create rules panel displays the conditions Source is equal to (direct) OR Sessions is greater than 2000.
Source equal to "(direct)" OR Sessions greater than 2000 will format rows that match either condition.

The Format rules section on the Create rules panel displays the conditions Source is equal to (direct) AND Sessions is greater than 2000.

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:

Conditional format rules menu with three rules defined to change the text and background color when certain conditions are met.

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

Conditional format rules menu displaying Rule 3 rearranged to the first rule position.

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

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.

The Color and style section on the Create rules conditional formatting panel for a table chart displays field and style options.

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.

The Create rules conditional formatting panel displays default formatting rules for three data points.

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.
A user selects the color scale drop-down menu in the Color and style section of the Create rules panel to display preset color scale options.
 

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."
  • Dimension-based compound conditions in pivot tables must use the same field in each OR condition.
  • Format rules can only use fields currently contained in the chart.
  • You can have up to 20 format rules per chart.
  • Switching between chart visualization types (for example, switching a table to a scorecard, or a pivot table to a table) may require you to update conditional formatting rules, depending on the type of chart and the fields used in the rule.

Was this helpful?

How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
997497904679489433
true
Search Help Center
true
true
true
true
true
102097
false
false