You send a sales report with 200 rows. Your manager does not have time to inspect every number. If below-target orders are highlighted in red and above-target orders in green, the report becomes easier to review in seconds.

What is Conditional Formatting?

Conditional Formatting automatically changes the look of cells based on rules. Instead of manually coloring each cell, you define the condition once and Excel applies the formatting whenever the condition is met.

Simple rule: Use Conditional Formatting to guide attention, not to decorate the sheet.

Sales variance example

ProductSalesTargetVarianceStatus
Laptop Stand₹2,250₹3,000-₹750Below Target
Wireless Mouse₹5,200₹4,500₹700Above Target
Webcam₹5,000₹5,000₹0On Target

Common Conditional Formatting rules

Highlight CellsHighlight values greater than, less than or equal to a number.
Text ContainsHighlight cells that contain words like Urgent or Below Target.
Data BarsShow value size visually inside the cell.
Color ScalesApply gradient colors to show high, medium and low values.
RedRisk / negative
GreenPositive
BarRelative size

Using Data Bars

Data Bars are useful when you want to compare values visually without creating a separate chart. For example, you can apply Data Bars to the Sales column to quickly see which orders are larger.

Data bar example

ProductSalesData Bar
USB Hub₹3,000
Printer₹12,000
Monitor₹38,000
💡
Professional habit: Keep rules simple. One or two strong signals are better than a report full of colors.
⚠️
Common mistake: Applying rules to the wrong range. Always confirm the selected range before creating a rule.
🧪 Practice Lab

Highlight target performance automatically

Download one ZIP file containing the practice workbook, challenge workbook, solution workbook, cheat sheet, quiz and answer key.

📦 Download Lesson 14 ResourcesWorks as a standalone file and after website deployment

Mini assignment

  1. Calculate Variance as Sales minus Target.
  2. Highlight negative variance in red.
  3. Highlight positive variance in green.
  4. Highlight Urgent priority in red.
  5. Add Data Bars to the Sales column.
  6. Review if the report is easier to read.

Quick quiz

  1. What is Conditional Formatting used for?
  2. What does a Data Bar show?
  3. Why should too many colors be avoided?
  4. What should you check before applying a rule?

Answers: Rule-based formatting; relative value size; it makes reports noisy; selected range.

FAQ

No. It changes how the cell looks, not the value inside the cell.
Yes, but too many overlapping rules can make the report confusing.