Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Build a Waterfall Chart in Excel with Python

Show how an opening figure becomes a closing one — revenue bridges, budget walks, cash movements — as a waterfall chart built with xlsxwriter. Uses the stacked-column technique with an invisible base, formula-driven helper columns that update when inputs change, green and red steps, total bars and value labels.
Charts & imagesxlsxwriter

A waterfall — or bridge — chart explains a change. Revenue was 500 last year and 570 this year: how much came from new customers, how much was lost to churn, what did price changes add? Each step floats at the level where the previous one ended, increases in one colour, decreases in another, with the opening and closing totals as full bars. Finance teams expect it in every monthly pack. Excel has a built-in waterfall chart type, but the libraries that write Excel files from Python do not support it, so this guide, part of Building Excel Reports with xlsxwriter, builds one the classic way: a stacked column chart with an invisible base.

A revenue bridge from 500 to 570 A waterfall chart with seven bars. Opening revenue of 500 is a full bar. New customers add 180 and upsell adds 60, floating green steps rising to 740. Churn removes 120 and costs remove 90 as red steps, with price changes adding 40 in between. Closing revenue of 570 is a full bar. 0 200 400 600 800 Opening New cust. Upsell Churn Price Costs Closing 500 +180 +60 −120 +40 −90 570

Prerequisites

Bash
pip install xlsxwriter pandas

Step 1 — Lay out the steps

A bridge is a list of labelled steps: an opening total, the movements, and a closing total. Keep the inputs in two columns — label and value — and mark which rows are totals:

Python
import pandas as pd
import xlsxwriter

steps = pd.DataFrame({
    "label": ["Opening", "New customers", "Upsell", "Churn", "Price changes", "Costs", "Closing"],
    "value": [500, 180, 60, -120, 40, -90, None],
    "is_total": [True, False, False, False, False, False, True],
})
steps.loc[6, "value"] = steps.loc[:5, "value"].sum()
print(steps["value"].tolist())         # [500.0, 180.0, 60.0, -120.0, 40.0, -90.0, 570.0]

The closing total is the sum of everything above it — computed here so the example is self-consistent, and written as a formula in the workbook in the next step so it stays correct when someone edits a movement.

Step 2 — Helper columns that do the geometry

The chart needs four series per row: an invisible base, the increase, the decrease and the total. They follow from a running total. Write them as formulas, so the chart updates when a user changes a value, and store the computed results with each formula so every viewer sees the right numbers — the reason is explained in Store Formula Results with xlsxwriter So Every Viewer Shows Values:

Python
running, rows = 0, []
for value, is_total in zip(steps["value"], steps["is_total"]):
    before = running
    running = value if is_total else running + value
    rows.append({"base": 0 if is_total else min(before, running),
                 "up": 0 if is_total else max(value, 0),
                 "down": 0 if is_total else max(-value, 0),
                 "total": value if is_total else 0,
                 "running": running})
geo = pd.DataFrame(rows)

wb = xlsxwriter.Workbook("bridge.xlsx")
ws = wb.add_worksheet("Bridge")
ws.write_row("A1", ["Step", "Value", "Running", "Base", "Increase", "Decrease", "Total"])
n = len(steps)
for i in range(n):
    r = i + 2                                   # Excel row number
    ws.write(f"A{r}", steps.loc[i, "label"])
    if i == n - 1:
        ws.write_formula(f"B{r}", f"=SUM(B2:B{r - 1})", None, steps.loc[i, "value"])
    else:
        ws.write(f"B{r}", steps.loc[i, "value"])
    g = geo.loc[i]
    if steps.loc[i, "is_total"]:
        ws.write_formula(f"C{r}", f"=B{r}", None, g.running)
        ws.write_row(f"D{r}", [0, 0, 0])
        ws.write_formula(f"G{r}", f"=B{r}", None, g.total)
    else:
        ws.write_formula(f"C{r}", f"=C{r - 1}+B{r}", None, g.running)
        ws.write_formula(f"D{r}", f"=MIN(C{r - 1},C{r})", None, g.base)
        ws.write_formula(f"E{r}", f"=MAX(B{r},0)", None, g.up)
        ws.write_formula(f"F{r}", f"=MAX(-B{r},0)", None, g.down)
        ws.write(f"G{r}", 0)
print(geo[["base", "up", "down", "total"]].values.tolist()[3])      # [620.0, 0.0, 120.0, 0.0]

The base of each step is the lower of the running totals before and after it: an increase starts from where the previous step ended, a decrease from where it will end. Churn is a good check: the running total falls from 740 to 620, so its base is 620 and the red segment on top is 120 tall, spanning exactly 620 to 740.

How one decrease step is drawn The churn step is a stacked column. The lowest segment is the invisible base of 620, the running total after the step. On top sits the red decrease segment of 120, reaching 740, the running total before the step. The increase and total segments for this row are zero. Decrease: 120 (red) 620 → 740 Base: 620 (no fill) min(before, after)

Step 3 — The stacked column chart

Add the four helper columns as stacked series. The base gets no fill and no border, which makes it invisible; the others get their colours. A small function keeps the chart definition in one place:

Python
def add_bridge_chart(wb, ws, n, labels=None):
    chart = wb.add_chart({"type": "column", "subtype": "stacked"})
    categories = ["Bridge", 1, 0, n, 0]
    chart.add_series({"name": "Base", "categories": categories, "values": ["Bridge", 1, 3, n, 3],
                      "fill": {"none": True}, "border": {"none": True}})
    colours = {4: ("Increase", "#0B6157"), 5: ("Decrease", "#B91C1C"), 6: ("Total", "#4338CA")}
    for col, (name, colour) in colours.items():
        series = {"name": name, "categories": categories, "values": ["Bridge", 1, col, n, col],
                  "fill": {"color": colour}, "gap": 50}
        if labels:
            series["data_labels"] = {"custom": labels, "position": "inside_end"}
        chart.add_series(series)
    chart.set_title({"name": "Revenue bridge"})
    chart.set_legend({"none": True})
    chart.set_y_axis({"major_gridlines": {"visible": True, "line": {"color": "#E7EBEF"}}})
    ws.insert_chart("I2", chart, {"x_scale": 1.4, "y_scale": 1.2})
    return chart

The list form ["Bridge", first_row, col, last_row, col] avoids building A1-style strings; rows and columns are zero-based and row 0 is the header. gap narrows the space between columns, which makes a bridge easier to follow. The legend is hidden because the colours are self-explanatory and "Base" should not be advertised.

Step 4 — Label the steps with their signed values

Bars alone make readers estimate. Label each visible segment with the step's value — including the minus sign for decreases, which the decrease series itself does not contain — using custom data labels that point at the value column:

Python
labels = [{"value": f"=Bridge!$B${r}"} for r in range(2, n + 2)]
add_bridge_chart(wb, ws, n, labels)
wb.close()
print(len(labels), labels[3])           # 7 {'value': '=Bridge!$B$5'}

Each custom label shows the content of the referenced cell, so the churn segment reads −120 rather than 120, and editing a value in column B updates the label along with the bar. The same list is applied to the increase, decrease and total series; a label on a zero-height segment has nothing to sit on, but check the result in Excel and delete any stray zero labels with {"delete": True} entries in the custom list if your version shows them.

From a list of movements to a bridge chart List the opening total, the movements and the closing total, compute a running total, derive base, increase, decrease and total helper columns as formulas with stored values, stack them as column series with an unfilled base, and label each step from the value column. List steps label, value, total? Running total formula column Helper columns base, up, down, total Stacked columns base has no fill Custom labels from the value column

Step 5 — Know the limits

The invisible-base technique works while the running total stays above zero, which covers most revenue and cost bridges. When a step takes the running total below zero — a loss-making bridge, a cash balance that goes overdrawn — a stacked column cannot draw a segment that straddles the axis, and the picture breaks. For those, build the chart once with Excel's native waterfall type and let Python update only the data it points at; opening and saving that workbook with openpyxl would drop the native chart, so write the numbers with a tool that leaves charts alone, or keep the chart in a template that the data sheet feeds. Charts in general are covered in Add a Chart to an Excel File with xlsxwriter.

Rendering the finished workbook in LibreOffice shows the expected bridge: a blue opening bar at 500, green steps rising to 740, a red step down to 620, a small green step, a red step down to 570 and a blue closing bar at 570. LibreOffice did not draw the cell-referenced custom labels in our test — Excel does — so if the workbook will also be opened in LibreOffice, add the signed values to the category labels ("Churn −120") as a fallback.

Common pitfalls

  • Base from the wrong side. Using the running total before a decrease as its base puts the red segment above the previous step. The base is the lower of before and after.
  • Totals in the running sum. A subtotal row must reset the running total to its own value, not add to it, or everything after it floats too high.
  • Hard-coded helper values. Static helper columns do not follow edited inputs. Use formulas, with stored results.

Conclusion

Without access to Excel's native waterfall type from Python, build the bridge as a stacked column chart: helper columns for an invisible base, increases, decreases and totals, derived from a running total with formulas that carry stored values, a base series with no fill, colours for up and down, and custom labels that read signed values from the input column. Keep the running total above zero, or use the native chart in a template when it cannot.

Frequently asked questions

Can xlsxwriter create Excel's built-in waterfall chart? No. The waterfall type Excel added in 2016 uses a newer chart format that neither xlsxwriter nor openpyxl writes. The stacked column technique with an invisible base series produces the same picture and works in every Excel version and in LibreOffice.

How does the invisible base work? Each step is a stacked column. The bottom segment, the base, is as tall as the lower of the running totals before and after the step and has no fill, so it is invisible; the visible segment on top is the increase or decrease, which therefore appears to float at the right height.

How do I show negative steps in red? Split the steps into two series, increases and decreases, each holding the positive size of the step or zero. Give the increase series a green fill and the decrease series a red one.

What if the running total goes below zero? The stacked technique assumes the running total stays positive, because stacked columns cannot mix negative and positive segments in one bar as a floating range. For bridges that cross zero, build the chart with Excel's native waterfall type in Excel, or use an up-down bar chart.

More on charts & images

More on charts & images →