Fixing Python xlsxwriter Chart That Renders Blank After Writing Data

You generate a spreadsheet with xlsxwriter, the data is clearly written to the cells, and you add a chart. Then you open the file and find a blank white rectangle where the chart should be. No error was raised — it just silently produced nothing.

This is one of the most common xlsxwriter frustrations because the library gives you no runtime feedback when a chart series reference is wrong. The file opens, the chart frame is there, and the data is there — they just refuse to connect.

What you'll learn

• Why xlsxwriter charts silently render blank and which root causes are most common
• How chart data range references work internally and where they break
• How to verify your series configuration before you even open the file
• A complete, working chart example you can use as a reference template
• Edge cases involving sheet names, index arithmetic, and write order

Prerequisites

You need xlsxwriter installed (pip install xlsxwriter). The examples below use Python 3.8 or later. No other libraries are required. Basic familiarity with xlsxwriter's Workbook and add_worksheet() pattern is assumed.

How xlsxwriter charts actually reference data

Before debugging, it helps to understand the mechanism. An xlsxwriter chart does not hold data directly. It holds string references that Excel uses at open time to locate the data. When you call chart.add_series(), you are writing a formula like =Sheet1!$A$2:$A$10 into the chart XML.

Excel resolves that reference when the file loads. If the reference is malformed, points to a non-existent sheet, or the index arithmetic produces the wrong cell address, Excel quietly renders nothing. xlsxwriter itself never validates these references against the actual data you wrote — that validation happens only inside Excel's engine, after the file is already on disk.

This is why the bug is silent: you can pass a completely wrong range string and xlsxwriter will happily write it to the file without complaint.

Root cause 1: The series data range points to the wrong sheet

The most frequent culprit is a mismatch between the sheet name you used in add_series and the actual worksheet name in the workbook. xlsxwriter worksheets default to Sheet1, Sheet2, etc., but the moment you pass a custom name to add_worksheet(), the default name is gone.

# BAD — worksheet is named 'Sales' but the series references 'Sheet1'
worksheet = workbook.add_worksheet('Sales')
worksheet.write_column('A1', ['Jan', 'Feb', 'Mar'])
worksheet.write_column('B1', [100, 150, 120])

chart.add_series({
    'categories': '=Sheet1!$A$1:$A$3',   # wrong sheet name
    'values':     '=Sheet1!$B$1:$B$3',   # wrong sheet name
})

# GOOD — sheet name matches exactly
chart.add_series({
    'categories': '=Sales!$A$1:$A$3',
    'values':     '=Sales!$B$1:$B$3',
})

If you want to avoid hard-coding the name, capture the worksheet name from the object itself:

sheet_name = worksheet.get_name()
chart.add_series({
    'categories': [sheet_name, 0, 0, 2, 0],
    'values':     [sheet_name, 0, 1, 2, 1],
})

The list form — [sheet_name, first_row, first_col, last_row, last_col] — is often safer than string formulas because Python assembles the reference programmatically, and you can verify each index with a print() before writing the file.

Root cause 2: The workbook was not closed before opening the file

xlsxwriter writes the actual Excel file only when workbook.close() is called. Until then, the file on disk is either non-existent or an incomplete, corrupt ZIP archive that Excel will try to repair — and during repair it typically discards chart data.

# BAD — file opened before workbook.close() is called
workbook = xlsxwriter.Workbook('report.xlsx')
worksheet = workbook.add_worksheet()
# ... add data and chart ...
worksheet.insert_chart('D2', chart)
# Missing workbook.close() — or it comes AFTER you've already opened the file

# GOOD — always close before touching the file
workbook.close()  # flush everything to disk
# Now open report.xlsx

If you use a context manager the close is automatic and this class of bug disappears entirely:

import xlsxwriter

with xlsxwriter.Workbook('report.xlsx') as workbook:
    worksheet = workbook.add_worksheet('Sales')
    # write data, create chart, insert chart
    # close() is called automatically on __exit__

Root cause 3: The chart was never inserted into a worksheet

Creating a chart object and calling add_series() is not enough. You must explicitly insert the chart into a worksheet with worksheet.insert_chart(). If you skip that step, the chart object exists in memory but never makes it into the file.

# BAD — chart configured but never inserted
chart = workbook.add_chart({'type': 'column'})
chart.add_series({'values': '=Sales!$B$1:$B$3'})
# workbook.close() — the chart will not appear anywhere

# GOOD
worksheet.insert_chart('D2', chart)

It is worth scanning your code for every add_chart() call and confirming there is a matching insert_chart() before close().

Root cause 4: Row and column indices are off by one

xlsxwriter uses zero-based row and column indices in its list-form references. Row 0 is the first row of the sheet (what Excel calls row 1). If your data starts at Excel row 2 (because row 1 is a header) and you write first_row=1 but accidentally use first_row=0, your series will reference the header cell, Excel will find a text value where it expected a number, and the chart will render blank.

# Data layout:
# Row 0 (Excel row 1): headers  — 'Month', 'Revenue'
# Row 1 (Excel row 2): Jan, 100
# Row 2 (Excel row 3): Feb, 150
# Row 3 (Excel row 4): Mar, 120

# BAD — starts at row 0 which is the header
chart.add_series({
    'categories': [sheet_name, 0, 0, 3, 0],
    'values':     [sheet_name, 0, 1, 3, 1],
})

# GOOD — starts at row 1, skipping the header
chart.add_series({
    'categories': [sheet_name, 1, 0, 3, 0],
    'values':     [sheet_name, 1, 1, 3, 1],
})

Print your computed row/column values before passing them to add_series. One line of debugging here saves a lot of head-scratching later.

Root cause 5: Writing data after adding the chart series

xlsxwriter does not care about the order in which you write cells versus configure charts — it streams everything to XML when close() is called. However, a common pattern that breaks things is computing the row range before all the data is written, so the last_row value is smaller than it should be.

# BAD — range computed before the loop finishes
rows = []
chart.add_series({
    'values': [sheet_name, 1, 1, len(rows), 1],  # len(rows) is 0 here!
})
for i, row in enumerate(data):
    rows.append(row)
    worksheet.write(i + 1, 1, row['revenue'])

# GOOD — write all data first, then configure the chart
for i, row in enumerate(data):
    worksheet.write(i + 1, 0, row['month'])
    worksheet.write(i + 1, 1, row['revenue'])

last_row = len(data)  # correct after loop
chart.add_series({
    'categories': [sheet_name, 1, 0, last_row, 0],
    'values':     [sheet_name, 1, 1, last_row, 1],
})

Root cause 6: The worksheet name contains special characters or spaces

When you use the string formula form of a series reference and the sheet name contains spaces or special characters, Excel requires the name to be wrapped in single quotes. xlsxwriter does not automatically add those quotes in the string formula form.

# Sheet named 'Q1 Sales' — has a space

# BAD — Excel cannot parse this reference
chart.add_series({
    'values': '=Q1 Sales!$B$2:$B$10',
})

# GOOD — single quotes around sheet name
chart.add_series({
    'values': "='Q1 Sales'!$B$2:$B$10",
})

The safest fix is to avoid spaces and special characters in sheet names altogether, or to always use the list form of add_series which handles quoting automatically.

If you are dealing with other xlsxwriter quirks, the same quoting issue can surface in conditional formatting — see how xlsxwriter silently skips conditional formatting on filtered rows for a related discussion of how the library handles sheet-scoped rules.

Putting it all together: a working chart example

Here is a complete, minimal script that creates a column chart with a named worksheet, proper index arithmetic, and a context manager to guarantee the close. Use this as your baseline when something is not working.

import xlsxwriter

data = [
    ('Jan', 100),
    ('Feb', 150),
    ('Mar', 120),
    ('Apr', 180),
    ('May', 160),
]

with xlsxwriter.Workbook('sales_chart.xlsx') as workbook:
    worksheet = workbook.add_worksheet('Sales')  # explicit name
    sheet_name = worksheet.get_name()            # 'Sales'

    # Write headers
    worksheet.write(0, 0, 'Month')
    worksheet.write(0, 1, 'Revenue')

    # Write data rows
    for row_idx, (month, revenue) in enumerate(data, start=1):
        worksheet.write(row_idx, 0, month)
        worksheet.write(row_idx, 1, revenue)

    last_row = len(data)  # 5 — rows 1 through 5 (zero-indexed)

    # Create and configure chart
    chart = workbook.add_chart({'type': 'column'})
    chart.add_series({
        'name':       f'={sheet_name}!$B$1',
        'categories': [sheet_name, 1, 0, last_row, 0],
        'values':     [sheet_name, 1, 1, last_row, 1],
    })
    chart.set_title({'name': 'Monthly Revenue'})
    chart.set_x_axis({'name': 'Month'})
    chart.set_y_axis({'name': 'Revenue ($)'})

    # Insert chart into the worksheet
    worksheet.insert_chart('D2', chart, {'x_scale': 1.5, 'y_scale': 1.5})

# File is closed and fully written here
print('Done. Open sales_chart.xlsx to verify.')

Every decision above maps directly to one of the root causes covered earlier: explicit sheet name captured via get_name(), data written before the chart is configured, last_row computed after the loop, list-form series reference, and the context manager ensuring close() runs.

If you are writing to an existing file alongside other libraries, be aware that xlsxwriter's handling of merged cells and cell formats has similar silent-failure patterns worth knowing.

Common pitfalls to watch for

• Multiple charts, same sheet object: Each chart must be inserted with a unique cell anchor. Two charts inserted at 'D2' will overlap — only one will display cleanly.
• Empty data range: If your data list is empty, last_row will equal 0 and your range will be [sheet, 1, 0, 0, 0] — a backwards range. Guard against this with a check before writing the chart.
• Mixing write methods: write_column() and write_row() are convenient but make it easier to lose track of which row your data actually ends on. Calculate last_row explicitly rather than inferring it.
• Re-using a chart object: Once you call insert_chart(), the chart object belongs to that worksheet. Do not insert the same chart object into a second worksheet — create a new chart object instead.
• Chart type mismatch: Passing numerical data as categories (x-axis labels) sometimes causes Excel to treat the entire series as a label, rendering no bars. Ensure your categories column contains strings or dates, not numbers, unless you specifically want a scatter/numeric axis.

For problems that arise when the source data comes from Pandas, check the guide on fixing Pandas DataFrames that export the wrong column order to Excel — column order issues upstream can shift the indices you pass to add_series.

If you are building a pipeline that reads Excel files with Pandas and then writes charts back out, also review how Pandas read_excel skips rows when the header is not on row one — a header offset there will cascade into wrong row indices in your chart configuration.

Wrapping up

A blank xlsxwriter chart almost always comes down to one of six issues: wrong sheet name in the series reference, workbook not closed before opening the file, chart never inserted into a worksheet, off-by-one row or column indices, the data range calculated before data is written, or an unquoted sheet name containing spaces. Here are the concrete steps to take right now:

1. Switch to the list form of add_series and use worksheet.get_name() to build the reference — this eliminates sheet-name typos and quoting problems in one move.
2. Always write all your data to the worksheet before computing the series range and calling add_series.
3. Wrap your workbook in a with statement so close() is never accidentally skipped.
4. Print your computed first_row, last_row, and column indices just before passing them to add_series, then cross-check against what you see in the spreadsheet.
5. If the chart is still blank after all of the above, open the file, right-click the chart, and select Select Data — Excel will show you exactly which range it resolved, making it easy to spot the discrepancy.