Conditional Formatting in Power BI: A Practical Guide

Conditional formatting turns a table of numbers into something a reader can scan: losses in red, targets met in green, the largest values marked with bars. In Power BI it applies to the values, totals and subtotals of tables and matrices, and to several other visuals such as bar charts and cards (Microsoft Learn: conditional table formatting). Used carefully, it directs attention; used everywhere, it becomes noise. This guide covers the options, one setting that silently produces wrong colours, and how to keep formatted tables readable.

How to apply it

Select a table or matrix, then in the Visualizations pane open the menu on the field you want to format and choose Conditional formatting. The options are background colour, font colour, data bars, icons and web URL. Several can be combined on one column, and conditional formatting overrides any fixed colour you set on those cells.

For background and font colour there are three format styles:

The formatting can be based on the field being formatted or on any other numeric field in the model, so a sales column can be coloured by margin.

The Percent versus Number trap

When you write rules for a field that holds percentages, Microsoft's documentation says to enter the thresholds as decimals, such as 0.25 for 25%, and to select Number, not Percent. Percent sets each boundary as a share of the range between the lowest and highest values in the visual, which is a different thing. The effect is easy to miss, so here it is on real numbers: margins by segment and country from Microsoft's Financial Sample (Microsoft Learn: Financial Sample), with rules intended as red below 10%, amber from 10% to 20% and green above.

import io
import urllib.request
import pandas as pd

url = "https://go.microsoft.com/fwlink/?LinkID=521962"
finance = pd.read_excel(io.BytesIO(urllib.request.urlopen(url).read()), keep_default_na=False)
finance.columns = finance.columns.str.strip()
cells = finance.groupby(["Segment", "Country"])[["Profit", "Sales"]].sum()
margin = (cells["Profit"] / cells["Sales"]).rename("Margin")
print(f"{len(margin)} cells, margins from {margin.min():.1%} to {margin.max():.1%}")

25 cells, margins from -4.0% to 73.5%

def colour(value, low, high):
    return "red" if value < low else ("amber" if value < high else "green")

by_number = margin.map(lambda m: colour(m, 0.10, 0.20))
low, high = margin.min() + 0.10 * (margin.max() - margin.min()), margin.min() + 0.20 * (margin.max() - margin.min())
by_percent = margin.map(lambda m: colour(m, low, high))
print(f"Percent boundaries fall at {low:.1%} and {high:.1%} instead of 10% and 20%")
compare = pd.DataFrame({"Number rules": by_number.value_counts(), "Percent rules": by_percent.value_counts()}).fillna(0).astype(int)
print(compare.loc[["red", "amber", "green"]].to_string())
print("cells coloured differently:", (by_number != by_percent).sum())

Percent boundaries fall at 3.7% and 11.5% instead of 10% and 20%
        Number rules  Percent rules
Margin                             
red                9              5
amber              1              5
green             15             15
cells coloured differently: 4

table = margin.unstack("Country")
table.columns = [c if c != "United States of America" else "USA" for c in table.columns]
print(table.map("{:.1%}".format).to_string())

                 Canada France Germany Mexico    USA
Segment                                             
Channel Partners  73.1%  73.0%   73.5%  72.9%  73.1%
Enterprise        -3.1%  -2.5%   -2.5%  -3.6%  -4.0%
Government        21.0%  22.3%   23.4%  20.8%  20.3%
Midmarket         26.0%  27.7%   28.3%  29.5%  27.3%
Small Business     9.8%   9.9%   10.5%   9.4%   9.4%

With Percent selected, the boundaries slide to 3.7% and 11.5% because the range runs from -4.0% to 73.5%. Four Small Business cells, all below the 10% threshold, turn amber instead of red. Nothing looks broken; the table is simply wrong. And because the boundaries depend on the minimum and maximum, they can move again whenever filtering changes the range of values.

Gradient or rules?

The same matrix shows why the choice matters. A gradient from the lowest to the highest value would let Channel Partners' margins of around 73% claim the strongest colour, while Government and Midmarket, between 20% and 30%, would sit in the lower half of the scale, and the gap between a 10% and a 20% margin, the one that matters for the target, would be only a modest change in shade. Rules answer the question the reader actually has, which cells are below target, and a gradient answers a different one, where each cell sits relative to the others. Choose the style by the question, not by appearance.

Field value colours from a measure

When the same thresholds apply in several visuals, put them in one measure that returns a colour, and format with Field value. Colours can be names or hex codes:

Margin Colour =
VAR m = [Gross Margin %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( m ), BLANK (),
        m < 0.10, "#C0392B",
        m < 0.20, "#E6A817",
        "#2E8B57"
    )

Changing a threshold then updates every visual at once. The same approach colours text categories, which rules cannot do directly: Microsoft's example maps each status to a colour with SWITCH and formats the status column by that field.

Data bars, icons and links

• Data bars draw a bar in each cell proportional to its value, with or without the number. They suit columns of amounts, such as sales, where relative size matters.
• Icons add shapes such as arrows or traffic lights, by rules or field value, beside or instead of the value.
• Web URL turns a text field into links, using a column of addresses.

Totals, subtotals and blanks

Rules can apply to totals and subtotals as well as values; in a matrix, "values" means the lowest visible level. Decide deliberately whether a total should be coloured: a green total over red rows can mislead. Blank or invalid values also need care. Microsoft notes that NaN values, typically from dividing by zero, prevent gradients with automatic minimum and maximum and percent-based rules from working, and recommends the DIVIDE function, which returns blank instead.

Make formatting readable for everyone

Microsoft's accessibility guidance is clear: avoid using colour, including conditional formatting, as the only way of conveying information (Microsoft Learn: accessible reports). Pair red and green with icons or signs, keep enough contrast between text and background, and check pages for colour vision deficiency.

Practical guidelines

• Format exceptions, not everything. One or two formatted columns per table.
• Keep meanings consistent. Red means the same thing on every page.
• Explain the thresholds in a subtitle or tooltip: "red: margin below 10%".
• Prefer diverging gradients for values around zero, with a middle colour at zero, so losses and profits are distinguishable.

Dashboard design principles covers colour more broadly, KPIs and cards in Power BI covers formatting single numbers, and the finance dashboard project uses this margin matrix in context.

Dashboard design, KPI cards and UI best practices for BI reports are part of the Power BI course.

Sources

• Apply conditional table formatting in Power BI (Microsoft Learn) — Microsoft Learn
• Design Power BI reports for accessibility (Microsoft Learn) — Microsoft Learn
• Download the Financial Sample Excel workbook for Power BI (Microsoft Learn) — Microsoft Learn