Excel & Spreadsheets · IACT Training Blog
Conditional formatting is the fastest way to turn a wall of numbers into something a reader understands at a glance. It automatically applies colours, icons and data bars to cells based on their values, so trends, outliers and exceptions surface without anyone having to squint at the grid. At IACT we teach it on every Excel course because it delivers the biggest visual payoff for the least effort.
Start with the built-in rules
The Home tab’s Conditional Formatting button gives you five families of ready-made rules. Highlight Cells Rules flag values greater than, less than, between or equal to a threshold, plus text that contains a string and dates occurring this week or last month. Top/Bottom Rules shade the top 10 items, the bottom 10%, or anything above or below average. These cover most everyday needs and apply in two clicks.
Data Bars draw a horizontal bar inside each cell proportional to its value, turning a column of figures into an instant in-cell bar chart. Colour Scales blend two or three colours across a range so a heat map emerges, ideal for spotting hot and cold zones in a sales matrix. Icon Sets add traffic lights, arrows or flags to grade performance against bands you control.
Take control with formula rules
The real power arrives when you choose ‘New Rule’ then ‘Use a formula to determine which cells to format’. Here you write a logical test that returns TRUE or FALSE for each cell. To highlight an entire row when a project is overdue, select the data range and enter a formula like =$F2<TODAY(), locking the column with a dollar sign so the test always reads the date column while the row reference moves.
Formula rules let you compare a cell to another cell, flag duplicates across columns, shade weekends in a roster, or highlight the row containing the value a user picked from a drop-down. Because the formula evaluates per cell, a single rule can format thousands of rows consistently and update the moment the data changes.
Build dashboard-ready visuals
Combine techniques for professional results. Use a colour scale on a KPI grid, data bars on a ranked list, and an icon set tied to percentage bands for a status column. Keep the palette restrained: two or three colours read far better than a rainbow.
- Use ‘Stop If True’ to layer rules in priority order without conflicts.
- Point a rule at a helper cell so a slicer or drop-down drives the highlighting.
- Apply formatting to an Excel Table so new rows inherit the rules automatically.
- Manage everything from Conditional Formatting > Manage Rules to audit what is applied where.
Common pitfalls to avoid
Over-formatting is the number one mistake. If everything is highlighted, nothing stands out. Reserve strong colour for genuine exceptions. Watch your absolute and relative references in formula rules, the most frequent cause of a rule that only formats the first row. Finally, copy formatting with Paste Special > Formats rather than dragging, which can fragment your rule ranges into dozens of duplicates.
Train with IACT
IACT runs Excel Beginner to Advanced courses in Dublin and live online, covering conditional formatting, dynamic arrays, Power Query and dashboards. Build reports your colleagues actually read.