Advanced Conditional Formatting with Formulas

Hi ,

Most Excel users stick to built-in conditional formatting rules. A colour scale here, a “greater than” rule there.

But the moment you start using formulas, everything changes.

Formula-based conditional formatting allows you to highlight entire rows, flag missing data, catch errors, track deadlines, and more. All automatically and in real time.

In this guide, you’ll learn practical, real-world examples you can apply immediately to stop manually scanning spreadsheets for issues.

1. Format Entire Rows Based on One Cell

One of the most useful techniques is formatting an entire row based on a value in a single column.

Scenario

You have an orders table like this:

You want any row where the status is “Cancelled” to stand out.

Steps

  =$F2="Cancelled"

Key Concept

Now the entire row highlights when the status changes to Cancelled, and it updates instantly.

2. Compare Two Columns Automatically

Scenario

You want to flag rows where actual spend exceeds budget:

Formula

  =$H5>$G5

This checks if Actual is greater than Budget.

Improve It with Thresholds

Add a second rule to highlight rows where spend is more than 10% over budget:

  =$H5>$G5*1.1

Result

You can instantly identify problem areas without scanning numbers.

Important Tip

Use Manage Rules to control priority. Place the stricter rule above the general one so it takes precedence.

3. Flag Missing Data in Rows

This is one of the most practical rules you’ll use.

Scenario

You have a contact list with missing emails, phone numbers, or account managers:

Formula

  =SUM(--ISBLANK($C5:$H5))

How It Works

Result

Fill in the missing data and the formatting disappears automatically.

4. Highlight Rows Containing Keywords

Scenario

You have a Comments column and want to find specific keywords like “urgent” or “burned out”.

Formula for “urgent”

  =SEARCH("urgent",$H5)

Why This Works

Add Multiple Signals

Create another rule for “burned out”:

  =SEARCH("burned out",$H5)

Result

You can prioritise without reading every comment.

5. Create Date-Based Alerts That Update Automatically

Scenario

You manage contract renewals and need to know:

Overdue Contracts – format red

  =$G7<=TODAY()

Contracts Due in Next 7 Days – format yellow

  =$G7<=TODAY()+7

Key Benefit

The TODAY function recalculates automatically.

That means:

Tip: Always set rule priority in the Conditional Formatting Manager so overdue items override upcoming ones:

6. Detect Duplicates with Full Control

Excel has a built-in duplicate rule, but it flags both the original and duplicate.

Using formulas gives you more control. Let’s take the following data:

Highlight Only Duplicate Entries

  =COUNTIF($E$5:$E5, $E5)>1

How It Works

Result

Advanced: Match Multiple Columns

Use COUNTIFS to detect duplicate email and session combinations:

  =COUNTIFS($E$5:$E5, $E5, $G$5:$G5, $G5)>1

Result

This creates a clear hierarchy of issues.

7. Create Banded Rows That Work with Filters

Excel Tables offer banded rows, but they limit some functionality like dynamic arrays, so we need an alternative approach.

You can recreate banding with conditional formatting.

Formula

  =MOD(SUBTOTAL(3, $C$5:$C5),2)

How It Works

Result

This is far more flexible than standard table formatting.

Take Your Conditional Formatting Further

Everything in this guide builds on core Excel functions.

The more functions you know, the more powerful your conditional formatting becomes.

Functions like XLOOKUP, SUMIFS, and IF can be combined to create highly intelligent rules that adapt to complex data scenarios.

If you want to go deeper into formulas and unlock more advanced techniques, you can explore the full Advanced Excel Formulas course here.

If you start using these formula-based rules, you’ll spend far less time checking spreadsheets and far more time acting on insights.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub