Conditional Formatting in Excel: A Practical Guide

-

 

Conditional Formatting in Excel: A Practical Guide

Conditional Formatting automatically changes a cell's colour, font, or icon based on rules you set, without you needing to manually format anything yourself. Excel comes with built-in presets for common cases like highlighting duplicates or colour-scaling a range of numbers, but the real power is in the formula-based option, which lets you build a rule around almost any condition you can write as a formula.

For longer than I'd like to admit, I built monthly sales heatmaps by eye — manually shading cells red, yellow, or green based on how each region's numbers compared to target, one cell at a time, every single month. It looked fine in the final report, but it took ages and I had to redo the whole thing from scratch whenever the numbers changed even slightly. A colleague eventually watched me doing this and asked, genuinely confused, why I wasn't just using Conditional Formatting with a formula rule. I didn't really have a good answer beyond "I've just always done it this way," which is a phrase I've come to be a bit suspicious of whenever I catch myself saying it.

How do I apply basic Conditional Formatting?

  1. Select the range you want to format
  2. Go to Home > Conditional Formatting
  3. Choose one of the built-in rule types — Highlight Cells Rules (like "Greater Than" or "Duplicate Values"), Top/Bottom Rules, Data Bars, Colour Scales, or Icon Sets
  4. Set the condition and choose a formatting style
  5. Click OK

For a quick example, highlighting every cell over $1,000: Home > Conditional Formatting > Highlight Cells Rules > Greater Than, then type 1000 and pick a colour.



What's the difference between the built-in rules and a formula-based rule?

The built-in presets cover common, simple scenarios well — highlighting values above or below a threshold, colour-scaling a range, or flagging exact duplicates. A formula-based rule lets you define any condition yourself, including ones that reference other cells, other columns, or combine multiple conditions together, which the built-in presets simply can't do.

To use a formula-based rule:

  1. Select your range
  2. Home > Conditional Formatting > New Rule
  3. Choose "Use a formula to determine which cells to format"
  4. Type a formula that evaluates to TRUE or FALSE for the top-left cell of your selection
  5. Click Format, choose your styling, and click OK

How do I highlight an entire row based on one column's value?

This is one of the most useful formula-based tricks, and it's not obvious from the built-in presets at all. Say you want to highlight the entire row whenever the "Status" column (column E) says "Overdue."

  1. Select the full data range, starting from column A
  2. New Rule > Use a formula
  3. Enter: =$E2="Overdue"
  4. Format with your chosen highlight colour
  5. Apply

The key detail people miss here is the dollar sign placement. Locking the column with $E2 (not $E$2) means every cell in the row checks column E specifically, while still allowing the row number to adjust as the rule applies down the whole selection. Getting this wrong is the single most common reason formula-based Conditional Formatting doesn't behave as expected.



Can I use COUNTIF inside a Conditional Formatting rule?

Yes, and this is a genuinely handy combination. If you've read my post on "SUMIF, COUNTIF and SUMIFS", you'll remember COUNTIF as a way to spot duplicates using a formula. You can turn that same logic into a visible highlight instead of a helper column full of numbers.

Highlighting duplicate client names directly, without a separate count column:

=COUNTIF($A$2:$A$200, A2)>1

This checks whether the current value appears more than once anywhere in the full range, and highlights it if so — no helper column needed at all, since Conditional Formatting handles the counting invisibly behind the scenes.

Why isn't my Conditional Formatting rule applying to the cells I expect?

A handful of common causes:

  1. Wrong range selected before creating the rule. The rule only applies to whatever was selected at the time you created it — check Home > Conditional Formatting > Manage Rules to see and adjust the "Applies to" range.
  2. Absolute vs relative reference mistakes, as covered above with the row-highlighting example. Getting $ placement wrong is the most common formula-based issue.
  3. Multiple overlapping rules, where one rule is silently overriding another. Manage Rules shows the order rules are evaluated in, and you can reorder or tick "Stop If True" to control this.
  4. The formula references the wrong starting cell. Your formula should always reference the top-left cell of your selected range, not a random cell within it.

FAQ

Can I have more than one Conditional Formatting rule on the same range? Yes, and Excel evaluates them in the order shown in Manage Rules, applying each one that's true unless "Stop If True" is ticked on an earlier rule.

Does Conditional Formatting slow down large spreadsheets? It can, particularly with formula-based rules applied across very large ranges, since Excel needs to re-evaluate the formula for every cell whenever the sheet recalculates. For everyday spreadsheet sizes, it's rarely noticeable.

Can I copy Conditional Formatting to another range like regular formatting? Yes — use the Format Painter (the paintbrush icon), or copy the cells and use Paste Special with "Formats" selected, and the rule carries across to the new range.

Why did my highlighted duplicates disappear after I sorted the data? This usually means the rule's range reference wasn't set up with the right mix of absolute and relative references, so sorting shifted which cells the rule is actually checking. Rebuilding the rule with correctly locked references (as shown in the row-highlighting example) fixes this.

Is there a limit to how many Conditional Formatting rules I can have in one workbook? There's no meaningful practical limit for typical use, though having dozens of overlapping rules across a large workbook can make it noticeably slower and much harder to maintain. It's worth periodically checking Manage Rules and removing anything no longer needed.

Why does my duplicate-highlighting rule miss entries that look identical? This is almost always the same hidden-space or invisible-character issue covered in my post on "Cleaning Messy Data". Conditional Formatting's COUNTIF-based duplicate check is just as sensitive to trailing spaces as a regular formula, so cleaning the data first will fix highlighting that seems to be missing obvious repeats.

Comments

Post a Comment

Popular posts from this blog

Data Validation in Excel: Building Dropdown Lists That Actually Work

-
Image
  Data Validation in Excel: Building Dropdown Lists That Actually Work Data Validation restricts what someone can type into a cell, most commonly by turning it into a dropdown list of allowed values. Instead of hoping everyone types "NSW" exactly the same way every time, a dropdown forces them to pick from a fixed list, which quietly prevents a huge amount of the messy, inconsistent data that causes formulas to fail later. I once inherited a spreadsheet where the "Region" column had been typed freely for over a year by half a dozen different people. Between them, NSW had been entered as "NSW," "N.S.W," "nsw," and, memorably, "Sydeny" (missing the second S, presumably typed in a hurry). Every SUMIF and COUNTIF built on that column was quietly wrong, because Excel treated each spelling as a completely different value. Fixing the historical data took a proper cleanup, but the bit that actually stopped it happening again was d...
Read more

Text Functions in Excel: LEFT, RIGHT, MID and TEXTJOIN Explained

-
Image
  Text Functions in Excel: LEFT, RIGHT, MID and TEXTJOIN Explained LEFT, RIGHT and MID pull out specific characters from a piece of text based on position, while CONCATENATE and TEXTJOIN combine separate pieces of text back together into one. Between the two groups, they cover almost everything you'll ever need to reshape messy or combined text without retyping anything by hand. For a long stretch early on, I built mailing labels for client letters by manually copying each address piece — name, street, suburb, postcode — one at a time into a single cell, retyping commas and spaces between them by hand, client by client. It genuinely didn't occur to me that Excel could stitch text together with a formula, because nobody had ever shown me, and I'd never gone looking. Someone finally saw me doing this for what must have been the fortieth label that afternoon and asked, not unkindly, whether I'd heard of the ampersand symbol. That one small nudge saved me an embarrassin...
Read more

Absolute vs Relative References in Excel: What the $ Sign Actually Does

-
Image
  Absolute vs Relative References: What the $ Sign Actually Does A relative reference (like A2) shifts automatically when you copy or drag a formula elsewhere, while an absolute reference (like $A$2) stays locked to that exact cell no matter where the formula goes. The dollar sign is what tells Excel to lock a reference in place, and mixing the two correctly is what lets one formula work properly when copied across an entire column or row. Years ago I built a commission calculator that multiplied each salesperson's revenue by a commission rate sitting in one fixed cell, say F1. I wrote the formula for row 2, dragged it down 200 rows the way I always did, and didn't think to check it closely because the first few results looked plausible. It wasn't until someone further down the list got a commission figure of literally zero dollars that I went back and realised the formula had quietly shifted its reference to the commission rate cell on every single row, pointing at an ...
Read more