Conditional formatting makes the important numbers stand out on their own. Twelve chapters walk through the preset rules, the six “New Rule” types including formula-based formatting, and how to manage rules without breaking a workbook.
What is conditional formatting?
Conditional formatting changes a cell’s colour, font, bar, icon or border automatically when a condition is true. It lives on the Home tab and needs no formulas for the built-in rules, while formula-based rules let you format one cell based on the value of another, highlight whole rows, or flag duplicates and deadlines. It is the fastest way to turn a table into something a manager can read at a glance.
Course curriculum
Preset rules
-
Highlight Cells RulesGreater than, less than, between, equal to, text that contains, dates and duplicates.
-
Top and Bottom RulesTop 10, bottom 10%, above and below average.
-
Data BarsIn-cell bars that show size at a glance, gradient or solid.
-
Color ScalesTwo- and three-colour heat maps.
-
Icon SetsArrows, traffic lights and flags with custom thresholds.
-
Highlight Dates in ExcelHighlight dates in Excel with conditional formatting: overdue, due today, next 7 days, this week and weekends using TODAY and WEEKDAY formula rules you can copy.
-
Conditional Formatting Based on Another Cell, Drop-Down or CheckboxFormat a cell or a whole row based on another cell, a drop-down list or a checkbox in Excel using formula rules such as =$E2=TRUE, explained step by step.
-
Conditional Formatting Rule PrecedenceHow Excel decides which conditional formatting rule wins: rule precedence in the Rules Manager, Move Up and Move Down, and exactly what Stop If True does.
New rules
-
Format all cells based on their valuesCustom scales, bars and icons with number, percent, formula or percentile limits.
-
Format only cells that containValues, text, dates, blanks and errors.
-
Format only top or bottom ranked valuesRank-based highlighting with an exact count or percent.
-
Format only values above or below averageIncluding standard deviation bands.
-
Format only unique or duplicate valuesFind duplicates across one or many columns.
-
Use a formula to determine which cells to formatThe most powerful rule: highlight entire rows, compare columns, flag overdue dates.
Manage rules
-
Manage and Clear RulesRule order, Stop If True, editing ranges and clearing rules safely.
$ locking the column, for example =$D2>TODAY().Dashboards that use these rules
KPI scorecards on NextGenTemplates use icon sets, data bars and formula rules for their traffic lights and trend arrows. A ready-made file is a fast way to see the rules in context.
Frequently asked questions
Why does my rule apply to the wrong cells?
Almost always a reference problem: the rule’s formula is evaluated relative to the first cell of the “Applies to” range. Chapter 11 explains absolute and relative references for rules.
Can I use conditional formatting on a pivot table?
Yes, and the rule can follow the pivot when it grows. See chapter 6 with the pivot table module.
Does too much conditional formatting slow Excel down?
Hundreds of overlapping rules can. Chapter 12 shows how to consolidate rules and use one rule for a whole table.