How to Make a Heatmap

A heatmap shows a numeric value as color in each cell of a grid whose rows and columns are categories (or binned numbers). To make one, pivot your data into that matrix and map the values to a color scale: a color-scale conditional format in Excel, sns.heatmap() in Python, or a Square mark colored by a measure in Tableau.

What Data Format Does a Heatmap Need?

A heatmap needs a matrix (a wide 2D table): rows are one category, columns are another, and each cell holds a single value. If your data is "long" (one row per observation), pivot it into that shape 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 Do You Make a Heatmap in Excel?

In Excel, a heatmap is a cell range with a color-scale conditional format: select the value cells and choose Home → Conditional Formatting → Color Scales, with no chart or add-in needed. By default Excel spreads the colors from the lowest to the highest value in the selection.

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). For calendar heatmaps, pivot-table heatmaps and tuning a 3-color scale, see how to make a heatmap in Excel.

How Do You Make a Heatmap in Python With Seaborn?

In Python, pivot the long data into a matrix with pandas pivot_table(), then pass the matrix to seaborn's sns.heatmap(), which draws one colored cell per value and a color bar beside the grid.

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",
)
# pivot_table sorts rows alphabetically; put weekdays back in order
matrix = matrix.reindex(["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"])

# 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. Pass numeric_only=True to corr(): since pandas 2.0, text columns such as day_of_week raise an error without it.

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

For masking half of a correlation matrix, choosing a colormap and sizing labels, see the seaborn heatmap tutorial.

How Do You Make a Heatmap in Tableau?

In Tableau, a heatmap is a grid of Square marks: one dimension on Rows and one on Columns define the cells, and a measure on Color shades each square by its value.

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

Use a sequential palette when values run from low to high with no meaningful midpoint, and a diverging palette when values fall above or below a meaningful center such as zero or a target. The palette names below are matplotlib/seaborn colormaps.

• 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

Most heatmap problems come from the color scale or the grid size, not the data: scales that differ between charts, a midpoint that doesn't exist, one outlier that washes out every other cell, or more cells than anyone can read.

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