Python XlsxWriter Formatting Formulas and Multiple Sheets

When generating Excel reports in Python, you often need to combine cell formatting, formulas, and multiple worksheets in a single workbook. XlsxWriter gives you one API for all three, but the way formats, formulas, and worksheet objects interact can be surprising. This article shows how to use python xlsxwriter formatting formulas and multiple sheets together without duplicating logic or losing performance.

Creating a Workbook with Multiple Worksheets

XlsxWriter creates a workbook with one worksheet by default. To add more, call add_worksheet() for each sheet you need. Each call returns a new worksheet object that you can write to independently.

import xlsxwriter

workbook = xlsxwriter.Workbook("report.xlsx")
sheet_sales = workbook.add_worksheet("Sales")
sheet_expenses = workbook.add_worksheet("Expenses")
sheet_summary = workbook.add_worksheet("Summary")

workbook.close()

The worksheet name appears on the tab in Excel. Names are limited to 31 characters and cannot contain certain characters like : or \. If you need to rename a sheet later, you can pass the name as the first argument to add_worksheet(), as shown above.

You can also control the order of sheets by creating them in the order you want them to appear. If you need to move a sheet after creation, there is no direct API for reordering, so plan the order before writing data.

Applying Cell Formatting with Format Objects

Formatting in XlsxWriter is done through format objects created from the workbook. A format object defines the visual style for a cell or range: font, background, borders, number format, alignment, and more. You create a format with workbook.add_format() and then pass it to write() or write_blank().

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

Once you have a format, you can apply it to a cell when writing a value:

sheet_sales.write("A1", "Product", header_format)
sheet_sales.write("B1", "Revenue", header_format)

Formats are reusable across all worksheets in the workbook. Creating one format and using it on multiple sheets avoids redundant objects and keeps the code consistent. If you need a slightly different style, create a new format rather than modifying an existing one, because formats are immutable after they are added to a worksheet.

Writing Formulas and Handling Their Results

XlsxWriter writes formulas as strings that Excel evaluates when the file is opened. Use write_formula() to write a formula to a cell. The formula must start with = and use Excel syntax.

sheet_sales.write_formula("B10", "=SUM(B2:B9)")

The cell will display the calculated result in Excel, but XlsxWriter does not compute the value itself. If you need the result to be visible in a tool that does not evaluate formulas, you can optionally pass a cached value as the third argument:

sheet_sales.write_formula("B10", "=SUM(B2:B9)", 12345)

The cached value is only used by non-Excel applications; Excel recalculates on open. Use this sparingly because hardcoded values can become stale.

Formulas can reference cells across worksheets. Use the sheet name followed by an exclamation mark: =Sales!B2. If the sheet name contains spaces, wrap it in single quotes: ='North Region'!B2.

Combining Formatting with Formulas in the Same Cell

You can pass a format object to write_formula() just like you do with write(). This is how you apply formatting to a cell that contains a formula.

total_format = workbook.add_format({
    "bold": True,
    "num_format": "$#,##0.00",
    "top": 1,
})

sheet_sales.write_formula("B10", "=SUM(B2:B9)", total_format, 12345)

The fourth argument is the cached value. If you do not need it, pass None or omit it. The format controls the appearance of the cell, including number formatting. Without a number format, the formula result will display as a raw number, which may not match the rest of your report.

You can also apply a format to a range of cells that contain formulas by writing each cell individually or by using write_formula() in a loop. XlsxWriter does not have a bulk formula writer, so you need to iterate over rows and columns when you have many formula cells.

Managing Worksheet Order and Naming

Worksheet names are visible in the tab bar and in formulas. Choose names that are short, unique, and free of special characters. If you create a sheet with a name that already exists, XlsxWriter raises an exception, so keep a list of used names if you generate sheets dynamically.

You can also set the active sheet and the first visible sheet using worksheet.activate() and workbook.set_first_sheet(). This controls which sheet is selected when the file opens.

sheet_summary.activate()

If you have many sheets, consider using worksheet.hide() to hide intermediate sheets that the user does not need to see directly. Hidden sheets can still be referenced by formulas, which is useful for keeping a clean interface.

Performance and Memory Considerations for Large Workbooks

XlsxWriter writes files directly to disk and does not keep the entire workbook in memory, which makes it suitable for large datasets. However, creating many format objects or writing cell-by-cell in Python loops can slow down generation.

To keep performance reasonable:

• Reuse format objects across cells and sheets instead of creating a new format for every cell.
• Use write_row() and write_column() to write lists of data in one call.
• Avoid calling write() for every cell in a tight loop if you can batch the data.

Formulas are stored as strings, so a workbook with thousands of formulas will generate a larger file. Excel recalculates them on open, so there is no runtime cost in Python, but the file size and open time can increase.

If you need to write a very large number of formulas, consider whether you can compute the values in Python and write them as static values instead. This trades the ability to update in Excel for faster file generation and smaller output.

Common Pitfalls When Mixing Formats, Formulas, and Sheets

One common mistake is reusing a format object after changing its properties. XlsxWriter formats are immutable once used, so you cannot modify them later. Create a new format for each distinct style.

Another issue is referencing a sheet name incorrectly in a formula. If the sheet name contains spaces or starts with a digit, you must wrap it in single quotes. Forgetting the quotes produces a broken formula in Excel.

When you write a formula to a cell that already has a value, XlsxWriter overwrites the cell. There is no warning, so make sure your cell coordinates do not overlap.

Finally, remember that write_formula() does not validate the formula syntax. A typo will produce a file that opens with an error in Excel. Test the formulas by opening the generated file or by using a library that evaluates formulas if you need automated verification.

The combination of formatting, formulas, and multiple sheets is straightforward once you understand how formats are shared and how formulas reference other sheets. Keeping format objects centralized and naming sheets predictably will make your report generation code easier to maintain as the number of sheets grows.