What is the fastest way to apply conditional formatting in Excel?

Select the range, then use Top/Bottom Rules or Highlight Cells Rules directly from Home > Conditional Formatting.

How do I resolve overlapping rules?

In Manage Rules, adjust the priority and use ‘Stop If True’ to prevent conflicts.

How do I use a custom formula rule?

Go to New Rule > ‘Use a formula to determine which cells to format’ and set the range and formula. For example, =WEEKDAY($A2,2)>=6 shades weekends only.

Can I apply conditional formatting to a PivotTable?

Yes. Set the rule range to the PivotTable area and use the ‘Apply rule to all cells showing’ option.

What should I do if performance is slow?

Reduce the number of rules, minimize the applied range, and manage the data as a Table.

CONTENTS

Excel Conditional Formatting Guide: Basics, Top N, Icon Sets, and Formula Rules

To automatically highlight important values in reports, Excel conditional formatting is one of the most powerful tools available. This guide covers Quick Fixes → basic rules → visual effects → formula rules, along with rule conflicts and performance issues.

Quick Fix (Done in 3 Minutes)

Select the range: B2:F101
Top N: Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items…
Duplicate values: Highlight Cells Rules > Duplicate Values…
Data bars: Data Bars > Gradient Fill
Manage Rules: Manage Rules > organize priority & Stop If True

Core Concepts: Rules, Priority, and Range

Regularly review each rule’s Applies to range and priority. In formula rules, use $ to correctly lock relative and absolute references.

Manage Rules · Stop If True

Go to Home > Conditional Formatting > Manage Rules to move rules (↑↓), and select Stop If True when rules conflict.

Basic Rules in Practice

Duplicate Values

Conditional Formatting > Highlight Cells Rules > Duplicate Values…

Top/Bottom N

Quickly highlight high and low values with Top 10 Items, Top 10%, Bottom 10 Items, and similar rules.

Above/Below Average

Quickly identify deviations based on the average.

Visual Effects: Data Bars, Color Scales, and Icon Sets

Data bars are useful for relative comparisons by length, color scales show the distribution of continuous values, and icon sets are ideal for summarizing status.

Custom Formula Rules (Best 7)

// Highlight a specific month
=TEXT($A2,"yyyy-mm")=$H$1

// Shade weekends
=WEEKDAY($A2,2)>=6

// Department = Sales & below target
=AND($C2="Sales",$F2<$E2)

// Last 30 days
=TODAY()-$A2<=30

// Mark blank cells
=ISBLANK($D2)

// Duplicate key (name + phone)
=COUNTIFS($B:$B,$B2,$C:$C,$C2)>1

Alternatives and Related Features

Manage ranges with a Table for automatic expansion.
Use with filters and slicers to improve readability.
For PivotTables, check the “Apply rule to all cells showing” option.

Troubleshooting

Symptom	Cause	Solution
Formatting conflict	Overlapping rules	Adjust priority + Stop If True
Some cells are missed	Incorrect applied range	Reset Applies to
Formula rule applies to only one row	Incorrect locked references	Adjust $ (for example, =$B2>=$H$2)
Slow performance	Too many rules or an overly large range	Clean up rules and manage the data as a Table
Lost when the PivotTable refreshes	Incorrect application method	Check the PivotTable field-based application option
Color scale looks awkward	Midpoint not set	Set the midpoint to 0

엑셀 드롭다운 목록 만들기 — 데이터 유효성으로 기본·동적·의존형까지

Excel Conditional Formatting Guide: Basics, Top N, Icon Sets, Color Scales, and Formula Rules