Conditional Formatting in Data Studio
Hello, everyone! I’m Nicole Page, Director of Data Strategy and Analytics at Ednovate, a network of schools located in Southern California. Our mission is built on the idea of creating positive multigenerational change. What this means is that in addition to providing rigorous coursework that sets a student up academically to succeed in life after high school, we also incorporate year-long themes such as Know Your Community into the curriculum so our students leave us as thoughtful, curious human beings ready to create impact in their families, communities, and the world around them.
Critical to us as we look to know if we are being successful as an organization is our library of data dashboards and visualizations. Being in DataViz Starter Pack means we have a series of analytic views housed in our SQL database and Google Sheets. DataViz currently has Illuminate SIS and DeansList data flowing into Google Data Studio for us so I can build the reports that matter to our teachers and students.
One feature that I’ve always looked for in Google Data Studio has been the ability to create a data table where the cells are conditionally formatted red, yellow, or green depending on the metric. Unfortunately this is not available out of the box in Data Studio. Judging by my frequent Googling, there are plenty of Data Studio users who are in need of this feature as well.
I was able to make it work through a few clever hacks using calculated fields and layering data tables on top of each other. Here is a look at how it’s done in hopes that others find it helpful and want to implement something similar.
The table you see above is actually 4 tables in one. For each data column that I want conditionally formatted, I’ve created 3 calculated fields (ex. ADA Green, ADA Yellow, ADA Red).
ADA Red looks like this:
If a student has an average daily attendance lower than 90%, this field will have a value of 1000. Otherwise, it will have a value of 0. When you select a table in Data Studio, under the Style properties you can set a metric to show a heat map in addition to a number.
Set the text color to #61616100 so that it is transparent and you are left with:
Now all you have to do is layer the tables on top of each other and you have a data table that is conditionally formatted! As the data source updates, all 4 tables will update as well.
An important thing to note: data filters will work as expected, but pagination and scrolling will not. If you create a filter for grade level and your user selects a single grade level, all 4 tables will be filtered. However, there is no way to page through the results in a way that all data updates. (That said, I’m hopeful that one of you may be able to come up with a clever solution for that, too, and pass it along!) Currently, when building reports, I use this feature when I have a data table that can display all rows in one visual.
I hope you found this clever hack useful - happy building!