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

Style Chart Colours and Data Labels with openpyxl

Replace Excel's default chart styling from Python — series fill and outline colours, one highlighted bar, dashed target lines, subtle gridlines, bold coloured data labels, label number formats that survive every viewer, and a single palette function so every chart in a report matches.
Charts & imagesopenpyxl

Charts generated by openpyxl start with Excel's default look: the theme's first colours in order, heavy gridlines and no labels. That is fine for a quick check and wrong for a report, where colours should mean something — the company palette, red for a missed month, a dashed line for the target — and the numbers should be readable without hovering. openpyxl exposes nearly all of Excel's chart formatting through a few property objects. This guide, part of Creating Charts in Excel with openpyxl, styles a revenue-versus-target chart and checks every setting by rendering the workbook.

Revenue against a dashed target line Six monthly revenue bars in a light brand colour, with March, the one month well below target, highlighted in red. A dashed teal line shows the target of 90K rising to 95K from April. Each bar carries a bold label in thousands. Revenue (K) Target (K) 0 50 100 150 Jan Feb Mar Apr May Jun 82 91.5 78.25 104.3 99.8 112.4 90 90 90 95 95 95

Prerequisites

Bash
pip install openpyxl

Monthly revenue and a target, with a column chart for revenue and a line for the target:

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

wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Month", "Revenue", "Target"])
for row in [("Jan", 82000, 90000), ("Feb", 91500, 90000), ("Mar", 78250, 90000),
            ("Apr", 104300, 95000), ("May", 99800, 95000), ("Jun", 112400, 95000)]:
    ws.append(row)

chart = BarChart()
chart.x_axis.delete = False  # Excel hides axes unless this is set
chart.y_axis.delete = False
chart.type = "col"
chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=7), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=7))
target = LineChart()
target.x_axis.delete = False
target.y_axis.delete = False
target.add_data(Reference(ws, min_col=3, min_row=1, max_row=7), titles_from_data=True)

Step 1 — Series fill and outline

Every series has graphicalProperties: solidFill for the area, line for the outline. Colours are hex strings without #:

Python
revenue = chart.series[0]
revenue.graphicalProperties.solidFill = "C7D2FE"          # light indigo fill
revenue.graphicalProperties.line.solidFill = "4338CA"     # darker outline

A light fill with a darker outline of the same hue keeps bars crisp without making them heavy, and leaves room for labels and highlights to stand out. Set both properties: without an explicit outline, some viewers draw the theme's default border around a custom fill.

Step 2 — Highlight one bar

To draw attention to one category — the month that missed its target — override that point only. A DataPoint takes the zero-based index of the category:

Python
from openpyxl.chart.marker import DataPoint

missed = DataPoint(idx=2)                                 # March
missed.graphicalProperties.solidFill = "B91C1C"
missed.graphicalProperties.line.solidFill = "B91C1C"
revenue.dPt.append(missed)

In a generated report, compute the indexes from the data instead of hard-coding them — here, every month where revenue fell more than 5% short of target:

Python
revenue.dPt.clear()
rows = list(ws.iter_rows(min_row=2, max_row=7, values_only=True))
for idx, (_, actual, goal_value) in enumerate(rows):
    if actual < goal_value * 0.95:
        point = DataPoint(idx=idx)
        point.graphicalProperties.solidFill = "B91C1C"
        point.graphicalProperties.line.solidFill = "B91C1C"
        revenue.dPt.append(point)
print([p.idx for p in revenue.dPt])       # [0, 2]

The rule lives in code next to the data, so next month's chart highlights whichever months qualify. With a 5% tolerance both January (82K against 90K, 8.9% short) and March (13% short) are marked; a 10% tolerance would mark only March. Choose the threshold with whoever reads the report, and state it in the chart title or a note. Keep highlighting rare; a chart where half the bars are red highlights nothing, and readers stop looking for the signal.

Step 3 — A dashed target line

Line series use the same line object for the line itself. Width is in EMUs — 12,700 per point — and dashStyle takes Excel's names such as "dash", "sysDot" and "lgDash":

Python
goal = target.series[0]
goal.graphicalProperties.line.solidFill = "0B6157"
goal.graphicalProperties.line.width = 28575                # 2.25 pt
goal.graphicalProperties.line.dashStyle = "dash"
goal.marker.symbol = "none"
goal.smooth = False
chart += target

Turning off markers and smoothing keeps the target a plain reference line that reads as a threshold rather than as data. Combining the bar and line charts with += puts them on the same axes; a second axis is only needed when the units differ, as described in Add a Combo Chart with a Secondary Axis in openpyxl.

Where each visual property lives A series fill is graphicalProperties solidFill, its outline or line graphicalProperties line solidFill, line thickness line width in EMUs and dashes line dashStyle. A single bar is a DataPoint in series dPt. Label text style is the DataLabelList txPr, and gridline colour is the axis majorGridlines spPr. openpyxl attribute Bar or area fill series.graphicalProperties.solidFill Outline or line colour ….line.solidFill Line width and dashes ….line.width / dashStyle One highlighted bar DataPoint(idx) in series.dPt Label font DataLabelList.txPr (RichText) Gridline colour axis.majorGridlines.spPr

Step 4 — Data labels with a readable font

Labels are a DataLabelList on the series. Switch on the value, switch off everything else, choose a position — "outEnd" above a column, "ctr" inside, "t" above a line point — and style the text through txPr:

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

labels = DataLabelList()
labels.showVal = True
labels.position = "outEnd"
for part in ("showSerName", "showCatName", "showLegendKey", "showPercent"):
    setattr(labels, part, False)
bold_dark = CharacterProperties(sz=900, b=True, solidFill="1E293B")       # 9 pt
labels.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=bold_dark), endParaRPr=bold_dark)])
revenue.dLbls = labels

Font size sz is in hundredths of a point, so 900 is 9 pt. The same RichText pattern styles axis labels (chart.x_axis.txPr) and the title. Rendering the chart shows bold labels above each bar; the January label sits close to the target line, which is worth checking on real data — move labels inside the bars ("inEnd") when a reference line runs through the label area.

Step 5 — Label number formats that work everywhere

Revenue of 112,400 reads better as 112K. openpyxl lets you set a number format on the label list, but it writes it without the flag that tells a viewer to prefer it over the source format, and LibreOffice ignored it in our test. The reliable route is simpler: labels show values in the source cells' own number format, so format the cells:

Python
for (cell,) in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    cell.number_format = '#,##0,"K"'
chart.y_axis.number_format = '#,##0,"K"'

Rendered, the labels read 82K, 92K, 78K and so on, and the axis matches. The side effect — the table shows thousands too — is usually what a report wants; if it is not, point the chart at a hidden helper column with the labelled format. Scaling formats themselves are covered in Format Numbers in Thousands and Millions in Excel with Python.

Step 6 — Quieter gridlines and one palette for every chart

Default gridlines compete with the data. Give them a light colour, then collect every colour decision into one function so all charts in the report match:

Python
from openpyxl.chart.shapes import GraphicalProperties
from openpyxl.drawing.line import LineProperties

PALETTE = {"primary": ("C7D2FE", "4338CA"), "alert": "B91C1C", "reference": "0B6157", "grid": "E7EBEF"}

def apply_house_style(ch):
    ch.y_axis.majorGridlines.spPr = GraphicalProperties(ln=LineProperties(solidFill=PALETTE["grid"]))
    ch.legend.position = "b"
    ch.height, ch.width = 8, 15

apply_house_style(chart)
chart.title = "Revenue vs target"
ws.add_chart(chart, "E2")
wb.save("styled.xlsx")
print(chart.series[0].graphicalProperties.solidFill.srgbClr)          # C7D2FE

Once the palette lives in one place, a brand refresh is a one-line change and every chart in every report follows it. Explicit hex colours also make the chart independent of the workbook's theme: unstyled series take the theme's accent colours, which change if someone applies a different theme in Excel, while a series with its own solidFill keeps its colour whatever theme is active. The same idea for number formats and cell styles is covered in Write a Formatted Excel Report with xlsxwriter.

Styling a generated chart in order Set series fills and outlines, override single data points that need attention, style reference lines, add data labels with a readable font, format the source cells for label numbers, and finish with gridlines and layout from one shared house-style function. Series colours fill + outline Highlight points DataPoint Reference lines dash, width Labels txPr font, position House style one function

Common pitfalls

  • Colours with a #. openpyxl expects "4338CA". It accepts "#4338CA" without complaint and writes it into the file, where it is not a valid colour — LibreOffice drew such a bar in black in our test. Strip the hash before assigning.
  • Labels showing everything. Leaving showSerName or showCatName unset can make Excel print "Revenue; Jan; 82000". Switch the unwanted parts off explicitly.
  • Labels on every point of a long series. Twelve or more labels on a line chart collide. Label only the last point, or the highest and lowest, by giving those points their own label through DataLabel(idx=…) in dLbls.dLbl.
  • Too many colours. One neutral colour plus one highlight reads faster than six hues. Reserve colour for meaning.

Conclusion

openpyxl chart styling comes down to a few objects: graphicalProperties for fills, outlines, widths and dashes; DataPoint in series.dPt for single highlighted bars; DataLabelList with a RichText txPr for readable labels; and axis spPr for gridlines. Format label numbers through the source cells so every viewer shows them, and keep all colour decisions in one palette so every chart in the report looks like it belongs.

Frequently asked questions

How do I change the colour of a series in an openpyxl chart? Set series.graphicalProperties.solidFill to a hex colour such as "4338CA" for the fill, and series.graphicalProperties.line.solidFill for the outline or, on a line chart, the line itself.

How do I colour just one bar differently? Create a DataPoint with the bar's zero-based index, give its graphicalProperties a solidFill, and append it to series.dPt. The other bars keep the series colour.

How do I format data label numbers, for example as thousands? The most reliable way is to format the source cells — labels show values in the cells' number format by default. openpyxl can also set a label number format, but it is written without the flag that separates it from the source format, and LibreOffice ignored it in our test.

How do I make data labels bold or coloured? Assign a RichText to the DataLabelList's txPr, containing a paragraph whose default run properties (CharacterProperties) set b=True, sz in hundredths of a point and solidFill for the colour.

More on charts & images

More on charts & images →