📋 GS-Conditional-Formatting Guide

📩 1. Formula Name + Syntax

Feature: Conditional Formatting (menu-based tool)

Navigation: Format → Conditional formatting

Optional Formula Syntax (for custom rules):

=A1>100

Or

=AND(A1>50, A1<80)

📩 2. What it does

 

Applies visual formatting to cells based on specified conditions or values.

📄3. Use Case / Scenario

You are managing student grades and want any score below 40 to turn red automatically to highlight failures. Conditional formatting lets you do this instantly and visually without any formulas cluttering the sheet.

ExcelQuickGuide.com

🧪4. Interactive Example

Suppose you have marks in range B2:B6:

Name    | Marks
--------|-------
Alice   | 88
Bob     | 35
Charlie | 60
David   | 40
Eve     | 20

Steps to Apply:

  1. Select B2:B6
  2. Go to Format → Conditional formatting
  3. Set rule: Less than → enter 40
  4. Choose red fill color
  5. Click “Done”

Result: Bob and Eve’s cells turn red.

🛠️ 5. Real-Life Applications

  • Highlight overdue tasks in a project tracker.
  • Color-code stock inventory levels.
  • Flag missing entries (e.g. blank cells).
  • Visualize attendance patterns or test scores.
  • Use heatmaps for data visualization (min to max color scale).

⚠️6. Common Errors + Fixes

  • Formatting not applying: Often due to wrong range selection. Fix: Always check the “Apply to range” field.
  • Wrong formula logic: E.g. using =A1="Fail" when referencing wrong cell. Fix: Use absolute and relative references properly.
  • Formatting overlaps: Multiple rules may conflict. Fix: Order your rules and delete unused ones.

🧠 7. Bonus Tips

  • Use =ISBLANK(A1) to highlight empty cells.
  • Try custom formulas like =A1>TODAY() to mark future dates.
  • Color entire rows by selecting full range and using =$B2="Fail".
  • Use color scales for heatmaps (e.g., from red to green based on performance).
  • Conditional formatting works great in dashboards and reports.

🗂️ 8. Navigation

ExcelQuickGuide.com