A matrix with 200 cells of raw numbers is unreadable. The same matrix with conditional formatting reveals patterns instantly — high values stand out, low values fade, and outliers draw the eye. This guide covers the four formatting types in Power BI and the rules for using them well.
The four formatting types
1. Background color scaling
Shades each cell based on its value, from a minimum color (e.g., light) to a maximum color (e.g., dark).
When to use: comparing values across a range, where relative magnitude matters.
How to apply: click the dropdown next to a measure in a visual → Conditional formatting → Background color.
Settings:
- Color scale: gradient from min to max. Choose two colors (e.g., white to blue) or three (e.g., red for low, yellow for mid, green for high).
- Minimum / Maximum: “Lowest value” / “Highest value” for dynamic scaling, or specific numbers for fixed thresholds.
- Diverging: enable if you have a meaningful midpoint (e.g., 0 for profit/loss, target for actual-vs-budget).
2. Font color scaling
Same as background, but changes the text color instead of the cell fill.
When to use: when background color would clash with other formatting, or when you want subtler emphasis.
Tip: use font color with a light background, or background color with dark text. Both together is usually too much.
3. Data bars
Draws a horizontal bar inside each cell, proportional to the value — like a mini bar chart within the matrix.
When to use: when you want both the exact value and a visual comparison.
Settings:
- Bar color: choose a single color or a gradient.
- Border: add a subtle border to separate the bar from the value.
- Axis: show or hide a vertical axis line at the zero point.
Data bars work best in narrow columns where the bar fills most of the cell width.
4. Icons
Adds an icon next to the value — arrows up/down, circles, flags, traffic lights.
When to use: highlighting direction (up/down) or status (good/warning/bad).
Settings:
- Icon style: arrows, circles, flags, or shapes.
- Rules: define thresholds (e.g., green arrow if value > 1000, yellow if 500–1000, red if < 500).
- Layout: icon only, icon left of value, or icon right of value.
Caution: icons are the most overused formatting type. Three icons per cell across 200 cells is visual noise. Use them sparingly — for status indicators, not for every value.
Pattern 1: Highlighting performance vs. target
A matrix showing actual revenue by region vs. target. Use diverging background color with the midpoint at 100% (on target).
- Below 80%: red
- 80–100%: yellow
- Above 100%: green
// Measure used for formatting
Revenue vs Target Pct = DIVIDE([Total Revenue], [Target Revenue], 0)
Apply background color scaling to this measure with diverging colors and a midpoint at 1.0 (100%).
Pattern 2: Trend indicators with icons
A table showing monthly revenue with a “trend” column. Use icons to show whether each month is up or down vs. the previous month.
// Measure for the trend column
Revenue Trend =
VAR Current = [Total Revenue]
VAR Previous = CALCULATE([Total Revenue], PREVIOUSMONTH(Date[Date]))
VAR Diff = Current - Previous
RETURN
SWITCH(
TRUE(),
Diff > 0, 1, // up
Diff < 0, -1, // down
0 // flat
)
Apply icon formatting with rules: 1 → green up arrow, -1 → red down arrow, 0 → gray circle.
Pattern 3: Data bars in a matrix
A matrix showing revenue by product per month. Add data bars to the revenue values so each cell shows both the number and a proportional bar.
This combines the precision of numbers with the visual comparison of a bar chart — ideal for detailed analysis tables.
Pattern 4: Highlighting outliers
Use field-based formatting to highlight cells that meet a specific condition (e.g., revenue above a threshold).
// Measure that returns a color hex code
Revenue Color =
IF([Total Revenue] > 50000, "#FF6B6B", "#FFFFFF")
Apply this as the background color via “Field value” formatting. Cells above 50,000 get a red background; others stay white.
Design principles
Principle 1: One formatting type per cell
Don’t combine background color, font color, data bars, and icons on the same value. Choose one that fits the goal.
Principle 2: Use color intentionally
- Red/green: performance vs. target (but be mindful of colorblindness — use icons as a backup).
- Blue scale: magnitude comparisons.
- Single color with intensity: clean, professional look.
Principle 3: Don’t format everything
Conditional formatting on every cell of every visual is exhausting. Apply it where comparison adds value — matrices, tables, and specific KPIs. Leave cards and chart visuals alone.
Principle 4: Test for colorblindness
Red/green is the most common formatting choice and the most common accessibility failure. Use a colorblind-friendly palette (e.g., blue/orange) or add icons as a redundant signal.
Common mistakes
Mistake 1: Using default colors without checking contrast. A light yellow font on a white background is invisible. Test formatting with actual data.
Mistake 2: Forgetting to handle blanks. Blank cells may show formatting (or not) depending on the rule. Use “Show a blank value” in the formatting settings to control this.
Mistake 3: Over-formatting small tables. A table with 5 rows doesn’t need conditional formatting — the numbers are already comparable. Save formatting for tables and matrices with 20+ cells.
Mistake 4: Not updating formatting after changing measures. If you swap the measure in a visual, the conditional formatting may point to the old measure. Check formatting settings after any measure change.
Summary
Conditional formatting turns walls of numbers into scannable visuals. Use background color for magnitude comparisons, data bars for combined precision and visual comparison, and icons for direction or status. Apply one formatting type per cell, choose colors intentionally, and test for accessibility. The goal is to make patterns visible — not to color every cell for the sake of it.