Conditional Formatting & Data Visualization Best Practices

Explain how conditional formatting rules and visualization choices communicate insight effectively rather than just displaying numbers.

Formatting is where a report stops being correct and starts being understood.

Conditional formatting is like a doctor circling the one abnormal number on a lab report in red instead of making you scan fifty normal values yourself to find it — the format itself does the diagnostic work before you even read the number.

Key Concepts

Conditional formatting applies a rule to a column.

    if Revenue < Target      -> red background
    if Days Overdue > 30     -> red text, bold
    if Status = 'Blocked'    -> a warning icon

Rules can test a different column than the one they format, which is how you highlight a whole row based on one value.

Use it to draw the eye, not to decorate. A table where every column is conditionally coloured tells the reader nothing, because nothing stands out. The value of the red cell is that it is the only red cell.

Choosing the chart. The requirement usually announces the answer:

    comparison across categories    bar
    change over time                line
    part of a whole                 stacked bar, and only a few parts
    relationship between measures   scatter
    one number against a target     gauge or a large tile

Pie charts, specifically. People compare angles badly. Three slices is readable; nine is decoration. A bar chart answers the same question and can be read.

Truncated axes. A bar chart whose axis starts at 95 rather than zero makes a 2% difference look enormous. That is a misleading chart even when every number on it is correct, and it is the most common way a report misinforms.

Table or chart. A chart shows a shape. A table gives exact values. If the reader needs to know that revenue was 1,284,302, a chart makes them hover over a bar.

The usual answer is both, on one analysis, sharing one query.

Sort by the measure, not alphabetically. A bar chart of twenty regions in alphabetical order requires reading every bar to find the largest. Sorted by value, it takes no effort. This is the smallest change with the largest effect on comprehension.

And the discipline behind all of it. Before formatting anything, state what the reader is meant to conclude. The colours, the chart type and the sort all follow from that — and if there is no answer, the report does not yet have a purpose.

What the interviewer is probing.1. "What is conditional formatting for?" Probing: the purpose. Stalls: "Colour." Moves up: drawing the eye to exceptions — values outside a threshold — so the reader does not have to scan every number.

2. "What chart would you use for a part-to-whole comparison?" Probing: whether you know chart choice. Stalls: "A pie chart." Moves up: usually a stacked bar — pie charts are hard to compare across slices and worse across several pies.

3. "What is the accessibility consideration?" Probing: the thoroughness. Stalls: "None." Moves up: colour alone is not enough — a red cell needs an icon or value cue for colour-blind readers.

4. "What is the most common design mistake?" Probing: experience. Stalls: "Bad colours." Moves up: too much on one dashboard — every chart competing means none is read, where three well-chosen ones get used daily.