Heat Maps: Visualizing Patterns with Excel Conditional Formatting and AI

A heat map shows values as colors — dark or bright means high, light means low — so patterns jump out without reading numbers. This tutorial covers Excel conditional-formatting heat maps and how Copilot and ChatGPT can build or extend them.

Table of Contents

• What Is a Heat Map
• Building a Heat Map with Conditional Formatting
• Creating One with Copilot
• Advanced Heat Maps via ChatGPT
• Example: Seaborn Heat Map
• Best Practices

What Is a Heat Map

A heat map is a grid where each cell's color reflects its value. It's ideal for cross-tables — like sales by region (rows) and month (columns) — where you want to spot hot and cold spots at a glance.

Building a Heat Map with Conditional Formatting

1. Select your number grid.
2. Home → Conditional Formatting → Color Scales → Red–Yellow–Green.
3. Excel shades each cell: high = green, mid = yellow, low = red.

This needs no AI, but it's the base both assistants build on.

Creating One with Copilot

Select the grid and ask:

Apply a color-scale heat map to this table and highlight
the highest value in each row.

Copilot applies the scale and can add a rule for row maxima, saving you the manual formatting clicks.

Advanced Heat Maps via ChatGPT

Excel color scales are limited to one gradient. ChatGPT can write Python to build a labeled, annotated heat map with custom palettes — useful for reports or slides.

Example: Seaborn Heat Map

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

# df: rows = Region, columns = Month, values = Sales
sns.heatmap(df, annot=True, cmap="YlGnBu", fmt=".0f")
plt.title("Sales by Region and Month")
plt.show()

annot=True prints the number in each cell; cmap sets the color scheme. You export the image and place it in your Excel report.

Best Practices

• Use sequential colors (light→dark) for one metric; avoid rainbow scales that mislead.
• Keep the grid small enough to read — 10×12 cells max is comfortable.
• Add numbers (annotations) so the map informs, not just decorates.