Difficulty
Intermediate
Estimated Time
30–40 minutes
The Problem
Numbers alone don’t communicate urgency.
In real dashboards, visual cues are used to highlight what needs attention.
In this challenge, you’ll extend the KPI dashboard from the previous challenge by adding conditional formatting to make performance immediately readable.
Dataset
Use the MonthlySales data from Challenge #9:
| Month | Sales |
|---|---|
| 2025-01 | 15100 |
| 2025-02 | 14750 |
| 2025-03 | 15900 |
| 2025-04 | 16250 |
| 2025-05 | 16800 |
| 2025-06 | 17200 |
(Ensure Month is a real date.)
Your Task
Part A — KPI Calculations
On a Dashboard worksheet, reuse or calculate:
- Latest Month Sales
- Previous Month Sales
- Month-over-Month Change (%)
- Status (
Up / Down / Flat) - 6-Month Average Sales
(All formula-based.)
Part B — Conditional Formatting Rules
Apply conditional formatting to make KPIs visually meaningful:
- MoM Change %
- Green fill if > 0
- Red fill if < 0
- Neutral (no fill) if = 0
- Status Label
- Green text for
"Up" - Red text for
"Down" - Gray text for
"Flat"
- Green text for
- Latest Month Sales vs Average
- Green if Latest > 6-Month Average
- Red if Latest < 6-Month Average
Part C — KPI Card Layout (Optional)
Format KPIs to resemble simple cards:
- Clear labels
- Consistent spacing
- Bold values
Requirements
- Use Excel conditional formatting rules
- No manual coloring
- Rules must update automatically
Expected Outcome
At a glance, your dashboard should clearly show:
- Whether performance improved or declined
- Where attention is needed
- How current performance compares to recent history
Learning Objectives
By completing this challenge, you will learn how to:
- Use conditional formatting strategically
- Improve dashboard readability
- Turn KPIs into visual signals
Why This Matters
Conditional formatting is essential for:
- Executive dashboards
- Performance monitoring
- Exception-based reporting
This challenge moves your Excel work from informative → actionable.
Next Challenge
Excel Challenge #11: Top Products by Category (Dynamic Ranking)
🔗 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.
SolveWithExcel 