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

Create a Gantt Chart in Excel with Python

Draw a project or month-end close timeline as a native Excel Gantt chart from a task list. Build it from a stacked bar chart with an invisible start series, put the first task at the top and the dates along the bottom, fix the date range, shade progress, and — as an alternative — paint a Gantt grid with conditional formatting.
Charts & imagesReport layouts & templatesopenpyxl

A Gantt chart — one horizontal bar per task, placed along a calendar — is the standard way to show a plan: a project, a month-end close, a release schedule. Excel has no Gantt chart type, but the classic workaround turns a stacked bar chart into one, and openpyxl can build it natively, so the chart lives in the workbook next to the task list and can be adjusted by anyone who opens it. This page, part of Creating Charts in Excel with openpyxl, builds a close timeline from a task list with start and end dates, including progress shading and a cell-based alternative.

How a stacked bar chart becomes a Gantt chart Each task's bar is made of stacked segments. The first segment runs from the chart's zero to the task's start date and is invisible. The next segment is the days already done, in a dark colour, and the last is the days remaining, in a light colour. Together the visible segments start on the start date and end on the end date. Days remaining light segment Days done dark segment Start date invisible — no fill, no line

Prerequisites

Bash
pip install openpyxl pandas

close_plan.xlsx lists six month-end close tasks with a start date, an end date and how far each has progressed.

Step 1 — Turn dates into start and duration columns

The chart needs, for each task, where the bar starts and how long it is. End dates are inclusive — a task that starts and ends on 12 October takes one day — so the duration is the difference plus one. Split the duration into done and remaining days for the progress shading:

Python
from datetime import date
import pandas as pd

plan = pd.read_excel("close_plan.xlsx")
plan["Days"] = (plan["End"] - plan["Start"]).dt.days + 1
plan["Done"] = (plan["Days"] * plan["Done %"] / 100).round(1)
plan["Remaining"] = plan["Days"] - plan["Done"]
print(plan[["Task", "Start", "Days", "Done", "Remaining"]].to_string(index=False))

The durations are calendar days, which is what a chart along a calendar axis needs: a task from Friday to Monday spans four days on the chart even if only two are working days.

Step 2 — Build the stacked bar chart

Write the task table, then make a horizontal (type = "bar") stacked chart of three series — start, done, remaining — and hide the first one by removing its fill and outline:

Python
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook(); ws = wb.active; ws.title = "Plan"
ws.append(["Task", "Start", "Done", "Remaining", "End"])
for r in plan.itertuples(index=False):
    ws.append([r.Task, r.Start.date(), r.Done, r.Remaining, r.End.date()])
for row in ws.iter_rows(min_row=2, min_col=2, max_col=5):
    row[0].number_format = row[3].number_format = "dd mmm"
ws.column_dimensions["A"].width = 18
for col in "BCDE":
    ws.column_dimensions[col].width = 10

n = len(plan) + 1
chart = BarChart()
chart.type = "bar"
chart.grouping = "stacked"
chart.overlap = 100
chart.gapWidth = 40
chart.add_data(Reference(ws, min_col=2, max_col=4, min_row=1, max_row=n), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=n))

start_series, done_series, remaining_series = chart.series
start_series.graphicalProperties.noFill = True
start_series.graphicalProperties.line.noFill = True
done_series.graphicalProperties.solidFill = "0B6157"
remaining_series.graphicalProperties.solidFill = "B7D7D2"
print([s.tx.strRef.f for s in chart.series])

The start series is still there — it pushes the visible segments along the axis — but without a fill or an outline it cannot be seen. Excel stores dates as numbers, so stacking a date with durations works: the bar for "Reconcile bank" starts at the serial number of 2 October and extends by three days.

Step 3 — Put the first task at the top and fix the date range

Two defaults make the chart read wrongly. Bar charts draw the first category at the bottom, so the plan appears upside down; reverse the category axis. And the value axis starts at zero — the year 1900 — so the bars sit far to the right; set its limits to the plan's first and last dates, as serial numbers:

Python
def excel_serial(d):
    return (d - date(1899, 12, 30)).days

first, last = plan["Start"].min().date(), plan["End"].max().date()
chart.x_axis.scaling.orientation = "maxMin"
chart.y_axis.crosses = "max"
chart.y_axis.scaling.min = excel_serial(first)
chart.y_axis.scaling.max = excel_serial(last) + 1
chart.y_axis.majorUnit = 2
chart.y_axis.number_format = "dd mmm"
chart.x_axis.delete = False
chart.y_axis.delete = False
chart.legend = None
chart.title = "Month-end close — October 2026 (dark: done, light: remaining)"
chart.width, chart.height = 22, 9
ws.add_chart(chart, "G2")
wb.save("close_gantt.xlsx")
print("axis", chart.y_axis.scaling.min, "to", chart.y_axis.scaling.max, "=", first, "to", last)

In a bar chart the x axis is the vertical category axis and the y axis the horizontal value axis — openpyxl keeps the names of a column chart. Reversing the categories moves the date axis to the top; crosses = "max" moves it back to the bottom, where readers expect a timeline. 1899-12-30 is the base date that makes the serial numbers match Excel's: excel_serial(date(2026, 10, 1)) is 46296. The legend is removed because it would list the invisible start series too; the title explains the two colours instead. The extra day on the maximum leaves room for the last task's bar, which ends at the end of its final day. The delete = False lines keep both axes visible in Excel; Format Chart Axes with openpyxl explains why they are needed.

The close timeline as the chart shows it A Gantt layout for the October close. Collect exports runs 1 to 2 October and is complete. Reconcile bank runs 2 to 4 October and is complete. Post accruals runs 5 to 6 October and is half done. Variance review runs 6 to 8 October, 20 percent done. Management pack runs 8 to 11 October and Pack to CFO is on 12 October, neither started. Plan — one row per task, days across A B C D E F G H I J K L M 1 Task 1 2 3 4 5 6 7 8 9 10 11 12 2 Collect exports █ █ 3 Reconcile bank █ █ █ 4 Post accruals █ ░ 5 Variance review ▓ ░ ░ 6 Management pack ░ ░ ░ ░ 7 Pack to CFO ░

Step 4 — A Gantt grid with conditional formatting

A chart is best for presenting a plan. For a plan people edit day to day, a grid is often more practical: one column per day, cells shaded when the task is running. It is built entirely from a conditional format, so it updates as soon as a start or end date is changed:

Python
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter

grid = wb.create_sheet("Grid")
grid.append(["Task", "Start", "End"])
days = pd.date_range(first, last)
for i, d in enumerate(days, start=4):
    grid.cell(row=1, column=i, value=d.date()).number_format = "d"
    grid.column_dimensions[get_column_letter(i)].width = 4
for r in plan.itertuples(index=False):
    grid.append([r.Task, r.Start.date(), r.End.date()])
for row in grid.iter_rows(min_row=2, min_col=2, max_col=3):
    for c in row:
        c.number_format = "dd mmm"
grid.column_dimensions["A"].width = 18

last_col = get_column_letter(3 + len(days))
grid.conditional_formatting.add(f"D2:{last_col}{n}", FormulaRule(
    formula=["AND(D$1>=$B2,D$1<=$C2)"], fill=PatternFill("solid", start_color="0B6157", end_color="0B6157")))
grid.freeze_panes = "D2"
wb.save("close_gantt.xlsx")
print(len(days), "day columns;", "rule range", f"D2:{last_col}{n}")

The formula compares each column's date in row 1 with the row's start and end dates. The anchoring is what makes one rule work for the whole grid: D$1 keeps the row fixed so every cell looks at the date header, and $B2/$C2 keep the columns fixed so every cell looks at its own task's dates. Highlight Differences Between Two Columns with openpyxl covers formula rules in more depth.

Chart or grid for a Gantt view Two panels. A stacked bar chart is a presentation object: it looks polished, shows progress shading and prints well, but changes need the data table edited. A conditional-formatting grid lives in the cells: it updates the moment a date changes and is easy to annotate, but it is less polished and gets wide for long plans. Stacked bar chart polished, prints well progress shading edit the table to change it Conditional-format grid updates as dates change easy to annotate wide for long plans

The grid takes two more rules well: grey weekend columns, so non-working days stand out, and a highlighted column for today, so the sheet shows at a glance which tasks should be under way. Add them after the task rule; openpyxl applies rules in the order they are added, and the task fill takes priority where they overlap because it was added first:

Python
from openpyxl.styles import Font

grid.conditional_formatting.add(f"D1:{last_col}{n}", FormulaRule(
    formula=["WEEKDAY(D$1,2)>5"], fill=PatternFill("solid", start_color="E6E6E6", end_color="E6E6E6")))
grid.conditional_formatting.add(f"D1:{last_col}1", FormulaRule(
    formula=["D$1=TODAY()"], font=Font(bold=True, color="C00000")))
wb.save("close_gantt.xlsx")
print(sum(1 for d in days if d.weekday() >= 5), "weekend days shaded;", len(grid.conditional_formatting), "rule ranges")

WEEKDAY(date, 2) numbers Monday as 1 and Sunday as 7, so values above 5 are the weekend. The today rule recalculates every time the file is opened, which is exactly what a live plan needs.

Common pitfalls

  • Tasks upside down. Reverse the category axis with orientation = "maxMin".
  • Date axis at the top. After reversing, set y_axis.crosses = "max".
  • Bars squashed to the right. Set the value axis limits to the plan's dates as serial numbers.
  • Off-by-one durations. With inclusive end dates, duration is end − start + 1.
  • A visible start segment. Remove both the fill and the outline of the first series.
  • Missing axes in Excel. Set delete = False on both axes.

Performance notes

A Gantt chart of a few dozen tasks is tiny. For plans with hundreds of tasks, the chart becomes unreadable before it becomes slow; group tasks into phases, or use the grid, which scrolls.

Conclusion

Compute each task's start and inclusive duration, split into done and remaining days, and chart start, done and remaining as a stacked horizontal bar chart with the start series made invisible. Reverse the category axis so the first task is on top, move the date axis back to the bottom with crosses = "max", and set its limits to the plan's dates as Excel serial numbers. For an editable view, shade a grid of day columns with one anchored conditional-formatting formula.

Frequently asked questions

Can Excel make a Gantt chart? Not as a built-in chart type, but a stacked bar chart becomes one: the first series holds each task's start date and is made invisible, and the second holds its duration, so the visible bar starts on the start date and is as long as the task.

Why are my Gantt tasks upside down? A bar chart draws the first category at the bottom. Reverse the category axis with chart.x_axis.scaling.orientation = "maxMin" so the first task is at the top, and set chart.y_axis.crosses = "max" to keep the date axis at the bottom.

How do I set the date range of the Gantt chart? Excel stores dates as serial numbers, so the value axis limits must be numbers too. Convert the first and last dates with (d - date(1899, 12, 30)).days and assign them to chart.y_axis.scaling.min and max.

How do I show progress on each task? Split each duration into two stacked series, days done and days remaining, and colour them differently. The done segment then fills from the start of the bar as work is completed.

More on charts & images

More on charts & images →