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

Plot a Chart from Non-Adjacent Columns

Chart only some of a sheet's columns — dates from column A, actual from D and forecast from G — without copying data around: add each series from its own Reference with openpyxl or its own add_series call in XlsxWriter, take series names from the header cells, find columns by heading instead of letter, keep a chart working when columns in between are hidden, and check the series references in the chart XML.
Charts & imagesopenpyxl

Report sheets are rarely laid out for charting. The sheet has a date column, then units, revenue, cost, margin, forecast, budget and variance, and the chart needs only three of them: the date along the axis, and actual revenue and forecast as two lines. In Excel you would hold Ctrl and select the columns. In Python the obvious approach — one Reference covering a block of columns — takes everything in between too. The fix is to build the chart one series at a time, each from its own column. This page does that with openpyxl and with XlsxWriter, takes each series' name from its header cell, finds the columns by their headings so the code survives a new column being inserted, deals with hidden columns between the ones being charted, and checks the result by reading the series references out of the chart. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

Bash
pip install openpyxl xlsxwriter pandas

The workbook has one sheet with eight columns: Month (A), Units (B), Revenue (C), Cost (D), Margin (E), Forecast (F), Budget (G) and Variance (H), for twelve months. The chart should show revenue and forecast by month, plus budget as a third line — columns A, C, F and G.

Step 1 — One series per column with openpyxl

Python
from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference, Series

wb = load_workbook("monthly_report.xlsx")
ws = wb.active
last = ws.max_row

chart = LineChart()
for col in (3, 6, 7):                                       # Revenue, Forecast, Budget
    values = Reference(ws, min_col=col, min_row=1, max_row=last)
    chart.series.append(Series(values, title_from_data=True))
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=last))
chart.title = "Revenue against forecast and budget"
chart.y_axis.numFmt = "£#,##0"
chart.x_axis.number_format = "mmm yy"
chart.x_axis.delete = chart.y_axis.delete = False
chart.width, chart.height = 18, 9
ws.add_chart(chart, "J2")
wb.save("report_with_chart.xlsx")
print([s.tx.strRef.f for s in chart.series])

Instead of chart.add_data() with a block of columns, each series is created separately from a one-column Reference and appended to chart.series. Starting each reference at the header row and passing title_from_data=True makes the header cell the series name, so the legend reads Revenue, Forecast and Budget and stays right if someone renames a heading in the sheet. The categories — the months — come from column A with set_categories, which applies them to every series already in the chart, so it is called after the series are added. The printed references show each series pointing at its own column header: C1, F1 and G1.

For comparison, chart.add_data(Reference(ws, min_col=3, max_col=7, ...)) would have produced five series — revenue, cost, margin, forecast and budget — because a multi-column reference becomes one series per column. Deleting the unwanted ones afterwards with del chart.series[1:3] works, but it ties the code to the current column order in a way that is easy to break, whereas building only the series you want states the intent directly. The months on the axis are real dates, so the axis number format mmm yy shows them as Oct 25, Nov 25 and so on.

The columns the chart uses Of the eight report columns the chart takes Month from column A as categories, and Revenue, Forecast and Budget from columns C, F and G as three series. Units, Cost, Margin and Variance are left out. A B C D E F G H 1 A Month B Units C Revenue D Cost E Margin F Forecast G Budget H Variance 2 categories — series 1 — — series 2 series 3 —

Step 2 — Find the columns by heading, not by letter

Python
def col_of(ws, heading, row=1):
    for cell in ws[row]:
        if cell.value == heading:
            return cell.column
    raise KeyError(f"no column headed {heading!r}; headings are {[c.value for c in ws[row]]}")

wanted = ["Revenue", "Forecast", "Budget"]
cols = [col_of(ws, h) for h in wanted]
print(dict(zip(wanted, cols)), "| categories in column", col_of(ws, "Month"))

Column numbers written into code break the first time someone inserts a column into the report. Looking each column up by its heading keeps the chart correct whatever the order, and the error message lists the available headings if one is missing, which makes a renamed column obvious at a glance. In practice the list of headings to chart is the only thing that changes from one chart to the next, so it belongs at the top of the script or in a configuration file, as in Keep Excel Report Settings in a Config File.

Step 3 — The same with XlsxWriter

Python
import pandas as pd
import xlsxwriter

df = pd.read_excel("monthly_report.xlsx")
with pd.ExcelWriter("report_xlsxwriter.xlsx", engine="xlsxwriter", datetime_format="mmm yy") as xw:
    df.to_excel(xw, sheet_name="Report", index=False)
    book, sheet = xw.book, xw.sheets["Report"]
    n = len(df)
    line = book.add_chart({"type": "line"})
    for heading in ["Revenue", "Forecast", "Budget"]:
        c = df.columns.get_loc(heading)
        line.add_series({"name": ["Report", 0, c], "categories": ["Report", 1, 0, n, 0], "values": ["Report", 1, c, n, c]})
    line.set_title({"name": "Revenue against forecast and budget"})
    line.set_y_axis({"num_format": "£#,##0"})
    sheet.insert_chart("J2", line)
print("saved")

XlsxWriter has always worked series by series: each add_series call names its own categories and values, so non-adjacent columns need nothing special. Passing row and column numbers as lists — ["Report", 1, c, n, c] — rather than A1 strings fits naturally with pandas, where df.columns.get_loc(heading) gives the zero-based column of each heading directly. The name points at the header cell, so the legend follows the sheet's headings here too, exactly as in the openpyxl version.

Non-adjacent series in each library In openpyxl each series is a Series built from its own one-column Reference with title_from_data, appended to chart.series, with categories set afterwards. In XlsxWriter each add_series call gives its own name, categories and values ranges. openpyxl Series(Reference(one column), title_from_data=True) chart.series.append(...) set_categories after the series XlsxWriter chart.add_series({...}) per column name, categories, values each row/column lists suit pandas

Styling each series for its role

Building series one at a time has a second benefit: each one can be styled for what it represents. Actual revenue is the main story and deserves a solid, heavier line; the forecast is an expectation and reads naturally as a dashed line; the budget is a reference level and can be a thin grey line in the background.

Python
styles = {"Revenue": ("1F5FA8", 28575, "solid"), "Forecast": ("1F5FA8", 19050, "dash"), "Budget": ("7F7F7F", 12700, "solid")}
for series, heading in zip(chart.series, ["Revenue", "Forecast", "Budget"]):
    colour, width, dash = styles[heading]
    series.graphicalProperties.line.solidFill = colour
    series.graphicalProperties.line.width = width
    series.graphicalProperties.line.dashStyle = dash
    series.smooth = False
wb.save("report_with_chart.xlsx")

Line widths are in EMUs — 12,700 per point — so the three lines are 2.25, 1.5 and 1 point wide. Keying the styles by heading rather than by position means the styling follows the data if the list of charted columns changes. The same approach extends to combination charts, where one column is shown as bars and another as a line on the same axes; the series are simply added to two chart objects that are then combined, as described in Add a Combo Chart with a Secondary Axis in openpyxl.

Building a chart from chosen columns The headings to chart are listed, each is looked up to find its column, a one-column series is built from the header row down with the header as its name, each series is styled for its role, and the shared month categories are set last. Headings to chart Revenue, Forecast, Budget Look up columns by heading One series each header row as name Style per role solid, dashed, grey Set categories month column

Step 4 — Hidden columns, and checking the result

Python
ws.column_dimensions["D"].hidden = ws.column_dimensions["E"].hidden = True    # hide Cost and Margin
chart.visible_cells_only = True                                               # Excel's default
wb.save("report_with_chart.xlsx")

import re, zipfile
xml = zipfile.ZipFile("report_with_chart.xlsx").read("xl/charts/chart1.xml").decode()
print(re.findall(r"<(?:c:)?f>([^<]+)</(?:c:)?f>", xml))

Hiding the columns between the charted ones does not affect the chart, because each series refers only to its own column. Hiding a charted column is different: by default Excel plots only visible cells, so a hidden Forecast column would make its line disappear. When a report hides helper columns that a chart needs, set chart.visible_cells_only = False in openpyxl — XlsxWriter's equivalent is chart.show_hidden_data() — so the chart keeps reading them.

Reading the formulas out of the chart XML lists every range the chart uses: the three header cells for the series names, the three value ranges in columns C, F and G, and the month range in column A, once per series. That list is a precise test for a chart built from scattered columns — it fails loudly if a heading lookup ever picks the wrong column.

Common pitfalls

  • One Reference over a block. It charts every column in between; build one series per column.
  • Hard-coded column numbers. Look columns up by heading so inserted columns do not break the chart.
  • Series without names. Start each reference at the header row and use title_from_data=True.
  • Categories set too early. In openpyxl, call set_categories after appending the series.
  • Hidden charted columns. Turn off visible cells only if the chart must read them.

Conclusion

To chart non-adjacent columns, build the chart one series at a time: in openpyxl, append a Series made from a one-column Reference that starts at the header row with title_from_data=True, then set the categories; in XlsxWriter, call add_series once per column. Find the columns by heading rather than letter, keep charted columns visible or switch off visible cells only, and check the series ranges in the chart XML.

Frequently asked questions

How do I chart columns that are not next to each other with openpyxl? Create a Series from a separate one-column Reference for each column and append it to chart.series, then call set_categories.

How do I take a series name from the header cell? Start the Reference at the header row and pass title_from_data=True when creating the Series.

Why does my chart line disappear when I hide its column? Charts plot visible cells only by default; set chart.visible_cells_only = False in openpyxl or chart.show_hidden_data() in XlsxWriter.

How do I chart non-adjacent columns with XlsxWriter? Call add_series once for each column, giving each its own values range and the shared categories range.

More on charts & images

More on charts & images →