CF Pattern Library — Excelligence

▶ 94%

▶ 67%

▶ 42%

▶ 78%

Threshold Highlight

Color cells above or below a fixed or dynamic threshold

Cell / Column VALUE

▾

When to Use

KPI monitoring — flag when metrics go out of range

Audit sampling — highlight exceptions above materiality

Commission tiers — visualize threshold crossings

When threshold changes frequently — use dynamic ref instead

Scope Guidance

Apply to a full column or named range. Anchor the threshold with $ when referencing a config cell. Never hardcode the threshold value inside the rule — it makes maintenance painful.

▶ #1

▶ #2

▶ #4

▶ #7

Top / Bottom N

Highlight the N highest or lowest values in a range

Range VALUE

▾

When to Use

Leaderboards — instantly surface top performers

Audit risk ranking — highlight highest exposure items

Variance analysis — flag the largest deviations

When N needs to change — hardcoded N in LARGE() is brittle

Scope Guidance

Apply to a fixed range like $B$2:$B$51. The LARGE/SMALL functions need the full range locked — mix of absolute/relative rows causes highlight drift as the range shifts.

▶ INV-001

▶ INV-002

▶ INV-001

▶ INV-004

Duplicate Detection

Flag all instances of a value that appears more than once

Column VALUE

▾

When to Use

Invoice auditing — catch duplicate payment submissions

Data validation — find repeated IDs in a key column

Commission reconciliation — flag duplicate transaction entries

Multi-column duplicate check — COUNTIFS handles this better

Scope Guidance

Lock the column reference ($A$2:$A$1000) but leave the row in the formula relative (A2). This lets the rule walk down the column while always counting against the full range.

▶ 3/10

▶ 3/16

▶ 3/20

▶ 3/28

Overdue / Date Past

Highlight dates that have passed today or are approaching

Column DATE

▾

When to Use

Action item tracking — surface past-due items automatically

Contract renewals — flag expirations within 30 days

Audit findings — highlight items past remediation deadline

Static date comparisons — just use a helper column instead

Scope Guidance

TODAY() recalculates on every open. Apply to the full date column. Chain two rules — overdue (red) and approaching (amber) — in that priority order so overdue always wins.

=AND(C2<TODAY(),C2<>““) =AND(C2>=TODAY(),C2<=TODAY()+7)

⚠ Always include the blank check (C2<>““) on the overdue rule. Without it, empty date cells evaluate as 0, which is before TODAY(), and your whole empty column turns red.

▶ Mon

▶ Sat

▶ Sun

▶ Tue

Weekend Highlighter

Shade weekend rows or date cells in schedule grids

Row / Range DATE

▾

When to Use

Project timelines — shade non-working days

Schedule grids — visual rhythm for weekly views

Payroll — flag weekend entries that need approval

When you need full row shading — lock column, leave row relative

Scope Guidance

To shade full rows: apply rule to the entire row range (e.g., $A2:$Z2), anchor the date column ($A2), leave row relative. WEEKDAY returns 1=Sunday, 7=Saturday.

=$D2=“FAIL“ =$D2=“PASS“ =ISNUMBER(SEARCH(“review“,$D2))

▶ #N/A

▶ 1,240

▶ #REF!

▶ 880

Error Cell Highlight

Surface formula errors instantly across a dataset

Range / Table TEXT

▾

When to Use

QA layer on lookup columns — catch #N/A before reporting

Audit workbooks — make broken references impossible to miss

Shared workbooks — immediate signal when data changes break formulas

Don't use on input columns — errors there are data problems, not formula problems

Scope Guidance

Apply to all formula columns in the table. ISERROR() catches all error types. Use ISNA() if you only want to surface #N/A specifically (common on lookup columns).

=ISERROR(B2) =ISNA(B2)

▶ ████

▶ ────

▶ ████

▶ ────

Full Row Highlight

Shade an entire row when a condition in one column is true

Row VALUE

▾

When to Use

Exception reporting — shade entire row when flagged

Priority tiers — visually separate high-priority records

Status filtering — make closed/complete rows recede visually

Tables with many columns — test performance before applying to 50+ columns

Scope Guidance

Apply to the full row range (e.g., $A2:$Z2). The trigger column must be column-locked ($E2) — lock the column letter, leave the row number relative so it walks down each row.

=$E2=“HIGH“ =$F2>1000 =AND($E2=“OPEN“,$F2>TODAY())

⚠ The most common mistake: forgetting to lock the column ($E2 not E2). Without the $, the rule shifts horizontally across columns and evaluates against the wrong column for every cell.

Data Bars

In-cell bar chart scaled proportionally to value

Column VISUAL

▾

When to Use

Quick distribution view — see relative size without a chart

Progress columns — budget utilization, completion percentage

Dense tables — adds visual signal without adding columns

Negative value columns — mixed bars look messy; use a real chart

Scope Guidance

No formula needed — built-in rule under Data Bars in the CF menu. Set Minimum to Number: 0 and Maximum to Number: [your max] for a stable scale. Auto-scaling stretches to the column max, which changes as data changes.

Home → Conditional Formatting → Data Bars Min: Number = 0 Max: Number = [your expected max or 100 for %] ☑ Show Bar Only

⚠ “Automatic“ min/max means the bars rescale every time data changes. A column where 80% looks like a full bar today will look like a half bar tomorrow if a 160% row is added. Lock your max.

▶ 98

▶ 61

▶ 44

▶ 12

3-Color Scale (Heat Map)

Gradient fill from min to max — red/amber/green or custom

Range VISUAL

▾

When to Use

Matrices — control matrices, risk heat maps, coverage grids

Score tables — immediately surface highest/lowest performers

Variance grids — green = favorable, red = unfavorable

Long column lists — color scales are hard to read vertically; use threshold rules instead

Scope Guidance

Built-in rule under Color Scales. Set all three points to Number type (not Percentile) when your data has a fixed meaningful range. Percentile-based scales make mediocre data look green just because it's above the median.

Home → Conditional Formatting → Color Scales → More Rules Min: Number = 0 → Red Midpoint: Number = 50 → Amber Max: Number = 100 → Green Min = Green, Max = Red

⚠ Default color scales use Percentile — the middle 50% always gets amber regardless of actual values. For governance purposes, always switch to Number and set explicit min/mid/max anchors so the color means something fixed.