Turn a Pivot Table into a Heatmap in Excel

A pivot of orders by weekday and hour, sales by region and month, or tickets by team and priority is a grid of numbers that nobody reads cell by cell. What people want to see is the pattern: the busy Friday evenings, the region that fell behind, the team with the backlog. Colouring each cell by its value turns the grid into a heatmap, and Excel's colour-scale conditional formats do it while leaving the numbers in the cells, sortable and live. The work is in the choices — which scale, which cells it covers, whether rows share one scale — and in writing them from Python so every run of the report looks the same. This page is part of Creating Pivot Tables from Excel Data.

Prerequisites

pip install pandas openpyxl xlsxwriter

Two exports: web_orders.xlsx, four weeks of order timestamps from an online shop, and regional_sales.xlsx, monthly sales in thousands for five regions, one of which — Online — is several times larger than the others.

Step 1 — Build the pivot

Count orders by weekday and hour. Make the weekday an ordered category so the rows come out Monday to Sunday rather than alphabetically, and reindex the hours so every hour has a column even if it had no orders:

import pandas as pd

orders = pd.read_excel("web_orders.xlsx", parse_dates=["Timestamp"])
days = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
orders["Day"] = pd.Categorical(orders["Timestamp"].dt.strftime("%a"), categories=days, ordered=True)
orders["Hour"] = orders["Timestamp"].dt.hour

grid = (orders.pivot_table(index="Day", columns="Hour", values="Amount", aggfunc="count", observed=False)
              .reindex(columns=range(24), fill_value=0).fillna(0).astype(int))
print(grid.shape)
print(grid.iloc[:, 6:12])

observed=False keeps all seven weekdays even if one had no orders; reindex(columns=range(24)) does the same for hours. A heatmap with missing rows or columns is misleading, because the gap looks like a pattern. Create a Pivot Table from Excel with pandas covers the pivot options.

Step 2 — Write it with a sequential colour scale

Write the grid and add a two-colour scale from white to the brand colour over the data cells only — not the headers:

with pd.ExcelWriter("orders_heatmap.xlsx", engine="xlsxwriter") as xw:
    grid.to_excel(xw, sheet_name="By hour")
    wb, ws = xw.book, xw.sheets["By hour"]
    n_rows, n_cols = grid.shape
    ws.conditional_format(1, 1, n_rows, n_cols, {
        "type": "2_color_scale", "min_color": "#FFFFFF", "max_color": "#4338CA"})
    ws.set_column(1, n_cols, 4.5)
    ws.set_column(0, 0, 6)
    ws.freeze_panes(1, 1)
print("written", grid.values.max(), "at peak")

Row 0 and column 0 hold the labels, so the scale covers rows 1 to n_rows and columns 1 to n_cols (xlsxwriter counts from zero). Narrow columns make the grid read as a picture rather than a table. The busy weekday evenings stand out immediately — the peak is 81 orders in one hour — and so does the quieter weekend, where each day has about 590 orders against 840 to 900 on weekdays. That is the pattern the pivot was built to find.

Step 3 — Keep totals out of the scale

Adding a total row and column is natural for a pivot, but a total is many times larger than any cell, and if it sits inside the scale's range every ordinary cell gets the palest colour. Add margins, then limit the scale to the inner cells:

with_totals = grid.copy()
with_totals["Total"] = with_totals.sum(axis=1)
with_totals.loc["Total"] = with_totals.sum()

with pd.ExcelWriter("orders_heatmap_totals.xlsx", engine="xlsxwriter") as xw:
    with_totals.to_excel(xw, sheet_name="By hour")
    wb, ws = xw.book, xw.sheets["By hour"]
    bold = wb.add_format({"bold": True, "top": 1})
    ws.conditional_format(1, 1, len(grid), grid.shape[1], {
        "type": "2_color_scale", "min_color": "#FFFFFF", "max_color": "#4338CA"})
    ws.set_row(len(grid) + 1, None, bold)
print(with_totals["Total"].to_dict())

The scale's range is exactly the grid — len(grid) rows and grid.shape[1] columns — so the total row and column are written with a bold format and no colour. Getting the range wrong by one is the most common heatmap bug; it shows as a single dark total cell and a washed-out grid.

Step 4 — A diverging scale for changes

Colour a change with a sequential scale and a small decline looks the same as a small rise. A diverging scale has a neutral colour at zero and runs to one colour for negative values and another for positive ones. Pivot regional sales, compute the month-on-month change, and centre the scale on zero:

sales = pd.read_excel("regional_sales.xlsx")
wide = sales.pivot(index="Region", columns="Month", values="Sales")
change = wide.pct_change(axis=1).iloc[:, 1:]
print((change * 100).round(1).iloc[:, -3:])

with pd.ExcelWriter("regional_heatmap.xlsx", engine="xlsxwriter") as xw:
    change.to_excel(xw, sheet_name="MoM change")
    wb, ws = xw.book, xw.sheets["MoM change"]
    ws.conditional_format(1, 1, *change.shape, {
        "type": "3_color_scale",
        "min_color": "#DC2626", "mid_type": "num", "mid_value": 0, "mid_color": "#FFFFFF",
        "max_color": "#2563EB"})
    ws.set_column(1, change.shape[1], 9, wb.add_format({"num_format": "+0.0%;-0.0%;0.0%"}))

"mid_type": "num", "mid_value": 0 pins white to exactly zero; without it Excel puts white at the midpoint of the values, so if everything grew, the smallest growth would be painted red. Red–blue is easier on colour-blind readers than red–green. West shrinks in six of the seven months and Online grows in six of seven, and both patterns read at a glance.

Step 5 — One scale per row

In the regional pivot, Online sells several times more than any other region. A single scale over sales makes Online's row dark and every other row pale, so the question "which month was best for each region?" cannot be answered by colour. Give each row its own scale:

with pd.ExcelWriter("regional_rows.xlsx", engine="xlsxwriter") as xw:
    wide.to_excel(xw, sheet_name="Sales")
    ws = xw.sheets["Sales"]
    for r in range(1, len(wide) + 1):
        ws.conditional_format(r, 1, r, wide.shape[1], {
            "type": "2_color_scale", "min_color": "#FFFFFF", "max_color": "#0F9488"})
print(wide.round(0).astype(int))

Each row's lightest cell is its own worst month and its darkest its best month. That shows the shape of each region's year but hides size — dark in the East row means something different from dark in the Online row — so say so in a note next to the table, and keep the numbers visible.

Step 6 — The same with openpyxl

When the workbook already exists or is built with openpyxl, ColorScaleRule does the same job:

from openpyxl import load_workbook
from openpyxl.formatting.formatting import ConditionalFormattingList
from openpyxl.formatting.rule import ColorScaleRule

wb = load_workbook("regional_heatmap.xlsx")
ws = wb["MoM change"]
ws.conditional_formatting = ConditionalFormattingList()   # drop the existing rule
last = f"{ws.cell(1, ws.max_column).column_letter}{ws.max_row}"
ws.conditional_formatting.add(f"B2:{last}", ColorScaleRule(
    start_type="min", start_color="DC2626", mid_type="num", mid_value=0, mid_color="FFFFFF",
    end_type="max", end_color="2563EB"))
for row in ws["B2":last]:
    for cell in row:
        cell.number_format = ";;;"          # hide the numbers: a pure heatmap
wb.save("regional_heatmap_pure.xlsx")
print(len(ws.conditional_formatting), "rule range(s)")

The number format ;;; has three empty sections — positive, negative, zero — so nothing is displayed but the value is still in the cell, visible in the formula bar and usable by formulas. Use it only for dashboards where the colours are the message; in most reports the numbers should stay. Add Data Bars and Colour Scales with openpyxl covers the other rule types.

Performance notes

A colour scale is one rule over a range, however many cells it covers, so a 7 × 24 grid and a 500 × 365 grid cost the same to write. Per-row scales add one rule per row, which is fine for dozens or hundreds of rows; for thousands, Excel itself slows down when scrolling, and a chart or a pre-computed fill colour per cell is the better choice. Pre-computing fills in Python also freezes the colours, which suits archived reports but stops them from updating when someone edits a value.

Common pitfalls

• Totals inside the scale. They flatten everything else; restrict the range to the inner cells.
• Missing rows or columns. Reindex so every weekday, hour or month appears, even with zero.
• Sequential scales on changes. Use a diverging scale with the midpoint fixed at zero.
• Rows of very different size. One scale hides the smaller rows; use one scale per row and label it.
• Red–green palettes. Hard to tell apart for many readers; use red–blue or a single-hue scale.

Conclusion

Build the pivot with complete rows and columns, write it with pandas, and add a colour-scale conditional format over the data cells only — sequential for volumes, diverging with a zero midpoint for changes, and one scale per row when rows differ greatly in size. Keep totals outside the range and the numbers visible unless the sheet is purely a picture. The rules are the same in xlsxwriter and openpyxl; only the syntax differs.

Frequently asked questions

How do I make a heatmap in Excel from pandas? Write the pivot with to_excel, then add a colour-scale conditional format over its data cells — with xlsxwriter's conditional_format and type 2_color_scale or 3_color_scale, or openpyxl's ColorScaleRule.

Why are all my heatmap cells the same pale colour? The scale's range probably includes a total row or column, whose large values set the maximum. Restrict the range to the inner cells of the grid.

How do I colour negative values red and positive values blue? Use a three-colour scale with the midpoint set to the number 0 and white as its colour, red as the minimum colour and blue as the maximum. Without a fixed midpoint Excel uses the middle of the data instead of zero.

Can each row have its own colour scale? Yes: add one conditional format per row, each covering only that row's data cells. It shows the pattern within each row, but colours can no longer be compared between rows.

Related

• Up one level: Creating Pivot Tables from Excel Data — group-bys, crosstabs and summaries.
• Export a pandas Pivot Table to Excel Formatted — the pivot's other formatting.
• Build a Cohort Retention Table from Excel Data — a heatmap that shows decay over time.
• Apply Conditional Formatting with xlsxwriter — every rule type xlsxwriter supports.