SolveWithExcel

Excel Challenge #13: Heatmap Sales Matrix (Conditional Formatting)

Difficulty

Intermediate

Estimated Time

30–40 minutes

The Problem

A matrix of numbers is useful—but a heatmap makes patterns obvious instantly.

In this challenge, you’ll take a region-by-product matrix and apply heatmap-style conditional formatting so high and low sales stand out visually. This is a common technique used in Excel dashboards, leadership reporting, and performance reviews.

Dataset

Use the SalesData and Matrix setup from Excel Challenge #12.

You should already have:

  • A matrix with Regions as rows
  • Products as columns
  • Row totals, column totals, and grand total

Your Task

Part A — Apply Heatmap Formatting

On the Matrix sheet, apply conditional formatting to the core matrix cells only (Region × Product area):

  • Use a 3-color scale:
    • Low values → light
    • Mid values → medium
    • High values → strong

This should highlight:

  • Strong region-product combinations
  • Weak performance areas
  • Overall distribution patterns

Part B — Heatmap Rules for Totals (Optional)

Apply conditional formatting separately to:

  1. Region Totals
  • Highlight top and bottom region totals
  1. Product Totals
  • Highlight top and bottom product totals

(Keep these ranges separate from the main heatmap so the scales remain meaningful.)

Part C — Add a Quick Insight Callout (Optional)

Add a small insight area on the right:

  • Best Region (highest Region Total)
  • Best Product (highest Product Total)

All formula-driven.

Requirements

  • Use Conditional Formatting rules only
  • No manual coloring
  • The heatmap must update automatically if SalesData changes

Learning Objectives

By completing this challenge, you will learn how to:

  • Turn matrix reports into visual heatmaps
  • Use 3-color scales effectively
  • Separate conditional formatting ranges for clarity
  • Improve dashboard readability without charts

Why This Matters

Heatmaps are used for:

  • Territory and regional reviews
  • Product mix analysis
  • Leadership dashboards
  • Identifying performance “hot spots” quickly

This challenge upgrades your analysis from readable → instantly interpretable.

Next Challenge

Excel Challenge #14: Interactive Sales Matrix (Dropdown Filters + Dynamic Formulas)

🔗 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.