Excel Conditional Formatting

Conditional Formatting is a powerful feature in Excel that automatically changes the appearance of a cell — such as its color, font, or icon — based on the value it contains. Instead of manually highlighting cells, Excel does it automatically based on rules that are set up. This makes it easy to spot trends, exceptions, and patterns in data at a glance.

What is Conditional Formatting?

Think of Conditional Formatting as a rule: "If a cell meets this condition, apply this formatting."

Examples

• Highlight all sales figures above 10,000 in green.
• Color all scores below 50 in red to indicate failure.
• Add a yellow background to any cell with a past due date.

Accessing Conditional Formatting

Go to Home → Conditional Formatting. A dropdown menu appears with several options.

Highlight Cells Rules

This is the most commonly used option. It highlights cells based on simple conditions.

Available Rules

• Greater Than: Highlights cells with a value greater than a specified number.
• Less Than: Highlights cells with a value less than a specified number.
• Between: Highlights cells with a value between two numbers.
• Equal To: Highlights cells matching a specific value.
• Text That Contains: Highlights cells containing specific text.
• A Date Occurring: Highlights cells with dates such as "yesterday", "this week", "last month".
• Duplicate Values: Highlights repeated or unique values.

How to Apply Highlight Cells Rules

1. Select the range of cells to format.
2. Go to Home → Conditional Formatting → Highlight Cells Rules.
3. Choose the rule type (e.g., Greater Than).
4. Enter the comparison value and choose a format (e.g., Light Red Fill).
5. Click OK.

Example

  Data in B2:B10 → Sales amounts
  Rule: Greater Than 5000 → Green Fill with Dark Green Text

  Cells with sales > 5000 are automatically highlighted green.
  All other cells remain unchanged.

Top / Bottom Rules

This option highlights the top or bottom performing values in a range.

Options Include

• Top 10 Items
• Top 10% of values
• Bottom 10 Items
• Bottom 10% of values
• Above Average
• Below Average

Example

  Select scores in C2:C20 → Apply "Top 10 Items" rule → Yellow fill
  → The 10 highest scores will be highlighted in yellow automatically.

Data Bars

Data Bars add a colored bar inside each cell, similar to a mini bar chart. The length of the bar reflects the cell's value relative to other values in the range. Larger values have longer bars.

How to Apply

1. Select the data range.
2. Go to Home → Conditional Formatting → Data Bars.
3. Choose a gradient or solid bar color.

Example

  Monthly sales figures: 3000, 7000, 5000, 9000, 2000

  After applying Data Bars:
  Each cell shows a horizontal bar indicating its proportion.
  → 9000 has the longest bar, 2000 has the shortest.

Color Scales

Color Scales apply a color gradient to a range of cells. For example, a red-yellow-green scale makes the lowest values red, middle values yellow, and highest values green — like a heat map.

How to Apply

1. Select the data range.
2. Go to Home → Conditional Formatting → Color Scales.
3. Choose a color scale option from the gallery.

Example

  Temperature data for a week:
  32, 35, 40, 28, 38, 41, 27

  Red-Yellow-Green Color Scale:
  → 27 and 28 → Red (low)
  → 35 and 38 → Yellow (middle)
  → 40 and 41 → Green (high)

Icon Sets

Icon Sets display a small icon inside each cell — such as arrows, traffic lights, or stars — to indicate the value's status relative to others.

How to Apply

1. Select the data range.
2. Go to Home → Conditional Formatting → Icon Sets.
3. Choose an icon set from the gallery.

Example

  Performance scores: 85, 60, 45, 90, 72

  Traffic Light Icon Set:
  → 85, 90 → Green circle (high performance)
  → 60, 72 → Yellow circle (medium performance)
  → 45     → Red circle (low performance)

Creating a Custom Rule

For more control, custom formulas can be used to define the rule. This allows complex conditions that are not available in the preset options.

How to Create a Custom Rule

1. Select the range of cells.
2. Go to Home → Conditional Formatting → New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter the formula in the input box.
5. Click Format and choose the desired formatting.
6. Click OK.

Example

  Highlight entire rows where the "Status" column (D) = "Overdue"

  Range selected: A2:D20 (the entire data)
  Formula entered: =$D2="Overdue"

  → Any row where column D contains "Overdue" will be highlighted.
  → The $ locks column D, but the row number adjusts for each row.

Managing and Clearing Rules

Viewing All Rules

Go to Home → Conditional Formatting → Manage Rules to see all rules applied to the selected range. Rules can be edited, deleted, or reordered here.

Clearing Rules

• Go to Home → Conditional Formatting → Clear Rules.
• Choose to clear rules from selected cells or from the entire sheet.

Summary

• Conditional Formatting automatically applies formatting to cells based on their values or custom rules.
• Highlight Cells Rules color cells that meet conditions like greater than, less than, or containing specific text.
• Top/Bottom Rules highlight the best or worst performing values.
• Data Bars show mini bar charts inside cells based on value size.
• Color Scales apply a heat map gradient to show high and low values visually.
• Icon Sets display icons (arrows, traffic lights, stars) inside cells to indicate performance levels.
• Custom Rules use formulas to apply formatting based on complex conditions.