In this first ever series of Up Your Data Studio Game, we bring you ways to use conditional formatting in your dashboard (or dashboards. If you’re reading this you definitely have more than one, right??). What’s conditional formatting you may ask? It’s similar to what’s available in Excel: formatting the cell when the condition is met.
In Data Studio, we can create 2 types of conditional formats:
- Single colour conditional formatting - a specific font colour and/or background colour to your data
- Colour scale conditional formatting - gradations of colours that correspond to minimum, midpoint, and maximum thresholds.
Conditional Formatting can be used in 2 chart formats: scorecards and tables. If you think about it, these two aren’t the most shouty of charts hence they need all the help they can get. Plus, scorecards and tables are up there on most commonly used charts. Note: as of August 2020, pivot tables have conditional formatting enabled!
Where to find it: In Edit mode > select chart > Style tab
Clicking on +Add brings up the rules pop-up:
There is a maximum of 20 rules per chart and this is something to keep in mind: Multiple rules in a chart are applied in a descending order. One easy way to remember this is to arrange the rules from broad to specific.
Single colour conditional formatting aka single colour fun
Your typical scorecard, and
Your typical scorecard with conditional formatting.
Now obviously you condition it for a reason. Let’s say I want to know when my website records more than 100 Sessions.
I can choose to show this achievement by highlighting either the text or cell.
That’s good if the colour olive means something significant to your brand or business, but what if I want plain yes or no, good or bad? I can do green and red right?
Clashing colours, inscrutable, plus what if someone in your audience is slightly colourblind? This is starting to look like an eye test.
You get the idea.
This is what you can do instead to make it easy on the eyes. Plus a note explaining that conditional formatting applies on the scorecards, helps.
How about tables? Here’s what we can do on tables.
In my example I’ve used conditional formatting to highlight different categories of the website’s pages, but it can be used with search terms, or keywords.
Text conditional formatting:
How to do it:
Background conditional formatting
How to do it:
I personally prefer the text conditional formatting; it’s less jarring to the eyes without feeling overwhelming.
I can also highlight the pages which have 100-200 page views.
How to do it:
A note on formatting percentages: When creating rules to compare numbers, make sure to use the actual value in the data rather than the value displayed in the charts. For example, I want to highlight pages greater than or equal to 5%.
Use the decimal value instead:
Numbers can be rounded up and down, so be sure to include as many digits as necessary in the rules to ensure it matches properly to the underlying data. Do adjust the metric decimal precision under the Style tab.
Colour scale conditional formatting aka gradient fun
You know this one.
Here I’ve used it as a heatmap on monthly Sessions.
How to do it:
1. Select a metric
2. Choose a colour scale and input minimum, midpoint, and maximum thresholds
These preset scales are based on your report theme, making it easier to keep your report visually consistent. The bottom 2 colour scales are optimised specifically for visually impaired users.
These colour scales are from two different dashboards which have different themes.
Combine conditional formatting with other charts
Conditional formatting featuring Bullet chart
Bullet charts are a variation of the bar chart to replace gauges and meters. On dashboards we can use them to track targets. If you haven’t used bullet charts before, you can set 3 ranges to indicate how close you are to your target.
What I’ve set up on the chart
- Range 1: 1,000
- Range 2: 3,000
- Range 3: 10,000
- Target: 5,000
You can also set a date comparison with the previous year, for example, to measure this year’s performance. The comparison will show up as a lighter shade of the target colour.
Here’s where you can use conditional formatting: I’ve set up a scorecard layered over a textbox. The textbox will remain static, whereas the scorecard is dynamic and updates when the date range changes. This adds a layer of interactivity to the chart.
Limits of conditional formatting
- Available only on tables and scorecards
- Rules apply to fields within the chart
- 20 format rules per chart
- Fields in rules have to be updated manually when the chart fields are changed
When Data Runs Deep creates dashboards for clients, we usually follow the client’s brand colours and styles. Green and red (or other colours) will either stick out like a sore thumb or blend in with your chosen colours on your beautifully designed dashboard. Less is more when table colours are applied!
Colour is a wonderful tool when used well. Wield it wisely. The power is in your hands, and all that.