Fixing Python xlsxwriter That Ignores Cell Formats When Writing Merged Cells

You call merge_range(), open the file in Excel, and the cells are merged — but the bold font, background fill, or border you set is completely gone. The data is there, the merge is there, but the format is not. This is one of the most common xlsxwriter surprises, and it trips up developers who have formatted regular cells dozens of times without issue.

The fix is straightforward once you understand where xlsxwriter expects the format object to go in a merge operation versus a normal write() call.

What you'll learn

• Why xlsxwriter silently drops formats on merged cells
• The correct signature for merge_range() including the format parameter
• How to build a format object that combines font, fill, alignment, and borders
• How to apply formats when merging in loops without creating redundant objects
• Gotchas that cause formatting to still fail even after you pass the object

Prerequisites

You need xlsxwriter installed (pip install xlsxwriter) and a working Python 3.7+ environment. The examples below use xlsxwriter's core API directly — no Pandas wrapper. If you are driving xlsxwriter through Pandas, the same principles apply but you will need the engine_kwargs or the underlying writer object to access the workbook.

How xlsxwriter handles merged cells internally

In an XLSX file, a merged cell is a single cell that spans a rectangular range. Only the top-left cell in that range actually holds data and formatting; every other cell in the range is empty. xlsxwriter reflects this directly: when you call merge_range(), it writes a value to the top-left cell, blanks out the rest, and records the merge region in the worksheet XML.

The critical detail is that xlsxwriter does not read back any format you previously set on the individual cells in that range. The format must be passed as an argument to merge_range() itself. If you wrote the cell with a format first and then merged over it, the merge call overwrites the cell with no format unless you supply one explicitly.

Why your format disappears

Here is the pattern that catches most people:

import xlsxwriter

workbook = xlsxwriter.Workbook('report.xlsx')
worksheet = workbook.add_worksheet()

bold_format = workbook.add_format({'bold': True, 'bg_color': '#4472C4', 'font_color': '#FFFFFF'})

# This does NOT carry the format into the merge
worksheet.write('A1', 'Header', bold_format)
worksheet.merge_range('A1:D1', 'Header')  # format is now gone

workbook.close()

The second call to merge_range() replaces the content of A1 with the string 'Header' and no format, because you did not pass one. The write() call on the line above it is effectively wasted work. The merge always wins, and it uses whatever format you give it at call time — which is nothing if you omit the argument.

Passing a format object to merge_range()

The correct signature for merge_range() is:

worksheet.merge_range(first_row, first_col, last_row, last_col, data, cell_format)

Or using A1 notation:

worksheet.merge_range('A1:D1', data, cell_format)

The cell_format argument is the last positional argument and it is optional in Python's sense — xlsxwriter will not raise an error if you leave it off, which is exactly why the bug is silent. Here is the corrected version of the earlier example:

import xlsxwriter

workbook = xlsxwriter.Workbook('report.xlsx')
worksheet = workbook.add_worksheet()

header_format = workbook.add_format({
    'bold': True,
    'bg_color': '#4472C4',
    'font_color': '#FFFFFF',
    'align': 'center',
    'valign': 'vcenter',
    'border': 1
})

# Pass the format as the third argument to merge_range
worksheet.merge_range('A1:D1', 'Quarterly Report', header_format)

workbook.close()

Open this file and the merged header will have the blue background, white bold text, center alignment, and a border — exactly what you defined in the format object.

Combining multiple format properties correctly

A common follow-up mistake is trying to combine two format objects. xlsxwriter format objects are not composable after creation — you cannot merge them the way you might merge dictionaries and then pass the result. Each format is a single object registered with the workbook.

Build all properties you need into one dictionary when you call add_format():

combined_format = workbook.add_format({
    'bold': True,
    'italic': False,
    'font_size': 14,
    'font_color': '#FFFFFF',
    'bg_color': '#C00000',
    'align': 'center',
    'valign': 'vcenter',
    'text_wrap': True,
    'border': 2,
    'border_color': '#000000'
})

worksheet.merge_range('B2:F4', 'Critical Warning', combined_format)

If you find yourself building many similar formats, use a helper function that accepts overrides and merges them into a base dictionary before calling add_format():

def make_format(workbook, overrides=None):
    base = {
        'font_name': 'Calibri',
        'font_size': 11,
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    }
    if overrides:
        base.update(overrides)
    return workbook.add_format(base)

header_fmt = make_format(workbook, {'bold': True, 'bg_color': '#4472C4', 'font_color': '#FFFFFF'})
subheader_fmt = make_format(workbook, {'bg_color': '#D9E1F2'})

worksheet.merge_range('A1:D1', 'Sales Data', header_fmt)
worksheet.merge_range('A2:D2', 'Q1 2024', subheader_fmt)

This pattern keeps your format definitions DRY and makes it easy to add new styles without re-typing every base property.

Applying formats to merged cells in loops

Generating merged headers dynamically — for example, one merged cell per data category — is a frequent requirement in report generation. The trap here is creating a new format object inside the loop on every iteration, which inflates the workbook's internal format table. Create formats once, outside the loop.

import xlsxwriter

workbook = xlsxwriter.Workbook('categories.xlsx')
worksheet = workbook.add_worksheet()

# Define formats ONCE outside the loop
category_format = workbook.add_format({
    'bold': True,
    'bg_color': '#70AD47',
    'font_color': '#FFFFFF',
    'align': 'center',
    'border': 1
})

categories = [
    {'label': 'Revenue', 'start_col': 0, 'end_col': 2},
    {'label': 'Costs',   'start_col': 3, 'end_col': 5},
    {'label': 'Margin',  'start_col': 6, 'end_col': 8},
]

for cat in categories:
    worksheet.merge_range(
        0, cat['start_col'],
        0, cat['end_col'],
        cat['label'],
        category_format   # same object reused each time
    )

workbook.close()

xlsxwriter reuses format objects correctly — you do not need a separate object per cell. Only create a new format object when you genuinely need a different visual style.

Common pitfalls with merge_range() formats

Merging a range that was already written to

If you use write() or write_string() on any cell inside a range before calling merge_range(), xlsxwriter will raise a warning and may produce unexpected output. The merge call must be the only write operation for that range. Write to the merged cell exclusively through merge_range().

Forgetting that alignment lives in the format, not the cell

In the Excel UI, center-alignment is a cell property you set after the merge. In xlsxwriter, alignment is part of the format object. If your merged header is not centered, check that you included 'align': 'center' in the format dictionary, not that you are calling a separate alignment method (which does not exist in xlsxwriter).

Row height and text wrapping

A merged cell that spans multiple columns with long text will silently truncate visible content if the row is not tall enough and text wrapping is off. Add 'text_wrap': True to the format and set an explicit row height with worksheet.set_row(row_index, height_in_points) to avoid clipped headers.

Using the wrong data type for the cell value

merge_range() calls write() internally, which means it auto-detects whether the value is a string, number, or URL. If you pass a number where a string is expected, xlsxwriter stores it as a number — which is usually fine, but be aware that empty strings and None behave differently. Pass an explicit empty string '' rather than None if you want a formatted but empty merged cell.

Formats applied via Pandas ExcelWriter

If you are using Pandas to generate the base sheet and then adding merged headers on top, you need to grab the underlying xlsxwriter workbook and worksheet objects from the ExcelWriter context before you can call merge_range(). Applying formats through Pandas to_excel() kwargs alone will not reach merged regions. If you run into column ordering problems in that workflow, the guide on fixing Pandas DataFrames that export the wrong column order to Excel covers the ExcelWriter internals in detail.

For the merge itself, access the writer objects like this:

import pandas as pd
import xlsxwriter

df = pd.DataFrame({'A': [1, 2], 'B': [3, 4], 'C': [5, 6]})

with pd.ExcelWriter('output.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Sheet1', startrow=2, index=False)

    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']

    header_format = workbook.add_format({
        'bold': True, 'bg_color': '#4472C4',
        'font_color': '#FFFFFF', 'align': 'center'
    })
    worksheet.merge_range('A1:C1', 'My Report Header', header_format)

If you are running into a different openpyxl-based workflow where column widths are not sticking, the related problem of openpyxl failing to autofit column widths on save is worth a read for comparison.

Formats that look right in LibreOffice but wrong in Excel (or vice versa)

Some color values and border styles render differently across applications. Stick to standard hex color codes (no named colors) and use xlsxwriter's documented border style integers (1 through 13). If you see inconsistent results, avoid bg_color and fg_color at the same time unless you understand xlsxwriter's fill pattern model — bg_color alone is almost always what you want for a solid background.

Reading back merged cell data with Pandas

Once you write a file with merged cells, reading it back with pd.read_excel() can produce unexpected NaN values in all but the first cell of the merged region. This is not an xlsxwriter bug — it is standard XLSX behavior. If your pipeline reads the file back, unmerge or restructure the data layer separately from the presentation layer. The article on fixing Pandas read_excel that skips rows when the header is not on row one covers other common read-back pitfalls worth reviewing alongside this one.

Wrapping up

The root cause is simple: merge_range() writes its own cell from scratch, so any format you set beforehand is irrelevant. Always pass the format as the final argument to merge_range(), never rely on a prior write() call to carry it over.

Here are the concrete actions to take right now:

• Audit every merge_range() call in your codebase and confirm that a format object is the last argument. If it is missing, that is your bug.
• Build a make_format() helper that merges overrides into a base dictionary so you stop re-typing shared properties on every format.
• Move all format object creation outside loops to keep the workbook format table lean and avoid subtle duplication issues.
• Test in both Excel and LibreOffice Calc if your users span both environments, particularly for custom border styles and fill colors.
• Keep data and presentation layers separate in any pipeline that reads back the file with Pandas — merged cells and DataFrame parsing do not mix cleanly.