Complete Guide to Excel Conditional Formatting: Formula Rules, Icon Sets, Data Bars, and Management Tips

Posted on by

Complete Guide to Excel Conditional Formatting: Formula Rules, Icon Sets, Data Bars, and Management Tips

Reports are easier to understand when patterns stand out before the numbers. Excel conditional formatting automatically applies colors, icons, data bars, and more based on values or formula results to improve readability. This guide covers practical examples you can use right away, from creating, managing, and copying rules to performance optimization and formula-based rules.

Quick Fix (3 Steps)

  1. Select the range.
  2. Go to Home → Conditional Formatting (Alt → H → L) and select a rule.
  3. Set the options, then click OK.

Conditional Formatting Basics

  • Rules / Applies To / Stop If True
  • Rule types: Highlight Cells, Top/Bottom, Data Bars, Color Scales, Icon Sets, and Use a Formula

Gallery Rules

  • Highlight duplicate or unique values
  • Top, bottom, and average-based rules
  • Data bars, color scales, and icon sets

Edit absolute thresholds in Manage Rules and set them as numbers, percentages, or formulas.

7 Formula Rule Examples

  1. Below target: =B4<$A4
  2. Weekend shading: =WEEKDAY($D4,2)>=6
  3. Due within 7 days: =AND($E4>=TODAY(),$E4<=TODAY()+7)
  4. Outliers (±2σ): =OR(B4>AVERAGE($B$4:$F$100)+2*STDEV.P($B$4:$F$100), B4<AVERAGE($B$4:$F$100)-2*STDEV.P($B$4:$F$100))
  5. Alternating row shading: =MOD(ROW(),2)=0
  6. Contains the text “Delayed”: =ISNUMBER(SEARCH("Delayed",$C4))
  7. Highlight the first row of each group: =A4<>A3

Managing, Prioritizing, and Copying Rules

  • Use Manage Rules to change priority and set Stop If True.
  • Edit Applies To directly; separate multiple ranges with ;.
  • Use Format Painter or Paste Special (Formats).

Using It with Tables, PivotTables, and Charts

  • Tables (Ctrl+T) structured reference example: =[@[Actual]]<[@[Target]]
  • For PivotTables, apply the rule to “All cells showing” the relevant field.
  • Charts can indirectly use the source formatting colors.

Performance Optimization

  • Minimize the number of rules and reduce ranges.
  • Minimize volatile functions.
  • Use Stop If True for early termination.

Practical Example (Sample Data)

DepartmentPerson in ChargeTargetJanuaryFebruaryMarchDue DateNotes
SalesHana Kim120981321102025-09-22Possible delay
SalesDul Lee1501601401552025-09-20
ManufacturingSet Park1301151001282025-09-25At risk
Human ResourcesNet Choi909288852025-09-18
FinanceDaseot Moon110100118952025-09-29Note: Review

Troubleshooting

SymptomCauseSolution
Applied only partiallyIncorrect Applies To rangeReset the range in Manage Rules
Overlapping colorsPriority not setMove rules up or down and use Stop If True
Formula not working correctlyIncorrect use of $ referencesRedesign the formula based on the top-left cell
PivotTable does not expandTarget is fixed to a rangeChange it to apply to the entire field
Slow performanceToo many rules or volatile functionsConsolidate rules and reduce ranges

Related Articles

{ “@context”: “https://schema.org”, “@type”: “FAQPage”, “mainEntity”: [ { “@type”: “Question”, “name”: “What cell is the basis for formulas in conditional formatting?”, “acceptedAnswer”: { “@type”: “Answer”, “text”: “Relative and absolute references expand based on the top-left cell of the selected range. Use $ appropriately to lock only the intended column or row.” } }, { “@type”: “Question”, “name”: “How do I set priority when rules overlap?”, “acceptedAnswer”: { “@type”: “Answer”, “text”: “Go to Home → Conditional Formatting → Manage Rules and use Move Up or Move Down to change the order. If needed, select ‘Stop If True’ to stop evaluating lower-priority rules.” } }, { “@type”: “Question”, “name”: “How can I keep conditional formatting in a PivotTable?”, “acceptedAnswer”: { “@type”: “Answer”, “text”: “In Manage Rules, set the rule to apply to ‘All cells showing’ the relevant field so the range expands automatically when you refresh the PivotTable.” } }, { “@type”: “Question”, “name”: “How can I optimize conditional formatting for large datasets?”, “acceptedAnswer”: { “@type”: “Answer”, “text”: “Reduce the number of rules and their applied ranges, and avoid volatile functions. When possible, consolidate rules into one formula-based rule and use Stop If True for early termination.” } } ] }
엑셀 CSV 한글 깨짐 완전 해결(UTF-8/ANSI, 가져오기, 파워쿼리, 메모장 변환, 구분자 문제)

Leave a Reply

Your email address will not be published. Required fields are marked *