Data Studio does not offer conditional formatting in table charts. But it's easy to create a column with a visual indicator.

As we saw previously, you can add images to tables. We will use the same technique to compose the urls of the icons depending on the value of a field.

Further down on this page, we give you 5 more examples: up/down arrow, star, rating, customer satisfaction, and error/warning.


Step 1: Get our icons

The color of the text in a table chart can't be controlled with a formula. Our workaround uses colorful icons:

To display icons in your reports, the corresponding images need to be accessible somewhere on the Internet.

We could tell you to download a pack of icons and figure where to host them yourself. But we made it easier: You can use our images.

Fill the form below to receive an email containing the public location of our icons. We need your email address so that we can contact you if we ever need to move the images somewhere else, or stop offering this free service.

Receive the address of our icons

Enter your email to receive the url of our icons in the next minutes. This is the fastest way to have access to images hosted online that you can use in the next steps:

No spam, ever. Unsubscribe anytime. We won't share your email with anyone.

If you don't receive our email, contact us at [email protected].

These icons:

  • Are from this official icon set from Google, which is free to use and modify.
  • Are available under Apache license version 2.0.
  • Use colors from Google's Material Design guidelines.

Step 2: Choose your formatting

Decide which icon to use. In this first example, we want one icon for each possible value of a 'Status' field associated to an order:

Step 3: Create your condition

Create a new calculated field on your data source. Give it a name, then write a formula like this one:

Replace https://example.com with the address received in Step 1.

It uses 2 functions:

1- IMAGE, to create an image field.

2- CASE, to create the conditional logic needed to choose the right icon depending on the value of 'Status'.

Step 4: Add the column to your table

Add the new country flag image field to your table chart as a dimension:

Resize the column to adjust the icon size, and you're done!

You may want to hide the column header, as it will mostly be truncated anyway. To do so, click the image icon (1):

And set the name as '-' (2):


Here are 5 other examples:

Example 1: Up / down arrow

Using arrows to highlight a positive or negative evolution in revenue:

If you're wondering how the columns can be in this order, read this note.

Field: 'Variation' is a Percent field. It contains values like: ‌‌0.3872, 0.4377, -0.1728 ...

Icons: arrow_drop_up, arrow_drop_up, transparent.

Condition:


IMAGE(
  CASE
    WHEN Variation > 0 THEN 'https://example.com/arrow_drop_up.png'
    WHEN Variation < 0 THEN 'https://example.com/arrow_drop_down.png'
    ELSE 'https://example.com/transparent.png'
  END
)

Replace https://example.com with the address received in Step 1.

Example 2: Star

Using a star to indicate which customers have spent more than $1,000:

Field: 'Total value' is a Currency field, and contains values like: ‌‌28, 1679, 120 ...

Icons: star, star_border.

Condition:

IMAGE(
  CASE 
    WHEN Total value > 1000 THEN 'https://example.com/star.png'
    ELSE 'https://example.com/star_border.png'
  END
)

Replace https://example.com with the address received in Step 1.

Example 3: Rating

Using stars to illustrate product review scores:

Field: 'Stars' is a Number field, going from 0 to 5. It contains values like: 4.2, 5, 2.9. It can be NULL when a product has no reviews.

Icons: stars_0, stars_1, stars_2, stars_3, stars_4 , stars_5, stars_null.

Condition:

IMAGE(
  CASE 
    WHEN Stars = 5 THEN 'https://example.com/stars_5.png'
    WHEN Stars >= 4 THEN 'https://example.com/stars_4.png'
    WHEN Stars >= 3 THEN 'https://example.com/stars_3.png'
    WHEN Stars >= 2 THEN 'https://example.com/stars_2.png'
    WHEN Stars >= 1 THEN 'https://example.com/stars_1.png'
    WHEN Stars >= 0 THEN 'https://example.com/stars_0.png'
    ELSE 'https://example.com/stars_null.png'
  END
)

Replace https://example.com with the address received in Step 1.

Example 4: Customer satisfaction

Using a face icon to represent the satisfaction of customers who replied to a survey:

Field: 'Satisfaction' is a Number field, going from 0 to 10.

Icons: sentiment_very_satisfied, sentiment_satisfied_alt, sentiment_satisfied, sentiment_dissatisfied, sentiment_very_dissatisfied.

Condition:

IMAGE(
  CASE 
    WHEN Satisfaction >= 9 THEN 'https://example.com/sentiment_very_satisfied.png'
    WHEN Satisfaction >= 7 THEN 'https://example.com/sentiment_satisfied_alt.png'
    WHEN Satisfaction >= 5 THEN 'https://example.com/sentiment_satisfied.png'
    WHEN Satisfaction >= 3 THEN 'https://example.com/sentiment_dissatisfied.png'
    ELSE 'https://example.com/sentiment_very_dissatisfied.png'
  END
)

Replace https://example.com with the address received in Step 1.

Example 5: Error / Warning

Using a sign to indicate when a product has low stock, or is out of stock:

Field: 'Stock' is a Number field. It contains values like: 2, 0, 8, 26 ...

Icons: error, warning, transparent.

Condition:

IMAGE(
  CASE 
    WHEN Stock = 0 THEN 'https://example.com/error.png'
    WHEN Stock < 3 THEN 'https://example.com/warning.png'
    ELSE 'https://example.com/transparent.png'
  END
)

Replace https://example.com with the address received in Step 1.


Did you know ?

You can combine this technique with a heatmap. Here is an an example:

To activate the heatmap, click the arrow next to your chart's type (1):

Select "Table with heatmap" (2):

Then finally pick a color to use for the gradient in the style tab (3):


Conclusion

We hope that "real" conditional formatting will soon be available in Data Studio. The corresponding feature in Google's issue tracker looks assigned, so the team must be working on it.

Until then, we hope that our solution helps. If you need more icons, let us know!


Note about column orders

Added on September 27, 2019

Some readers asked how our examples can have images displayed on the right of all other columns:

We were able to get this result by using dimensions instead of metrics:

... and by aligning the contents of these columns to the right: