How to Make a Heatmap

A heatmap encodes a numeric value as color across a grid of two categorical (or binned numeric) axes. The data prep is the same in every tool: pivot your long-format data into a matrix, then map values to a color scale. The differences below are mostly about the click-path or the function name.

Step 1: Shape Your Data Into a Matrix

A heatmap needs a 2D table — rows are one category, columns are another, cells are the value. If your data is "long" (one row per observation), pivot it first.

# Long format (typical raw data):
# day_of_week | hour | order_count
# Mon         | 9    | 12
# Mon         | 10   | 18
# ...

# Wide / matrix format (what a heatmap needs):
#            9   10   11   12  ...
# Mon       12   18   25   30
# Tue       10   16   22   28
# ...

How to Make a Heatmap in Excel

1. Arrange your data as a matrix (categories down column A, categories across row 1, values in the grid).
2. Select the value cells only — not the headers.
3. Home → Conditional Formatting → Color Scales.
4. Pick the "Green-Yellow-Red" or "Blue-White-Red" 3-color scale. For sequential data, choose a 2-color scale instead.
5. Optionally: Conditional Formatting → Manage Rules → Edit Rule to set explicit min/mid/max values so two heatmaps are comparable.

To hide the numbers and keep only the color: select the cells, Format Cells → Number → Custom → ;;; (three semicolons).

How to Make a Heatmap in Python (seaborn)

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

# 1. Pivot long-format data into a matrix
df = pd.read_csv("orders.csv")
matrix = df.pivot_table(
    index="day_of_week",
    columns="hour",
    values="order_count",
    aggfunc="sum",
)

# 2. Plot
plt.figure(figsize=(10, 4))
sns.heatmap(
    matrix,
    cmap="viridis",       # sequential — see notes below
    annot=True,           # write the value inside each cell
    fmt=".0f",            # integer formatting
    cbar_kws={"label": "Orders"},
)
plt.title("Orders by Day and Hour")
plt.tight_layout()
plt.savefig("heatmap.png", dpi=150)

For a correlation heatmap (values from -1 to +1), use a diverging cmap centered on zero:

corr = df.corr()
sns.heatmap(corr, cmap="RdBu_r", center=0, vmin=-1, vmax=1, annot=True)

How to Make a Heatmap in Tableau

1. Drag your row dimension (e.g., Day of Week) to Rows.
2. Drag your column dimension (e.g., Hour) to Columns.
3. Change the mark type from Automatic to Square.
4. Drag your measure (e.g., SUM(Orders)) to Color on the Marks card.
5. Click the Color legend → Edit Colors to switch palette and set fixed start/end values.
6. Optional: drag the same measure to Label to write values inside the cells.

Tableau's Density mark type also produces a heatmap, but it's for unbinned point data (lat/long heatmaps), not aggregated grids.

Pick the Right Color Scale

• Sequential (Viridis, Blues) — for ordered values with no special midpoint: counts, percentages, durations.
• Diverging (RdBu, BrBG) — for values with a meaningful zero or center: correlations, year-over-year change, residuals.
• Avoid rainbow / jet — it creates artificial banding and isn't perceptually uniform.

Annotate the Cells When the Grid Is Small

If your heatmap is roughly 12×12 or smaller, write the value into each cell (annot=True in seaborn, Label in Tableau). The color shows the pattern; the numbers let readers verify and extract specific values. For larger grids, drop the annotations and rely on color plus a tooltip.

Order Your Axes Meaningfully

If your categories have a natural order (Mon-Sun, Q1-Q4), use it. If they don't, sort by the row/column total or use hierarchical clustering (sns.clustermap) so adjacent rows share patterns. Random-order axes are the single biggest reason heatmaps look like noise.

Common Pitfalls

• Color scale recomputed per chart: two heatmaps with different min/max scales aren't comparable. Pin the scale.
• Diverging palette on non-diverging data: implies a midpoint that doesn't exist. Use sequential.
• Outliers crushing the scale: a single huge value flattens the rest. Cap the scale (vmax) or use a log/quantile transform.
• Too many cells: a 100×100 heatmap is a texture, not a chart. Aggregate, cluster, or filter.