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

Format Chart Axes with openpyxl

Make the axes of an openpyxl chart say what you mean: keep them visible in Excel, title them, fix the scale and the step between labels, show thousands as "k", switch gridlines on or off, use a logarithmic scale for fast growth, rotate crowded category labels, and move labels clear of negative bars.
Charts & imagesopenpyxl

A chart's axes decide how it is read. An axis that starts at an arbitrary value exaggerates small changes; labels such as 45000 next to 50000 are harder to scan than 45k and 50k; category labels that overlap become a smudge. openpyxl exposes every axis setting Excel has, but not always under obvious names, and one default — axes that Excel does not show at all — catches almost everyone. This page, part of Creating Charts in Excel with openpyxl, goes through the settings that matter in reports, from making the axes visible to rotating crowded labels.

Where each axis setting lives in openpyxl A table mapping axis settings to openpyxl attributes. Visibility is axis.delete, which must be False. The title is axis.title. The range is axis.scaling.min and max, and the step is majorUnit. Label format is number_format. Gridlines are majorGridlines and minorGridlines. A log scale is scaling.logBase. Label rotation is txPr. Label position is tickLblPos. openpyxl attribute Example Visible in Excel axis.delete False Range and step scaling.min / max, majorUnit 0, 100, 20 Label format number_format #,##0,"k" Gridlines majorGridlines None to remove Log scale scaling.logBase 10 Label angle txPr rot=-2700000

Prerequisites

Bash
pip install openpyxl

The examples chart twelve weeks of website visits, which grow quickly, and a weekly margin figure that starts negative.

Step 1 — Make the axes visible

In openpyxl charts the horizontal axis is chart.x_axis and the vertical one chart.y_axis (for bar charts, which are horizontal, the names stay the same but the directions swap). Each axis has a delete attribute; openpyxl 3.1 leaves it unset, and Excel treats an unset value as "hidden". Set both to False on every chart:

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

wb = Workbook(); ws = wb.active; ws.title = "Weekly"
ws.append(["Week", "Visits", "Margin %"])
for i in range(1, 13):
    ws.append([f"Week {i:02d}", int(1200 * 1.35 ** i), round(-4 + i * 0.9, 1)])

def line_chart(title):
    chart = LineChart()
    chart.title = title
    chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=13), titles_from_data=True)
    chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=13))
    chart.x_axis.delete = False
    chart.y_axis.delete = False
    chart.legend = None
    chart.width, chart.height = 18, 8
    return chart

visits = line_chart("Weekly visits")
visits.x_axis.title = "Week"
visits.y_axis.title = "Visits"
print(visits.x_axis.delete, visits.y_axis.delete)

LibreOffice ignores the missing flag and draws the axes, so a chart checked only in LibreOffice looks fine; in Excel the same chart shows plot lines floating with no scale. A small helper that creates every chart with delete = False removes the problem for good. Axis titles are plain strings; openpyxl turns them into chart text.

Step 2 — Fix the scale and the label format

By default Excel chooses the range and step. Set them when the default misleads — a range that does not start at zero for a measure where zero matters, or steps that fall on awkward numbers — and set a number format so the labels are easy to read:

Python
visits.y_axis.scaling.min = 0
visits.y_axis.scaling.max = 50000
visits.y_axis.majorUnit = 10000
visits.y_axis.number_format = '#,##0,"k"'
ws.add_chart(visits, "E2")
print(ws["B13"].value, "visits in week 12")

The axis now runs from 0 to 50k in steps of 10k. In the format #,##0,"k", the comma after the last 0 divides the displayed value by 1,000 and "k" adds the suffix; two commas would divide by a million, for "m". The format applies only to the axis labels, not to the data, so the cells keep their full values. Format Numbers in Thousands and Millions in Excel explains the scaling commas.

Step 3 — Gridlines on, off or finer

openpyxl gives the value axis major gridlines by default. Remove them for a cleaner chart when there are data labels, or add minor gridlines for a chart people read values from:

Python
from openpyxl.chart.axis import ChartLines

clean = line_chart("Weekly visits — no gridlines")
clean.y_axis.majorGridlines = None
ws.add_chart(clean, "E20")

fine = line_chart("Weekly visits — minor gridlines")
fine.y_axis.majorUnit = 10000
fine.y_axis.minorUnit = 2500
fine.y_axis.minorGridlines = ChartLines()
ws.add_chart(fine, "E38")
print(clean.y_axis.majorGridlines, type(fine.y_axis.minorGridlines).__name__)

Setting majorGridlines to None removes them; assigning a ChartLines() object adds them, on either axis — x_axis.majorGridlines draws vertical lines at each category. Minor gridlines follow minorUnit.

Step 4 — A logarithmic scale for fast growth

Visits grow by 35% a week, so on a linear axis the early weeks are squashed against the bottom and the last few dominate. A logarithmic axis gives equal steps to equal ratios, and steady percentage growth becomes a straight line:

Python
log_chart = line_chart("Weekly visits — log scale")
log_chart.y_axis.scaling.logBase = 10
log_chart.y_axis.number_format = '#,##0,"k"'
log_chart.y_axis.title = "Visits (log scale)"
ws.add_chart(log_chart, "E56")
growth = [round(ws.cell(row=r + 1, column=2).value / ws.cell(row=r, column=2).value, 3) for r in range(2, 13)]
print("week-on-week ratio:", min(growth), "to", max(growth))

The ratio between consecutive weeks is constant at 1.35, so the line is straight on the log axis — a slowdown would show as the line bending down, which is invisible on the linear chart until it is dramatic. Label the axis as logarithmic: readers assume linear unless told otherwise. A log axis cannot show zero or negative values.

The same growth on a linear and a logarithmic axis Two panels. On a linear axis, visits growing 35 percent a week look flat for weeks and then shoot up, and a slowdown only shows once it is severe. On a logarithmic axis the same growth is a straight line, so any change in the growth rate shows immediately as a bend. Linear axis early weeks squashed flat curve hides the growth rate familiar to every reader Log axis (logBase 10) steady growth is a straight line slowdowns show as a bend label it as a log scale

Step 5 — Rotate, thin out and move category labels

Twelve labels such as "Week 01" overlap in a narrow chart. Rotating them 45 degrees usually fixes it; showing every second label is an alternative. Rotation is set through the axis's text properties, with the angle in 60,000ths of a degree:

Python
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import CharacterProperties, Paragraph, ParagraphProperties, RichTextProperties

def rotated_labels(angle_degrees, size_pt=8):
    return RichText(bodyPr=RichTextProperties(rot=int(angle_degrees * 60000), vert="horz"),
                    p=[Paragraph(pPr=ParagraphProperties(defRPr=CharacterProperties(sz=size_pt * 100)),
                                 endParaRPr=CharacterProperties())])

narrow = line_chart("Rotated category labels")
narrow.width = 12
narrow.x_axis.txPr = rotated_labels(-45)
ws.add_chart(narrow, "E74")

sparse = line_chart("Every second label")
sparse.width = 12
sparse.x_axis.tickLblSkip = 2
ws.add_chart(sparse, "E92")
print(narrow.x_axis.txPr.bodyPr.rot, sparse.x_axis.tickLblSkip)

A negative angle tilts the labels upward to the right, the usual choice. The font size in CharacterProperties is in hundredths of a point, so 800 is 8 pt. tickLblSkip = 2 shows every second label while keeping every data point.

For a column chart with negative values, the category labels sit on the zero line by default — in the middle of the negative bars. Move them to the bottom with tickLblPos = "low", and set each series' invertIfNegative explicitly, because openpyxl leaves it unset and some applications then draw negative bars incorrectly (LibreOffice draws them upward, as if they were positive):

Python
margin = BarChart()
margin.title = "Weekly margin"
margin.add_data(Reference(ws, min_col=3, min_row=1, max_row=13), titles_from_data=True)
margin.set_categories(Reference(ws, min_col=1, min_row=2, max_row=13))
margin.x_axis.delete = False
margin.y_axis.delete = False
margin.x_axis.tickLblPos = "low"
margin.x_axis.txPr = rotated_labels(-45)
margin.y_axis.number_format = '0.0"%"'
margin.series[0].invertIfNegative = False
margin.legend = None
ws.add_chart(margin, "E110")
wb.save("chart_axes.xlsx")
print("negative weeks:", sum(1 for r in range(2, 14) if ws.cell(row=r, column=3).value < 0))

The first four weeks are negative, and their bars now hang below the zero line with the week labels clear underneath the plot. The format 0.0"%" appends a percent sign to numbers that are already percentages; use 0.0% instead when the data holds fractions such as 0.05.

Weekly margin, with negative weeks below the axis Bar chart of the weekly margin percentage for twelve weeks, from minus 3.1 in week 1 rising by 0.9 each week to 6.8 in week 12. The first four weeks are negative. Margin % by week W01 -3.1 W02 -2.2 W03 -1.3 W04 -0.4 W05 0.5 W06 1.4 W07 2.3 W08 3.2 W09 4.1 W10 5.0 W11 5.9 W12 6.8

Reversing and crossing

scaling.orientation = "maxMin" reverses an axis — used to put the first category at the top of a horizontal bar chart — and crosses = "max" moves the other axis to the far end, as in Create a Gantt Chart in Excel with Python. A second value axis for a series on a different scale is covered in Add a Combo Chart with a Secondary Axis in openpyxl.

Common pitfalls

  • Axes missing in Excel. Set delete = False on both axes of every chart.
  • Checking only in LibreOffice. It shows axes and draws some defaults differently; open the file in Excel before relying on it.
  • Truncated value axes. Starting a bar chart's axis above zero exaggerates differences.
  • Wrong percent format. 0% multiplies by 100; use 0.0"%" for values already in percent.
  • Font sizes in points. CharacterProperties(sz=...) is in hundredths of a point.
  • Negative bars. Set invertIfNegative = False and move labels with tickLblPos = "low".

Performance notes

Axis settings are a few XML attributes on the chart and have no effect on file size or speed. Build charts through one helper function so the settings every chart needs — visible axes, sizes, fonts — are applied consistently.

Conclusion

Set delete = False on both axes of every openpyxl chart so Excel shows them. Control the range with scaling.min, scaling.max and majorUnit, the labels with number_format, and gridlines with majorGridlines and minorGridlines. Use scaling.logBase for fast growth, txPr rotation or tickLblSkip for crowded categories, and tickLblPos = "low" with invertIfNegative = False for charts with negative values.

Frequently asked questions

Why does my openpyxl chart have no axes in Excel? openpyxl 3.1 does not write the flag that marks an axis as visible, and Excel hides an axis without it. Set chart.x_axis.delete = False and chart.y_axis.delete = False. LibreOffice shows the axes anyway, which is why the problem often goes unnoticed until the file is opened in Excel.

How do I set the minimum and maximum of a chart axis with openpyxl? Use the axis's scaling: chart.y_axis.scaling.min = 0 and chart.y_axis.scaling.max = 100. Set chart.y_axis.majorUnit to control the step between labels and gridlines.

How do I format axis numbers as thousands with openpyxl? Set chart.y_axis.number_format = '#,##0,"k"'. Each comma after the last digit placeholder divides the displayed value by 1,000, and the quoted text is added as a suffix, so 45000 shows as 45k.

How do I rotate the category labels of a chart in openpyxl? Give the category axis a txPr text body whose RichTextProperties have a rot value. The angle is in 60,000ths of a degree, so rot=-2700000 tilts the labels by 45 degrees.

More on charts & images

More on charts & images →