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.
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.
If you don't receive our email, contact us at [email protected].
- 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:
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:
Field: 'Variation' is a Percent field. It contains values like: 0.3872, 0.4377, -0.1728 ...
Icons: arrow_drop_up, arrow_drop_up, transparent.
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 )
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.
IMAGE( CASE WHEN Total value > 1000 THEN 'https://example.com/star.png' ELSE 'https://example.com/star_border.png' END )
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.
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 )
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.
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 )
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.
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 )
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):
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: