Create a Funnel Chart in Excel with Python

A funnel chart shows how many people make it through each stage of a process: visitors who viewed a product, added it to the basket, started checkout and ordered; leads that became opportunities and then deals; applicants who were screened, interviewed and hired. Each stage is a bar centred on the one above, narrower as people drop out, so the biggest leak is the biggest step inwards. Excel 2019 and later have a funnel chart type, but it belongs to a newer chart format that neither openpyxl nor xlsxwriter can write. The classic workaround — a stacked bar chart whose first series is invisible — works in every version of Excel and is fully controllable from Python. This page is part of Creating Charts in Excel with openpyxl.

Prerequisites

pip install openpyxl pandas

funnel_counts.xlsx has five stages of an online checkout for one month, from 48,000 visitors to 2,250 orders. Stages must be in process order, largest first.

Step 1 — Compute the conversion rates

Two rates matter in a funnel: the step conversion (what share of the previous stage made it to this one) and the overall conversion (what share of the first stage got this far). pandas computes both in two lines:

import pandas as pd

f = pd.read_excel("funnel_counts.xlsx")
f["Step %"] = (f["Count"] / f["Count"].shift()).fillna(1)
f["Overall %"] = f["Count"] / f["Count"].iloc[0]
print(f.assign(**{c: (f[c] * 100).round(1) for c in ["Step %", "Overall %"]}).to_string(index=False))
worst = f.iloc[1:]["Step %"].idxmin()
print("biggest drop-off: into", f.loc[worst, "Stage"], f"({f.loc[worst, 'Step %']:.1%} made it)")

The step rates find the leak: fewer than 30% of the people who viewed a product added it to the basket — the weakest step by far — while almost three in four of those who started checkout finished it. Overall, 4.7% of visitors ordered. These numbers are what the chart’s labels will carry, and the weakest step is where improvement work should start.

Step 2 — Write the table with a live spacer column

Write the stages with a spacer column that is a formula, so the funnel stays centred if someone edits a count, and a label column that combines the stage name with its numbers:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Funnel"
ws.append(["Label", "Spacer", "Count", "Step %", "Overall %"])
for i, row in enumerate(f.itertuples(index=False), start=2):
    label = f"{row.Stage}  ·  {row.Count:,}" + ("" if i == 2 else f"  ·  {row._2:.0%} of previous")
    ws.append([label, f"=($C$2-C{i})/2", row.Count, row._2, row._3])
    ws.cell(i, 4).number_format = ws.cell(i, 5).number_format = "0.0%"
ws.column_dimensions["A"].width = 44
print([ws.cell(r, 1).value for r in range(2, 7)])

=($C$2-C3)/2 is half the difference between the widest stage and this one; stacking that much empty space before each bar centres it. Because it is a formula, Excel recalculates it on open — and so does LibreOffice, which is how the render check below sees the right shape. Putting the count and the step rate into the category label keeps the numbers readable even for the narrowest bars at the bottom, where a data label inside the bar would not fit.

Step 3 — Build the chart

A horizontal stacked bar chart with two series — spacer and count — where the spacer has no fill and no outline:

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.type = "bar"
chart.grouping = "stacked"
chart.overlap = 100
chart.gapWidth = 25
chart.add_data(Reference(ws, min_col=2, max_col=3, min_row=1, max_row=6), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=6))

spacer, stage = chart.series
spacer.graphicalProperties.noFill = True
spacer.graphicalProperties.line.noFill = True
stage.graphicalProperties.solidFill = "4338CA"
stage.graphicalProperties.line.noFill = True

chart.x_axis.scaling.orientation = "maxMin"     # first stage at the top
chart.x_axis.delete = False
chart.y_axis.delete = True                      # the value axis means nothing here
chart.y_axis.majorGridlines = None
chart.y_axis.scaling.max = int(f["Count"].iloc[0])
chart.legend = None
chart.title = "Checkout funnel, September"
chart.width, chart.height = 16, 8
ws.add_chart(chart, "A9")
wb.save("funnel_chart.xlsx")
print(chart.grouping, chart.overlap, chart.x_axis.scaling.orientation)

grouping = "stacked" with overlap = 100 puts the spacer and the count end to end in one bar. In a horizontal bar chart the category axis is the vertical one, and Excel draws the first category at the bottom; orientation = "maxMin" flips it so the funnel reads top to bottom. The value axis is hidden deliberately — its numbers would describe spacer plus count, which is meaningless — and its maximum is pinned to the first stage so the widest bar fills the plot. This is the one openpyxl chart on the site where an axis stays deleted on purpose.

Step 4 — Render and check

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf funnel_chart.xlsx > /dev/null 2>&1
pdftoppm -png -r 60 -f 1 -l 1 funnel_chart.pdf preview && ls preview*

The render shows five indigo bars centred on each other, narrowing sharply at the third stage, with each label giving the stage, its count and its step rate. The shape makes the finding from Step 1 visible without reading a number: the big inward step is between product views and basket.

Counting the stages from an event log

The counts above arrived pre-aggregated. More often the source is an event log — one row per user action — and the funnel has to be counted. The important rule is that a stage counts users who reached it and every stage before it; someone who jumped straight to checkout from an email link is not part of the basket step:

import numpy as np

rng = np.random.default_rng(37)
stages = ["visit", "view", "basket", "checkout", "order"]
keep = [1.0, 0.45, 0.30, 0.48, 0.73]
events = []
for user in range(5000):
    for stage, p in zip(stages, keep):
        if rng.random() > p:
            break
        events.append((user, stage))
events += [(9001, "checkout"), (9001, "order")]              # skipped the earlier steps
log = pd.DataFrame(events, columns=["user", "event"])

reached = log.pivot_table(index="user", columns="event", aggfunc="size", fill_value=0).gt(0)
reached = reached[stages].cumprod(axis=1).astype(bool)         # must have every earlier stage
counts = reached.sum()
print(counts.to_dict())
print("users with an order but no visit:", int(log.query("event == 'order'")["user"].isin(
    log.query("event == 'visit'")["user"]).eq(False).sum()))

cumprod along the stages turns a user's row of reached/not-reached flags into "reached this stage in sequence": the first missing stage zeroes everything after it. User 9001, who ordered without a recorded visit, is counted nowhere in the funnel — and the last line reports such users, because a lot of them usually means tracking is broken rather than that customers teleport. The resulting counts feed Step 1 unchanged. For large logs the pivot is the expensive part; a groupby(["user", "event"]).size() followed by unstack does the same with less memory, and reading only the user and event columns from the export with usecols saves the most time of all.

Step 5 — Compare two periods

A single funnel shows where people drop out; two funnels side by side show whether a change helped. The same function works for any period's counts — build one chart per period with identical settings and the same scaling.max, so bar widths are comparable between them. Different maxima would make a smaller month look just as wide. For more than two periods, a table of step rates with conditional formatting is easier to read than a row of funnels — see Turn a Pivot Table into a Heatmap in Excel.

Common pitfalls

• Stages out of order. A funnel only makes sense in process order, largest first.
• Category order upside down. Set x_axis.scaling.orientation = "maxMin" on horizontal bar charts.
• Spacers visible. Set both noFill and line.noFill on the spacer series.
• Static spacer values. Write the spacer as a formula so edited counts keep the funnel centred.
• A value axis on show. It displays spacer plus count; hide it and put the numbers in the labels.

Conclusion

Compute step and overall conversion rates with pandas, write the stages with a spacer formula =(first − this)/2 and a label that carries the count and step rate, and chart spacer and count as a stacked horizontal bar with the spacer transparent, categories reversed, the value axis hidden and its maximum set to the first stage. The result reads like Excel's native funnel, works in every Excel version, and keeps its shape when the numbers change.

Frequently asked questions

Can openpyxl create Excel's built-in funnel chart? No. The funnel type introduced in Excel 2019 uses a newer chart format that openpyxl and xlsxwriter do not write. A stacked bar chart with an invisible spacer series produces the same look and works in all Excel versions.

How do I centre the bars in a funnel chart? Add a spacer series before the values equal to half the difference between the first stage and each stage, and give it no fill and no outline. The equal empty space on the left pushes each bar to the centre.

Why is my funnel upside down? Horizontal bar charts draw the first category at the bottom. Set chart.x_axis.scaling.orientation = "maxMin" so the first stage appears at the top.

How do I show conversion rates on a funnel chart? Calculate them in pandas and include them in the category label text, such as "Orders · 2,250 · 73% of previous". Labels inside narrow bars do not fit, while category labels always do.

Related

• Up one level: Creating Charts in Excel with openpyxl — every chart type openpyxl supports.
• Create a Stacked Column Chart with openpyxl — the stacking technique underneath.
• Build a Waterfall Chart in Excel with Python — another chart made from invisible bars.
• Build a Sales Pipeline Report — a funnel of deals rather than visitors.