Webbo3 Data Analysis Bootcamp · Data Visualisation Module · Lesson 16
Excel Conditional Formatting: Highlight Rules, Top and Bottom Rules, Data Bars, Colour Scales, Icon Sets, Custom Formulas, and Managing Rules
A hands on lesson on making your data flag its own problems, highlight its own patterns, and tell its own story, without you manually scanning a single cell.
In the last lesson you built charts to communicate data visually. Conditional formatting does something different: instead of creating a separate chart, it turns the spreadsheet itself into a visual, making individual cells change colour, grow a bar, or display an icon based entirely on what is inside them. A deadline column that turns red when overdue. A sales column where every cell grows a blue bar showing its size relative to the others. A performance table where a green tick, yellow dash, or red cross appears automatically against every score. All of this happens with zero manual colouring, zero formula changes, and zero rework when the numbers update, because the rules you write in this lesson apply permanently and recalculate every single time the underlying data changes.
1. Highlight Cells Rules: Greater Than, Less Than, Equal To, Between
Select your range, go to Home tab → Styles group → Conditional Formatting → Highlight Cells Rules, and you will see a submenu listing the six most commonly used comparison rules. Each one opens a small dialog with two boxes: a value to compare against on the left, and a formatting style dropdown on the right.
Greater Than and Less Than flag every cell above or below a threshold you specify. Type a fixed number directly into the left box, for example 50000, or click the small icon beside that box and click a cell on your sheet instead to reference a live value. Using a cell reference rather than a hardcoded number means changing that reference cell instantly updates every highlighted cell across the entire range, with no need to open the rule and edit it manually.
Equal To flags every cell matching exactly what you type, and it works on text as well as numbers. Highlighting every cell in a status column that reads "Overdue" in red, for example, uses this rule with the text "Overdue" typed into the left box.
Between flags every cell whose value falls within a range you define with a lower and an upper bound, both inclusive. If your acceptable delivery window is 5 to 10 days and you want to visually mark everything falling inside that range green, Between is exactly the right rule. Note that this is one rule, not two separate Greater Than rules stacked together, and it evaluates both bounds at once rather than requiring you to set up two separate conditions manually.
The formatting style dropdown deserves its own attention. The built in colour presets, Light Red Fill with Dark Red Text, Yellow Fill with Dark Yellow Text, and so on, are fast defaults but not your only options. Click "Custom Format" at the bottom of that dropdown to open the full Format Cells dialog, where you can build any combination of fill colour, font colour, border, and font style you like, giving you complete control over how a flagged cell looks rather than being limited to the preset options.
2. Top and Bottom Rules: Top 10, Bottom 10, Above Average
Go to Conditional Formatting → Top/Bottom Rules to find rules that evaluate each cell relative to the entire range rather than against a fixed number. These rules are genuinely dynamic: add a new row of data to your range, press Enter, and the rule immediately re-evaluates the entire set to determine whether any new value now belongs in the top or bottom group.
Top 10 Items and Bottom 10 Items are misleadingly named. They do not literally require exactly ten items. When you open the dialog, the "10" sitting in the left box is just a default starting point. Change it to 5 and only the top or bottom 5 values get highlighted. Change it to 3 for a cleaner leaderboard or bottom three flag. The rule always highlights that many individual cells by their ranked value, counting from the top or from the bottom.
Top 10 Percent and Bottom 10 Percent work differently from the count-based versions. Rather than finding the top 5 or 10 cells by rank, they flag every cell whose value places it within the top or bottom percentage of the full range. A range of 200 values with a Top 10 Percent rule applied highlights the 20 cells sitting in the highest tenth of the distribution, not just the ten highest individual values.
Above Average and Below Average calculate the mean of your entire range and flag every cell above or below it automatically. This single rule is extraordinarily useful in a performance tracker or a sales table because it visually partitions the dataset at its natural midpoint, showing at a glance who is pulling above the group and who is falling behind, and it updates immediately whenever any value in the range changes, since the average itself recalculates.
3. Data Bars: A Visual Bar Inside Each Cell
Go to Conditional Formatting → Data Bars and choose either a gradient fill style, where the bar colour fades from solid to transparent as it stretches across the cell, or a solid fill style, where the entire bar is one flat colour. Either way, the result is a miniature horizontal bar printed directly inside each cell, its length scaled proportionally to the cell's value relative to the highest value in the range.
What data bars communicate that plain numbers cannot. In a column of a hundred sales figures, reading every number individually to identify the largest and smallest takes time. A data bar column lets the eye do that work instantly, since the longest bar is the highest value and the shortest is the lowest, with every bar between scaled accordingly. This is particularly useful in tables where the reader needs to compare rows quickly, since the bar length draws the eye to the right cell far faster than font size or colour alone.
Customising data bars through Manage Rules. The defaults are a good starting point but the real control sits in Manage Rules, then Edit Rule. Inside that dialog you can change the Minimum and Maximum type from Automatic to a fixed Number, a Percentage, or a Percentile, which matters when the default automatic scaling produces misleading bars. If your range has one outlier value much larger than everything else, the automatic maximum scales every other bar relative to that outlier, making all the mid-range values appear tiny. Switching Maximum to a manually set value, for example 100 for a percentage range, fixes the scale to something meaningful rather than outlier-driven.
The Show Bar Only option, genuinely useful for dashboard work. Inside the same Edit Rule dialog, ticking the Show Bar Only checkbox hides the number text inside the cell completely, showing only the bar itself. This produces a cleaner, more visual dashboard column where the length tells the story and the precise number is readable elsewhere, for example in an adjacent totals column, without both competing for space inside the same narrow cell.
4. Colour Scales: A Gradient Across a Range
Go to Conditional Formatting → Color Scales and choose a two-colour or three-colour preset. A colour scale shades every cell in the range with a fill colour drawn from a gradient, mapping each cell's value onto a continuous spectrum from lowest to highest. The cell with the maximum value gets the darkest or most saturated version of one colour, the cell with the minimum gets the darkest or most saturated version of the other colour, and everything in between is shaded at the corresponding point along that gradient.
Two-colour scales versus three-colour scales. A two-colour scale runs a gradient between just two end points, for example white at the low end to green at the high end, or red at the low end to green at the high end. A three-colour scale adds a midpoint colour as well, for example a Red-Yellow-Green scale where low values shade red, mid-range values shade yellow, and high values shade green. The three-colour version is more visually distinctive for large tables where you want readers to immediately spot the extremes and the middle ground without reading numbers, since three zones are easier to perceive at a glance than one smooth gradient.
A genuine accessibility consideration worth building into your habit from this lesson onward. The most popular colour scale preset is Red to Green, which is also the worst possible choice for anyone with red-green colour blindness, a condition affecting roughly 8 percent of men and 0.5 percent of women. Before applying any colour scale to a shared workbook or a report that others will read, ask whether a Red-Blue or Yellow-Blue alternative serves the same visual purpose. Both are fully distinguishable for the most common forms of colour blindness, while red-green is often completely invisible. This is not a stylistic preference, it is a real readability decision that affects real readers.
5. Icon Sets: Arrows, Traffic Lights, and Ratings
Go to Conditional Formatting → Icon Sets and choose from four families of icons. Directional sets use arrows pointing up, sideways, or down, for example the 3 Arrows set. Shapes sets use circles or diamonds filled, half-filled, or empty. Indicators sets use tick marks, crosses, and exclamation marks. Ratings sets use filled bar segments, like a signal strength indicator. Each icon set applies a different visual symbol to each cell based on which value tier that cell falls into.
How the tier thresholds actually work. A three-icon set divides your range into three tiers. By default, the top tier covers values at or above the 67th percentile, the middle tier covers values between the 33rd and 67th percentile, and the bottom tier covers everything below the 33rd percentile. A five-icon set divides into five tiers at roughly equal percentile intervals. These defaults produce a sensible starting split for most datasets, but the real power comes from editing those thresholds manually through Manage Rules and Edit Rule, where you can change the type from Percent to Number and set your own exact boundaries, for example showing an up arrow only for values above 80, a sideways arrow for values between 50 and 80, and a down arrow for anything below 50, rather than the default percentile split.
A genuine advantage icon sets have over colour-only rules. Icons communicate their message through shape and direction as well as colour, which means they remain interpretable even to readers who cannot perceive colour differences. A green up arrow is still clearly an up arrow even in a greyscale printout, and a red down arrow is still clearly a down arrow even for a colour-blind reader. For any table destined for printed reports or presentations where colour rendering is uncertain, an icon set is a more reliable visual signal than a colour-only rule built on the same data.
6. Custom Conditional Formatting With Formulas
Every rule covered so far applies formatting based on the value inside the cell being formatted. A custom formula rule is fundamentally different: it lets you apply formatting to one column or row based entirely on a condition evaluated somewhere else in your data. This is the rule type that lets you highlight an entire row red when a status column in that row reads "Overdue," rather than just colouring the status cell itself.
Select the full range you want to potentially format, for example A2:F50 for an entire table body, then go to Conditional Formatting → New Rule → Use a formula to determine which cells to format. Type your formula in the box, choose your formatting, and click OK. The formula must evaluate to TRUE for a cell to receive the formatting and FALSE to leave it untouched, exactly the same logic as the IF statement from an earlier lesson.
The single most important thing to get right: the dollar sign pattern in the formula reference. If your formula is =$E2="Overdue" applied to the range A2:F50, the $E locks the column to E, so the condition always checks the status column regardless of which column in the row is currently being formatted. The 2 has no dollar sign, so it shifts freely with each row, checking E2 for the row starting at row 2, E3 for row 3, and so on. If you accidentally type $E$2 with both locked, every single row in your table checks only row 2 forever, meaning all rows highlight or none do, and no row in between behaves differently. Getting this reference pattern right is the exact same skill from the cell referencing lesson applied in a different context, and it is the single most common source of a custom conditional formatting rule that seems to do nothing or formats every cell identically.
A real example: highlighting an entire row when a task is overdue. Status labels sit in column E, starting at E2. Deadlines sit in column D. You want the entire row highlighted red when the status is "Overdue." Select A2:F50, open New Rule, Use a formula, and type =$E2="Overdue". Choose a red fill. Every row where column E reads exactly "Overdue" gets a full red fill across all six columns of that row, while every other row stays unformatted.
Another real example: comparing a value against a target in another column. If column B holds actual sales and column C holds each salesperson's individual target, =B2
7. Managing and Clearing Conditional Formatting Rules
Go to Conditional Formatting → Manage Rules to open the Rules Manager, which is the single place where every conditional formatting rule on a sheet can be viewed, edited, reordered, or deleted. By default it shows only the rules applied to your current selection. Click the dropdown at the top of the dialog that reads "Show formatting rules for" and switch it from "Current Selection" to "This Worksheet" to see every rule applied anywhere on that sheet in one complete list, regardless of what you currently have selected.
Rule order controls which rule wins when two rules conflict. Rules are evaluated from the top of the list downward, with the topmost rule having the highest priority. If a cell qualifies for two rules that both try to set the fill colour, only the topmost rule's colour is applied, since a cell cannot show two fill colours simultaneously. Use the up and down arrow buttons inside the Rules Manager to reorder rules so the most important one always sits at the top. A genuinely practical example: a grading table where scores above 90 should turn green and scores above 70 should turn yellow. If yellow sits higher in the list, a score of 95 gets yellow, never green, because Excel applies the topmost matching rule and stops. Move green above yellow, and a score of 95 correctly turns green, since green's condition is evaluated first.
Stop If True, and exactly when to use it. Each rule row in the Rules Manager has a Stop If True checkbox on its right side. When ticked, it tells Excel that if this particular rule applies to a cell, Excel should stop evaluating any further rules lower in the list for that cell. This is useful in two specific situations: when you want to prevent a lower-priority rule from partially overriding a higher-priority one that already fired, for example stopping a Yellow Warning rule from also changing the font colour of a cell that already turned Red Critical, and when you want to avoid unnecessary rule evaluations on cells where the answer is already clear from a higher-priority rule, which modestly improves performance on a sheet with many rules applied to a very large range. Stop If True has no effect on Data Bars, Colour Scales, or Icon Sets, since those rule types ignore the checkbox entirely.
Editing the Applies To range without recreating the rule. Inside the Rules Manager, each rule lists its current applied range in the Applies To column. You can click directly into that cell and type a new range, extend it, or correct it, without opening the Edit Rule dialog at all. This is the fastest fix when a rule was initially created on a small sample and later needs to cover a full table, or when pasting or extending data has caused the range to drift from what was originally intended.
Clearing rules. Go to Conditional Formatting → Clear Rules for a quick submenu offering two options: Clear Rules from Selected Cells removes only the rules attached to whatever you currently have selected, leaving rules elsewhere on the sheet completely untouched. Clear Rules from Entire Sheet wipes every single conditional formatting rule across the whole worksheet in one action, with no undo prompt, so treat it accordingly. For anything more targeted, deleting one specific rule rather than all of them is always better handled through the Rules Manager, where you can select one rule, click Delete Rule, and confirm only that one is removed.
A genuine issue that grows over time in shared workbooks: rule proliferation. Copying and pasting cells that carry conditional formatting creates silent duplicates of those rules in the Rules Manager, each pointing to progressively smaller sub-ranges. Over months of normal spreadsheet work, a sheet can accumulate dozens of nearly identical rules all doing the same job across fragmented ranges. This quietly slows the workbook down on every recalculation and makes the Rules Manager genuinely hard to read. A good habit is to open the Rules Manager periodically on any actively used shared sheet, look for duplicate or redundant rules, and consolidate them back into a single rule with a clean, consolidated range before the list becomes unmanageable.
Quick recap: Highlight Cells Rules compare against a fixed value or a referenced cell, using Custom Format for full control over what "flagged" looks like · Top and Bottom Rules evaluate relative to the whole range and update automatically when data changes · Data Bars print a length-proportional bar inside each cell, customise the minimum and maximum through Edit Rule, and Show Bar Only produces a cleaner dashboard look · Colour Scales shade cells along a gradient, two-colour for simple high-low, three-colour when a midpoint also matters, always check accessibility before using Red-Green · Icon Sets communicate through shape as well as colour and remain readable in greyscale or for colour-blind readers · custom formula rules use a logical formula returning TRUE or FALSE, always reference only the first cell of the selected range with the correct dollar sign pattern, since a locked column and a free row is the key to row-based highlighting · the Rules Manager controls priority with the topmost rule winning, Stop If True prevents lower rules from firing once a higher one applies, and rule proliferation from copying and pasting is worth cleaning up periodically.
Using AI to Move Faster in Excel
Conditional formatting is one of the clearest places in this entire bootcamp where AI inside Excel earns its place, because the most genuinely difficult part of this lesson is not clicking the menus, it is translating a business rule, stated in plain English, into the correct formula reference pattern without getting the dollar signs wrong.
1. Describe the highlighting rule in plain language, let Copilot write the formula.
Instead of working out the dollar sign pattern yourself for a custom formula rule, describe exactly what you want highlighted, for example "Highlight every row where the delivery date in column D has passed and the status in column E is not yet Delivered." Copilot should generate a formula combining AND and TODAY, already referencing the correct columns with the correct locking pattern, which you can then paste directly into the New Rule formula box and verify against a couple of rows by hand before applying it to the full range.
2. Ask Copilot to diagnose a rule that is formatting everything or nothing.
If a custom formula rule applies formatting to every cell in the range when only some should be highlighted, or to none at all when clearly some should be, the cause is almost always the dollar sign pattern described in this lesson. Select a misbehaving cell, ask Copilot to inspect the formula, and ask specifically whether the column reference should be locked, the row reference should be free, and whether the formula references the correct first cell in the selected range rather than drifting. That one targeted question covers the cause of the overwhelming majority of custom rule failures.
3. Use AI to suggest which rule type fits your actual data before you build anything.
If you are unsure whether your scenario calls for a Highlight Cells Rule, a custom formula rule, or an icon set, describe the visual outcome you want, for example "I want to show whether each salesperson is above or below their own individual target, not a single shared target." That description immediately points to a custom formula comparing two columns row by row, something the built in rules cannot do, and a good AI response should explain why and generate the exact formula structure the task requires.
4. Always test a conditional formatting rule on a handful of known values before applying it to a full sheet.
This is the same verification habit from earlier lessons, applied here with one additional step: deliberately include a cell that should trigger the rule, a cell that should not, and a cell sitting right on a boundary value, for example a score of exactly 70 when one rule covers above 70 and another covers at or below 70. Whether you wrote the formula yourself or Copilot generated it, these three test cases take thirty seconds and cover the majority of rule logic mistakes before they silently format the wrong cells across an entire workbook.
A habit worth building from this lesson onward: whenever you finish setting up conditional formatting on a shared workbook, open the Rules Manager and read through every rule listed for the whole sheet before handing it over. If you cannot immediately state in one sentence what each rule does and which cells it covers, that rule is either redundant, pointing to a broken range, or a silently copied duplicate of another rule already there. Five minutes of Rules Manager review before sharing a workbook is consistently worth the effort, and it is also the standard professional practice in any team that uses conditional formatting seriously rather than sporadically.
Next lesson: sorting, filtering, and working efficiently with larger datasets using AutoFilter and Advanced Filter.