Visual analysis and formatting

Excel Conditional Formatting Guide

Highlight trends, thresholds, duplicates, and exceptions in Excel with conditional formatting rules, formulas, and data bars.

Best rule types to start with

  • Use highlight cell rules for thresholds, duplicates, and top or bottom values.
  • Use data bars and color scales when you want quick visual comparison across rows.
  • Use formula-based rules when the condition depends on another column or a dynamic threshold.

Make rules easier to maintain

  • Store thresholds in input cells instead of hardcoding them inside rules.
  • Apply rules to structured Tables so they expand with new rows.
  • Combine conditional formatting with SORT and FILTER for interactive review workflows.

Conditional formatting workflow

  • Start with built-in color scales and data bars before writing custom formula rules.
  • Use helper columns when rule logic exceeds what a single CF formula can express clearly.
  • Apply rules to Excel Tables so formatting expands with new rows automatically.
  • Test rules on a copy — overlapping rules can hide each other unpredictably.

Recommended next steps

  • Highlight cells above/below thresholds with color scales before building complex custom rules.
  • Combine IF formulas with icon sets when you need both a calculated flag and a visual cue in dashboards.
  • Use a helper column when the rule logic is too complex for a single conditional-formatting formula.
  • Pair with SORT and FILTER in M365 for interactive review workflows on large datasets.

Rules analysts reuse

  • Highlight duplicates: =COUNTIF(A:A,A2)>1 applied to the key column.
  • Due in 7 days: =AND(B2>=TODAY(),B2<=TODAY()+7) on date columns.
  • Variance band: =ABS(C2)>0.1*D2 to flag actuals more than 10% off budget.
  • Top quartile: =A2>=PERCENTILE(A:A,0.75) for performance heatmaps.

People also ask

  • Formula-based vs built-in rules? Built-in for speed; formula rules when logic references other columns.
  • Why did my CF stop working? Table grew but rule range stayed fixed — apply to full Table column.
  • CF and performance? Large ranges with volatile formulas (INDIRECT, TODAY) can slow workbooks — scope carefully.

Deep-dive function map for Excel Conditional Formatting Guide

  • IF (/functions/if/): Returns one value if a condition is TRUE and another value if it's FALSE. Example: =IF(A1>100, "Over budget", "OK").
  • AND (/functions/and/): Returns TRUE if all arguments are TRUE; returns FALSE if any argument is FALSE. Example: =AND(A1>0, A1<100).
  • OR (/functions/or/): Returns TRUE if any argument is TRUE; returns FALSE only if all arguments are FALSE. Example: =OR(A1="Red", A1="Blue").
  • COUNTIF (/functions/countif/): Counts the number of cells that meet a criterion. Example: =COUNTIF(A:A, "Apple").
  • SUMIF (/functions/sumif/): Adds the cells specified by a given condition or criteria. Example: =SUMIF(A:A, "Apple", B:B).
  • FILTER (/functions/filter/): Filters a range of data based on criteria you define. Returns an array of values that meet the specified conditions. Example: =FILTER(A2:D10, C2:C10>100).

Two-week study plan

  • Week 1 — Read each related function page and copy one example per function into a practice Table.
  • Week 2 — Apply this guide to a real export from your job; document fixes in a changelog tab.
  • Explore categories: Logical, Math & Trig.
  • Run the fix-excel-formula-errors checklist when any formula shows #N/A, #VALUE!, #REF!, or #SPILL!.
  • Compare similar functions (VLOOKUP vs XLOOKUP, SUMIF vs SUMIFS) before standardizing team templates.
  • Export a PDF snapshot of your completed practice workbook for future reference and onboarding.

Error and troubleshooting cross-links

  • Fix #VALUE! (/errors/value-error/) — step-by-step causes and solutions in the error directory.
  • Fix #REF! (/errors/ref/) — step-by-step causes and solutions in the error directory.