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

Show Negative Bars in a Different Colour

Colour the negative bars of a variance or profit chart red and the positive ones green, from Python: split the values into two helper series that update with the data, colour individual data points when the values are fixed, or use XlsxWriter's invert-if-negative colour for Excel 2010 and later — with what each approach writes into the file, how to check it, and why openpyxl's invertIfNegative on its own turns negative bars white.
Charts & imagesopenpyxl

A chart of variances, monthly profit or year-on-year change mixes positive and negative values, and the reader's first question is which bars are which. Colouring the negative bars red and the positive ones green answers it before they read a single axis label. Excel has an option for this — Invert if negative — but it has a history: the original version simply turns negative bars white, and choosing the colour of the inverted bars was added later through an extension that not every library or application writes. From Python there are three reliable ways to get two-colour bars, each with a different trade-off. This page builds all three for the same eight months of budget variance, shows what each writes into the chart XML, and explains which to choose. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

Bash
pip install openpyxl xlsxwriter

The data is eight months of variance against budget, in thousands of pounds: five months over budget, three under. The first two methods use openpyxl; the third uses XlsxWriter.

Method 1 — Two helper series that follow the data

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

variance = [12, -8, 5, -15, 20, 7, -3, 9]
wb = Workbook()
ws = wb.active
ws.title = "Variance"
ws.append(["Month", "Variance", "Above budget", "Below budget"])
for i, v in enumerate(variance, start=2):
    ws.append([f"2026-{i - 1:02d}", v, f"=IF(B{i}>=0,B{i},NA())", f"=IF(B{i}<0,B{i},NA())"])

chart = BarChart()
chart.type = "col"
chart.grouping = "clustered"
chart.overlap = 100                                   # both series in the same slot
chart.add_data(Reference(ws, min_col=3, max_col=4, min_row=1, max_row=len(variance) + 1), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=len(variance) + 1))
above, below = chart.series
above.graphicalProperties.solidFill = "2E7D32"
below.graphicalProperties.solidFill = "C62828"
chart.title = "Variance to budget (£k)"
chart.x_axis.delete = chart.y_axis.delete = False
ws.add_chart(chart, "F2")
wb.save("variance_split.xlsx")
print("saved")

The most portable method avoids the invert option altogether. Two helper columns split the variance: one holds the value when it is zero or positive, the other when it is negative, and each leaves #N/A in the months that belong to the other. The chart plots the two helper columns as two series, each with its own fixed colour, and overlap = 100 puts them in the same slot on the axis, so each month shows one bar in the right colour. #N/A is used rather than zero because charts skip it entirely, while a zero would still create an empty bar position and, with data labels on, a misleading 0 label. Because the helper columns are formulas, a corrected variance switches colour on its own the next time the workbook calculates. The two series also give the chart a legend that explains the colours — Above budget and Below budget — for free.

The helper columns Each month's variance appears in the Above budget column when it is zero or positive and in the Below budget column when it is negative; the other column holds N/A, which the chart skips. A B C D 1 Month Variance Above budget Below budget 2 2026-01 12 12 #N/A 3 2026-02 −8 #N/A −8 4 2026-03 5 5 #N/A 5 2026-04 −15 #N/A −15

Method 2 — Colour individual data points

Python
from openpyxl.chart.marker import DataPoint

chart2 = BarChart()
chart2.type = "col"
chart2.add_data(Reference(ws, min_col=2, min_row=1, max_row=len(variance) + 1), titles_from_data=True)
chart2.set_categories(Reference(ws, min_col=1, min_row=2, max_row=len(variance) + 1))
series = chart2.series[0]
series.graphicalProperties.solidFill = "2E7D32"
for idx, v in enumerate(variance):
    if v < 0:
        point = DataPoint(idx=idx)
        point.graphicalProperties.solidFill = "C62828"
        series.dPt.append(point)
chart2.title = "Variance to budget (£k) — point colours"
chart2.legend = None
chart2.x_axis.delete = chart2.y_axis.delete = False
ws.add_chart(chart2, "F20")
wb.save("variance_split.xlsx")
print("red points:", [p.idx for p in series.dPt])

A chart series can override the formatting of single points. Here the series is green, and each point whose value is negative gets a DataPoint with a red fill, identified by its zero-based position in the series. This keeps the chart to one series and needs no helper columns, which is tidy when the chart is produced fresh every time from data Python already has. The catch is that the colours are fixed when the file is written: if someone corrects a value in the workbook and it changes sign, the bar keeps its old colour. Use point colours for generated, read-only reports, and the helper-series method for workbooks people edit.

Method 3 — Invert if negative, with a colour, in XlsxWriter

Python
import xlsxwriter

xw = xlsxwriter.Workbook("variance_invert.xlsx")
sh = xw.add_worksheet("Variance")
sh.write_column("A1", ["Month"] + [f"2026-{m:02d}" for m in range(1, 9)])
sh.write_column("B1", ["Variance"] + variance)
ch = xw.add_chart({"type": "column"})
ch.add_series({"name": "=Variance!$B$1", "categories": "=Variance!$A$2:$A$9", "values": "=Variance!$B$2:$B$9",
               "fill": {"color": "#2E7D32"}, "invert_if_negative": True, "invert_if_negative_color": "#C62828"})
ch.set_title({"name": "Variance to budget (£k) — invert if negative"})
ch.set_legend({"none": True})
sh.insert_chart("D2", ch)
xw.close()
print("saved")

Excel's own option is Invert if negative on the series. On its own it does something surprising: negative bars lose their fill and become white with an outline, which on a white background looks like the bar is missing. Choosing the colour of the inverted bars came with Excel 2010 as an extension element in the chart XML. XlsxWriter writes both parts when given invert_if_negative and invert_if_negative_color, so Excel shows green bars above zero and red ones below, and the colours follow the data as it changes. openpyxl exposes only the original flag, series.invertIfNegative = True, and does not write the colour extension, which is why setting that flag alone in openpyxl produces white negative bars in Excel. Applications that do not read the extension fall back to their own behaviour, so for files that will also be opened outside Excel, the helper-series method is the safe choice.

Three ways to colour negative bars Helper series work everywhere and follow the data but need two extra columns. Point colours need no helper columns but are fixed when the file is written. XlsxWriter's invert-if-negative colour follows the data in one series but relies on an Excel 2010 extension. Two helper series works in every application colours follow the data legend explains the colours two extra columns Point colours one series, no helpers colours fixed at write time good for generated reports Invert if negative + colour one series, follows the data XlsxWriter only Excel 2010+ extension What invert if negative writes into the chart The series has a normal green fill and the invertIfNegative flag. On its own the flag makes Excel draw negative bars without fill. The Excel 2010 extension invertSolidFillFmt adds the red fill used for negative bars, and XlsxWriter writes it when given invert_if_negative_color. Series fill green for every bar invertIfNegative = 1 negative bars lose their fill invertSolidFillFmt extension red fill for negative bars

Choosing the colours

Red and green are the conventional pair for bad and good, but about one man in twelve has some form of red–green colour blindness and sees the two as similar shades. For charts that will be widely read, a blue and orange pair, or green and red at clearly different lightness, keeps the distinction visible to everyone. The bars' position above and below zero already carries the meaning, so the colour only has to reinforce it — and a clear zero line, with the category labels moved to the bottom of the chart so they do not sit among the negative bars, helps as much as the colour does.

Check what was written

Python
import re, zipfile

with zipfile.ZipFile("variance_split.xlsx") as z:
    c1 = z.read("xl/charts/chart1.xml").decode()
    c2 = z.read("xl/charts/chart2.xml").decode()
with zipfile.ZipFile("variance_invert.xlsx") as z:
    c3 = z.read("xl/charts/chart1.xml").decode()
print("helper series:", len(re.findall(r"<c:ser>|<ser>", c1)), "| overlap:", re.search(r'overlap val="(-?\d+)"', c1).group(1))
print("data points coloured:", len(re.findall(r"<c:dPt>|<dPt>", c2)))
print("invert flag:", 'invertIfNegative val="1"' in c3, "| colour extension:", "invertSolidFillFmt" in c3)

Chart options leave no trace in openpyxl or XlsxWriter once the file is saved, so the reliable test is to read the chart XML inside the .xlsx. The helper-series chart has two series with an overlap of 100; the point-colour chart has one dPt element for each of the three negative months; and the XlsxWriter chart has both the invert flag and the invertSolidFillFmt extension that carries the red colour. A test that checks these strings catches the most common regression — a refactor that drops the colour extension and leaves Excel showing white bars.

A visual check is still worth doing in Excel itself when a report is first set up. Different spreadsheet applications render charts slightly differently, and the chart XML is the contract: if the elements above are present, Excel draws the bars as intended.

Common pitfalls

  • invertIfNegative alone. Without a colour extension, Excel draws negative bars white.
  • Zeros in the helper columns. Use NA() so the chart skips the month entirely.
  • Forgetting overlap = 100. The two series sit side by side, each half width.
  • Point colours on editable workbooks. They do not change when a value changes sign.
  • No legend for the colours. Helper series name themselves; otherwise add a note.

Conclusion

To show negative bars in a different colour, split the values into two helper series — above and below zero, with NA() for the other case — and plot them overlapped at 100 with fixed colours; it works everywhere and follows the data. For generated reports, colouring individual points with DataPoint is simpler. In XlsxWriter, invert_if_negative with invert_if_negative_color does it in one series for Excel 2010 and later. Check the chart XML to make sure the option was written.

Frequently asked questions

How do I make negative bars red in an Excel chart from Python? Plot two helper series, one for positive and one for negative values, with overlap 100 and a colour each; or colour negative points individually; or use XlsxWriter's invert_if_negative_color.

Why are my negative bars white after setting invertIfNegative in openpyxl? Excel's original invert option removes the fill; the colour for inverted bars is an Excel 2010 extension that openpyxl does not write.

Why use NA() instead of 0 in the helper columns? Charts skip #N/A, while zero still creates a bar position and a zero label.

Do point colours update when the data changes? No; they are fixed per point when the file is written, so a value that changes sign keeps its old colour.

More on charts & images

More on charts & images →