SolveWithExcel

Excel Challenge #5: Conditional Formatting for Risk Monitoring

Difficulty

Early Intermediate

Estimated Time

15–25 minutes

The Problem

You’ve already flagged high-risk orders using formulas.
Now it’s time to make risks immediately visible.

Your task is to apply conditional formatting so high-risk orders stand out visually, allowing managers to scan the sheet and spot issues instantly.

This is a common pattern in operational dashboards and control reports.

Dataset

Use the Orders dataset from Challenge #4, including:

  • Order ID
  • Customer
  • Order Date
  • Quantity
  • Unit Price
  • Order Value
  • Risk Flag

Your Task

Part A — Highlight High-Risk Orders

Apply conditional formatting so that:

  • Entire rows are highlighted when Risk Flag = "High Risk"
  • Use a clear, attention-grabbing style (e.g., light red fill, bold text)

Part B — Visual Risk Levels (Optional Extension)

Add a second rule to visually distinguish:

  • Very High Risk
    • Order Value ≥ 15,000
  • Normal
    • All other orders

Use different colors to differentiate levels.

Part C — Summary Signal

Enhance the Summary section so that:

  • The High-Risk Orders count changes color when the value is greater than 0
  • This acts as a visual alert for decision-makers

Requirements

  • Use Excel’s Conditional Formatting (no formulas for coloring)
  • Rules must respond dynamically to data changes
  • Formatting should remain readable and professional

Expected Outcome

  • High-risk orders are immediately visible without reading values
  • The summary visually signals when risk exists
  • The sheet becomes suitable for operational monitoring

Hints (Optional)

  • Use “Use a formula to determine which cells to format”
  • Apply rules to entire rows using absolute/relative references
  • Keep color usage minimal and consistent

Learning Objectives

By completing this challenge, you will learn how to:

  • Translate logic into visual signals
  • Build scan-friendly Excel sheets
  • Improve usability of analytical spreadsheets

Next Challenge

Excel Challenge #6: Rolling 3-Month Sales Average (Trend Analysis)

🔗 View reference solution on GitHub
(After you’ve tried the challenge)

Want more practical Excel challenges?
Subscribe to the Solve With Excel newsletter and get new problems delivered to your inbox.