Skip to content
Controllingblog

Home❧Software & Data❧Conditional formatting: the 30 most important rules for controllers

Conditional formatting: the 30 most important rules for controllers

Used well, the one critical variance jumps out immediately. Used badly, everything lights up. These 30 rules show how to do it right.

Software & Data12 min readUpdated 14 Nov 2026
Illustration: laptop with dashboard, database and cloud +12% 4.2 M 98%

You find conditional formatting under Home → Conditional Formatting. It colours cells based on their value or a formula – and adapts automatically when the numbers change. For controllers that means variances, due dates and outliers mark themselves. The formulas in this article use the English Excel version with commas as separators.

Back to the overview: Excel for controllers: the 30 most important functions

Highlight cells rules

1. Greater than / less than

Marks all values above or below a limit, e.g. costs above 10,000. Instead of a fixed number you can refer to a cell – then you change the limit in one place.

2. Between

Marks a range, e.g. variances between −5% and +5% that count as “on plan”.

3. Equal to

Highlights a specific value, for example all “open” entries in a status column.

4. Text that contains

Marks cells containing a text – e.g. all accounts with “travel” in their name.

5. A date occurring

Yesterday, today, this week, last month: due dates and deadlines change colour based on today's date.

6. Duplicate or unique values

Finds duplicate invoice or document numbers in seconds – one of the most useful checks of all.

Top/bottom rules

7. Top 10 items

The ten largest values in a list; the number is up to you. Good for the largest customers or cost blocks.

8. Top 10%

Marks the top share of a list – e.g. the top 20% of customers for an ABC analysis.

9. Bottom 10 items or %

The opposite: the smallest contribution margins, the weakest branches.

10. Above or below average

Colours everything above or below the list's average. Quick, but use with care when there are outliers.

Graphical formats

11. Data bars

A bar inside the cell shows the size of the value. Turns a column of numbers into a mini chart without using extra space.

12. Data bars with negative values

Under Manage Rules → Edit Rule → Negative Value and Axis, negative variances grow red to the left and positive ones to the right – ideal for budget vs actual columns.

13. Colour scales

Two- or three-colour gradients, e.g. red–yellow–green. Good for heatmaps, such as revenue by branch and month.

14. Icon sets

Traffic lights, arrows or circles depending on the value – the classic traffic-light system in a report.

15. Icon sets with your own thresholds

By default Excel splits into percent thirds. Under Edit Rule, set the type to Number and enter real thresholds, e.g. green from 0%, yellow from −5%, red below.

16. Show icon only

Hides the number and shows only the traffic light. Good for a separate status column next to the variance.

Formula-based rules

17. Actual below plan

Compares two cells in the same row: red when actual (column D) is below plan (column C).

=$D2<$C2

18. Highlight the whole row

New Rule → Use a formula to determine which cells to format. With the column fixed ($E), the whole row turns red when the variance is negative.

=$E2<0

19. Variance of more than 5% to plan

Marks items whose actual deviates from plan by more than 5% – in either direction.

=ABS($D2/$C2-1)>5%

20. Every other row

Banded rows that stay correct after sorting.

=MOD(ROW(),2)=0

21. Weekends in a calendar

Colours Saturdays and Sundays in a planning or holiday overview.

=WEEKDAY(B$1,2)>5

22. Highlight today's date

Marks the column with today's date, e.g. in a cash flow forecast.

=B$1=TODAY()

23. Missing entries

Colours empty mandatory fields until someone fills them in.

=ISBLANK($C2)

24. Value missing from master data

Marks cost centres or accounts that do not exist in the master data list.

=COUNTIF(Master[No],$A2)=0

25. Overdue open items

Red when an invoice is open and past its due date – the basis for accounts receivable management.

=AND($F2="open",$E2

Managing rules

26. Manage rules

Conditional Formatting → Manage Rules shows all rules on a sheet with their order and range (“Applies to”). This is where you tidy up when rules pile up.

27. Stop if true

When a rule applies, the rules below it are no longer checked. This avoids double colouring.

28. Clear rules

Clear Rules → from Selected Cells or from Entire Sheet. Helps with inherited files full of old rules.

29. Format Painter

The brush (Home → Format Painter) copies number formats and rules to other ranges. Watch out: this quickly creates duplicate rules – check Manage Rules afterwards.

30. In pivot tables

For pivot tables choose “All cells showing … values”. The formatting then survives a refresh.

Practical tipLess is more: use at most one colour for “bad” and one for “good” in a report. For simple sign colouring a custom number format is often enough instead of a rule:
[Green]+0.0%;[Red]-0.0%;0.0%

Conditional formatting is a tool for flagging, not for analysis. Where a variance comes from is answered by budget vs actual analysis and drilling down in the pivot table.

Frequently asked questions

Why does my formula rule colour the wrong cells?

Usually the reference is off. The formula always refers to the first cell of the selected range. Fix the column you colour by with $ (e.g. $E2), but not the row.

How many rules can a file handle?

Technically a lot, but each rule costs calculation time. If a file gets sluggish, delete duplicate and old rules under Manage Rules.

Can I use traffic lights with fixed limits instead of percent?

Yes. Switch the type in the rule from Percent to Number and enter the thresholds yourself.