Conditional Table Formatting in Power BI - Making Numbers Tell You Where to Look

A table of numbers is the least helpful way to present data, right up until you add the right formatting, at which point it can become the most helpful. That sounds like a contradiction. It is not. The difference is whether the reader has to hunt for the number that matters, or whether the report points at it for them.

Conditional table formatting in Power BI is the feature that does the pointing. You set rules so that cells change colour, show a data bar, or display an icon based on their value. A margin below 15 per cent goes red. A branch that beat target goes green. A stock level under the reorder point gets a warning icon. The reader's eye lands on the exceptions without reading a single figure, which is exactly what you want in a report that busy people glance at for ten seconds between meetings.

I have built a lot of Power BI for Australian businesses, and conditional formatting is one of those features that is genuinely useful and also very easy to overdo. So I want to walk through how it actually works, where it earns its keep, and the mistakes I see again and again. Microsoft's documentation on conditional table formatting is the reference for the mechanics. This is the version shaped by cleaning up other people's dashboards.

What you can actually do

Power BI gives you a handful of conditional formatting types, and each suits a different job.

Background colour and font colour. The two most common. You colour the cell fill or the text based on the value. A colour scale (also called a gradient) shades cells across a range, so low values might be pale and high values deep. Rules-based colouring lets you set hard thresholds instead, so anything under 100 is red and anything over 500 is green, with bands in between.

Data bars. These draw a small horizontal bar inside the cell, proportional to the value. It turns a column of numbers into a tiny bar chart you can read at a glance. Good for comparing magnitudes down a column, like sales by region or headcount by department.

Icons. Little symbols, usually traffic lights or arrows, driven by thresholds. Green tick, amber dash, red cross. Useful for status columns where the reader only cares about pass or fail, not the exact figure.

Web URLs. Less commonly used, but you can format a column so its text becomes a clickable link. Handy when a table row maps to a record in another system, like a customer ID that links straight to the CRM.

You apply all of these through the format pane, on the specific field in the values well of your table or matrix. The important bit that trips people up is that conditional formatting is set per field, and you can drive the colour off a different field than the one being displayed. That last part is where the real power lives, and I will come back to it.

The setting most people miss

Here is the thing that separates a report that looks tidy from one that is actually smart. You do not have to colour a cell based on its own value. You can colour it based on another measure entirely.

Say you have a table showing this month's revenue by product. The obvious move is to colour revenue red or green based on the revenue figure. But a product doing 50,000 dollars might be brilliant or terrible depending on its target. So instead, you write a measure that calculates variance to target, and you use that measure to drive the colour on the revenue column. Now the revenue number is coloured by how it performed against plan, not by its raw size. The reader sees the dollar figure and the judgement about that figure in one cell.

You do this with "Format by field value" or by writing a dedicated colour measure that returns a hex code. The colour measure approach is the one I reach for on anything serious, because it puts all your formatting logic in DAX where you can see it, test it, and change it in one place. A measure that returns "#C0392B" when a condition is met and "#27AE60" otherwise gives you complete control. It is more work up front and far easier to maintain later.

This is the kind of modelling decision that makes the difference between a dashboard people trust and one they quietly stop opening, and it is a big part of what we do in Power BI consulting work. The formatting is the visible five per cent. The measures underneath are the ninety-five per cent that makes it correct.

Where it works brilliantly

Exception reporting is the killer use case. A retailer with 200 stores does not want to read 200 rows. They want the eight stores that are below target to jump off the page. Conditional formatting turns a wall of numbers into a heat map of where attention is needed, and it does it without a chart, which means it prints, exports to PDF, and drops into an email without breaking.

Operational dashboards love icons. A logistics client of ours runs a daily table of routes with a simple three-state icon, on time, at risk, late. The dispatchers do not read the minutes, they read the colours, and they only stop on red. That table replaced a chart that nobody could act on quickly.

Data bars are great for ranked comparisons where the exact number is secondary. A column of data bars showing spend by supplier tells you who the big suppliers are in half a second, and the numbers are right there if you want the detail.

Where it goes wrong

Now the honest part. Conditional formatting is easy to abuse, and an over-formatted table is worse than a plain one.

Too many colours. I have opened reports where every column had a different colour scale and the whole thing looked like a fruit salad. When everything is highlighted, nothing is. Pick the one or two columns that actually carry the message and format those. Leave the rest plain. Contrast is the entire point, and you destroy contrast by using it everywhere.

Colour scales that mislead. Gradient colour scales are seductive because they look sophisticated, but they encode magnitude in a way that is hard to read precisely. Is that cell a slightly darker green than the one above it? Who can tell. If the exact threshold matters, use rules with hard bands, not a smooth gradient. Gradients are for a general sense of high and low, not for decisions.

Accessibility. Red-green colouring is a problem for the portion of your audience with colour vision deficiency, and it is a bigger portion than people assume. If your only signal is red versus green, some readers get no signal at all. Pair colour with an icon or a shape, or choose a palette that works for colour-blind viewers. This is not box-ticking, it is whether your report communicates to everyone who opens it.

Colour without meaning. If red does not consistently mean bad across your report, you have trained the reader to distrust the colours. Pick a convention and hold it everywhere. Red is a problem, green is fine, amber is watch this. The moment red means "high" in one table and "bad" in another, the whole visual language falls apart.

A few practical tips from real builds

Set your thresholds off measures, not hard-coded numbers buried in the format pane. If the reorder point changes, you want to change it in one measure, not hunt through formatting rules on six tables.

Test what happens with blanks and nulls. A colour scale can do strange things when a cell is empty, sometimes shading a blank cell as if it were a zero, which reads as a real low value when it is actually missing data. Decide deliberately what a blank should look like.

Watch the interaction with totals. A matrix subtotal or grand total row will pick up conditional formatting too, and a coloured total is often meaningless or actively misleading because it is aggregating things the colour rule was not designed for. Turn formatting off on totals when it does not make sense there.

And check it in the actual delivery format. A heat map that looks crisp on your 27-inch monitor can turn muddy on a projector in a board room or washed out in an exported PDF. Look at it where your audience will look at it.

My rule of thumb

Conditional formatting should answer one question the reader has, and answer it fast. Before you add it, ask what the reader is looking for in this table. If the answer is "which rows need attention," format the column that signals that and leave everything else alone. If you cannot name the question, you do not need the formatting.

Used with that discipline it is one of the highest-value, lowest-effort things you can do to a report. Used without it, you get a dashboard that looks busy and says nothing.

Most of the reports we build sit inside a broader business intelligence practice, and formatting is always the last step, after the model and the measures are right. If you have inherited a set of Power BI reports that are hard to read or hard to trust, or you want reporting built properly from the data layer up, take a look at our services or get in touch.