Conditional Formatting
Under Target? It Turns Red.Conditional formatting for charts: bars under target change color as they’re drawn.
Set a rule once, such as below target turns red, and every bar, slice or pivot cell that breaks it changes color as the chart is drawn. A board on the wall gets read from across the room, so a supervisor walking past sees the problem without stopping to read the axis.
- Seven ways to compare
- First matching rule wins
- Bars, slices and cells
How it works
- A chart is drawnEach bar, slice or cell in turn
- Rules are checkedIn order; the first match wins
- No rule matchesKeeps its usual colorA rule matchesTakes that rule’s color
- Seen from across the roomThe miss stands out on the wallboard
Problems You See Before You Read.
Anything under target changes color as the chart is drawn, so nobody has to stop and read the axis.
Use “between” rules for middle bands, and order the list so the stronger color wins where bands overlap.
Point a rule at revenue and it leaves the count plotted beside it alone.
Pivot cells follow the same rules, so a weak week reads like a short bar.
Color Rules in Three Steps.
Write the Rule
Pick a comparison, such as below or between, type the number and choose a color.
Put the Rules in Order
Strongest first. Each bar takes the color of the first rule it matches.
Aim It, or Leave It
Point a rule at one measure, or leave it to apply to every measure on the tile.
Three Common Rules, Seven Comparisons.
There are seven comparisons in all: below, at or below, above, at or above, equal, not equal, and between.
Below, or at or below. The classic: anything under target turns red.
Above, or at or above. Flag anything that runs over a limit.
Color the middle band amber, with red and green rules on either side.
What Is Conditional Formatting for Charts?
Conditional formatting for charts changes a chart’s colors based on its numbers. Instead of reading every bar against the axis, you see at a glance which ones broke a rule, such as red below target.
In Adoptiv a rule is a comparison, a number and a color. There are seven comparisons: below, at or below, above, at or above, equal, not equal, and between two numbers. Each bar, slice or cell walks the list and takes the color of the first rule it matches; anything matching nothing keeps its palette color from theme sets.
Rules work wherever a chart draws one mark per row: columns and bars, stacked or not, pie and donut slices, funnel bands, treemap tiles, radial arcs, and scatter and bubble points. Conditional formatting in pivot tables works the same way, cell by cell. Lines, areas, combo and radar charts don’t take rules, and nor do gauges, KPI cards or plain tables.
Red amber green bands come from three rules in the right order. To highlight values below target and also show where the target sits, pair a rule with a target line. Rules change colors in your browser, never the numbers, so color-coded charts count exactly what plain ones do.
Color-Coded Charts, Set Once.
Below, at or below, above, at or above, equal, not equal, between.
The order of your rules decides which color shows where bands overlap.
A hex code, an RGB value or one of your theme’s colors.
Aim a rule at a single measure, or let it apply to all of them.
Rules change colors, never the figures behind them.
For Boards That Get Read from Across the Room.
Conditional Formatting Questions, Answered.
What is conditional formatting for charts?
Rules that change a chart’s colors based on its numbers, such as turning any bar under target red. In Adoptiv each rule is a comparison, a number and a color, checked for every bar, slice or cell as it’s drawn.
Which charts can use color rules?
Columns and bars (stacked too), pie, donut, funnel, treemap, radial bar, scatter and bubble plots, and pivot table cells. Lines, areas, combo and radar charts, gauges, KPI cards and plain tables don’t take rules.
What happens when two rules overlap?
The first rule in the list that matches wins. Put the rule you care about most at the top.
Can I use my own colors?
Yes. A rule’s color can be any web color, such as a hex code, or one of your theme’s own colors.
How do I make bars red below target?
Add a rule: below, your target number, and red. Every bar under the number is drawn red; the rest keep their usual color.
Can I set red, amber and green bands?
Yes. Use a below rule for red, a between rule for amber and an above rule for green, ordered so the right one matches first.
Why doesn’t my line chart change color?
Rules color one mark per row, and a line or an area is drawn as one continuous stroke. Use a column chart, or add a target line instead.
Does conditional formatting change the data?
No. Rules run in your browser after the numbers arrive, and they only change colors.
Still have a question? A real person replies in under 60 seconds in US business hours. Talk to our team →
14-day free trial
Let the Wallboard Flag the Misses.
Try color rules on your own numbers in the free trial. Free for 14 days, no credit card.
14-day trial · no card · migration included