Visualizations are a powerful tool to give your data a voice, transforming rows and columns into insights you can act on. However, even the clearest dashboard can sometimes miss the opportunity to shout out the most critical data points.
Imagine you’ve created a dashboard of sales figures to share with a stakeholder who has a limited amount of time to review and interpret the data. As the author of the dashboard, you want them to walk away feeling they got the critical information they needed and that you, as an expert, were able to optimize for their understanding and time.
Conditional formatting to the rescue
Both biology and society have hard-wired us so colors draw our attention and, in some cases, elicit a specific response.
In nature, getting a close-up look at the jarring yellows and oranges of a coral snake amongst the cooler greens and browns on a forest floor might scream “danger”. Back in the office, the bright red or yellow formatting of a number on a black and white spreadsheet likely elicits the same response. In a dynamically updating dashboard, with up-to-date data and viewer controls, numbers can change – cue the “conditional” in “conditional formatting”.
By using conditional formatting effectively, you can draw a viewer’s eyes to the “danger” ( important metrics that are dangerously below target) that needs attention in a sea of data, or let them know with an airy green or blue when everything is “A-OK”. Conditional formatting lets you tap into these visceral responses to focus your viewer and aid in rapid visual identification.
What’s new in conditional formatting in Looker Studio?
Conditional formatting has always transformed how you convey data in tables within Looker Studio. You could use conditional formatting to create rules to set a background color for cells or even to define a gradient to use for background colors, based on the value of a metric.
Looker Studio table showing “total_sales” values formatted red when under “sales_target” and “sales_target” cells formatted in a gradient of green based on the value.
We are now extending this capability to two of our most fundamental visualization components: text boxes (via query result variables) and bar charts. This update makes it easier to bring your most important data to the forefront, creating a dynamic and intuitive dashboard experience.
Query result variables conditional formatting
You can use conditional formatting for chips in text boxes to change the text color or background color to a defined color, or across a color scale. In the variable editing window, click “+Conditional formatting” (or “+Add” if a rule already exists) to add a conditional formatting rule.
You can preview the results for your rule(s) in both the table and in the chip “Preview”.
The formatting will change dynamically in the text box based on the rules specified.
Bar chart conditional formatting
You can also add conditional formatting rules to change bar colors and labels in bar charts.
In the “Style” panel for a bar chart, look for “+Add formatting” under the “Conditional formatting” section to add a conditional formatting rule.
The bar color and/or label color will adjust on the bar chart according to your data and the rules specified.
More ways to supercharge your reports with conditional formatting
1. Drive attention to important data points
Use “Rules” for data labels to only show important data labels – Create a rule to change the color of data labels you want to highlight. Create a separate rule (or rules) to change the color of other data labels to “transparent”.
Use “Color Scale (Gradient)” with custom points to highlight top / bottom values – Use “Percent” to create Point #1 at 0 percent with a desired color for the bottom value, create Point #2 with the neutral color, create Point #3 with the neutral color, and create Point #4 with the desired color for the top value. By constraining the colors to the top and bottom extremes, you effectively only color the top and bottom values within the 99-100% range and 0-1% range of the metric, respectively. Note if you have multiple values that fall within those extremes, you may need to constrain it even further.
2. Convey sentiment
Use “Rules” for colors to convey positive or negative sentiment – A common finance term to indicate a company is profitable is “in the green”. Create a rule to use the green color for values that meet the criteria for a positive result. In the same sense, use red for negative results to quickly draw attention to and indicate sentiment that should be applied to a data point. Using colors can be particularly helpful as in some cases a lower value may be good (e.g., reduced churn), so using green may make it clear this is a positive result.
3. Consistent color-coding
Use “Rules” for colors to match dimension colors for better visual association – Create rules using a specified color when the data point applies to a dimension
Some best practices to keep in mind
-
Legends are still important – As you create conditional formatting rules, be mindful the default legend generated for the chart may no longer apply – you may want to disable the default legend and manually create one using a text box to show viewers how to interpret what they see in your chart.
-
Less is more – As with any great tool, you want to use it in moderation. Overly busy visualizations or dashboards can distract the user from taking in the most important points. Use conditional formatting only when it helps the user better understand the data or focus on important points.
-
Keep your audience in mind – The meaning and emotional responses associated with colors can vary significantly across cultures, so keep your audience in mind as you determine what colors to use to evoke specific emotions. In addition, you want to be mindful about the accessibility of any colors you may choose in your formatting.
To learn more about conditional formatting in Looker Studio, visit our help center.