Libraries, speed & errors
xlsxwriter
Fast, richly formatted new workbooks with xlsxwriter.
Start with the full guides
The long-form treatment of xlsxwriter, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toCombine Chart Types and Chartsheets with XlsxWriterBuild combination charts and full-page chartsheets with XlsxWriter: columns and a line in one chart with the line on a secondary axis, a stacked column chart with a total line, charts fed from a pandas summary written to a data sheet, chartsheets that show one chart per tab, and checks of the generated chart XML so a broken chart is caught before anyone opens the file.
- How-toDefine Names and Protect Sheets with XlsxWriterBuild an input template with XlsxWriter in which formulas use defined names instead of cell addresses and only the input cells can be edited: workbook and sheet-scoped names for ranges and constants, a dropdown fed by a named list on a hidden sheet, unlocked input cells, hidden formulas, protect() options that still allow sorting and filtering, and a check of the result with openpyxl and LibreOffice.
- How-toFreeze Panes and Set Zoom with XlsxWriterControl how each sheet of an XlsxWriter report opens: freeze the header row and the first column, split panes instead of freezing, set the zoom per sheet, hide gridlines, choose which sheet and cell are active, scroll the view to a starting cell, colour the tabs, and check every view setting by reading the file back with openpyxl.
- How-toInsert Checkboxes in Excel with xlsxwriterTurn a column of TRUE/FALSE values into clickable checkboxes — a month-end close checklist, a review sign-off column, a to-do list. Use xlsxwriter's insert_checkbox, count and percentage-complete formulas that show the right value before recalculation, strike through finished rows with conditional formatting, read the ticks back, and fall back gracefully where checkboxes are not supported.
- How-toSet Print Options, Headers and Footers with XlsxWriterMake an XlsxWriter report print properly: landscape A4, fit to one page wide, repeat the header row on every page, set the print area and margins, add page breaks between groups, and write headers and footers with the report name, page X of Y, the print date and a logo — then check the printed result by exporting it to PDF.
- How-toWrite an Excel Table with Formula Columns Using XlsxWriterWrite a pandas DataFrame as a real Excel table with XlsxWriter, including calculated columns that use structured references, a totals row, column formats and a table style: get the range right, choose between the [@Column] shorthand and fully qualified references that also work outside Excel, add custom totals, and verify the formulas by recalculating.
- How-toWrite Dates, Times and Durations with XlsxWriterWrite dates, times and durations to Excel correctly with XlsxWriter: why a date written without a format appears as 46297, setting a default date format, handling the timezone error by converting before stripping, durations over 24 hours with [h]:mm, times of day, dates before 1900, and the same settings through pandas' ExcelWriter.
- How-toAdd a Chart to an Excel File with xlsxwriterInsert a real Excel chart from Python: series built from row counts, axis and label formatting, a combined column-and-line chart on a secondary axis, and placement that survives changing data.
- How-toAdd Sparklines to an Excel Report with xlsxwriterPut a trend in every row: xlsxwriter sparklines — line, column and win/loss types, high and low markers, shared axes across rows, styling, and the openpyxl caveat.
- How-toAdd Text Boxes and Shapes to Excel with XlsxWriterFloat commentary, notes and labels over an Excel report with XlsxWriter insert_textbox: size, fill, border and font options, multi-line and rotated text, gradient fills and hyperlinks, text linked to a cell (and why it shows empty in LibreOffice), placing a box below a table of any length, and when a formatted cell is the better choice.
- How-toApply Conditional Formatting with xlsxwriterWrite rules Excel re-evaluates: cell and text criteria, colour scales and data bars, a formula rule that highlights whole rows, and the absolute-reference trap that makes a row rule follow the wrong column.
- How-toBuild a Waterfall Chart in Excel with PythonShow how an opening figure becomes a closing one — revenue bridges, budget walks, cash movements — as a waterfall chart built with xlsxwriter. Uses the stacked-column technique with an invisible base, formula-driven helper columns that update when inputs change, green and red steps, total bars and value labels.
- How-toLabel Chart Points from Cell Values with XlsxWriterPut product names, regions or comments on chart points instead of bare numbers: XlsxWriter custom data labels that reference cells (shown by Excel, ignored by LibreOffice), a portable one-series-per-point technique whose series-name labels render everywhere, labelling only the points that matter, labels from a different column on a column chart, and a test that the labels are really drawn.
- How-toStore Formula Results with xlsxwriter So Every Viewer Shows Valuesxlsxwriter writes formulas with a cached result of 0, so pandas, openpyxl, previewers and even LibreOffice's command-line conversion show zeros until Excel recalculates. Compute the results in Python and store them with the formula, keep them consistent, and stop text that starts with "=" from becoming a formula.
- How-toWrite a Formatted Excel Report with xlsxwriterBuild a complete styled report in one pass: a title block, a header row you control, column formats and widths, a totals row, banded rows, an autofilter and frozen panes.
- How-toWrite a Million Rows to Excel with xlsxwriterConstant memory mode explained: what it costs, why rows must be written in order, where the temporary files go, how to split past Excel's 1,048,576-row limit, and when a CSV is the honest answer.
- How-toWrite Rich Text with Mixed Formats in One Cell with xlsxwriterPut bold labels, coloured status words, superscripts and line breaks inside a single Excel cell with xlsxwriter's write_rich_string — the argument order, the silent return codes when a string is rejected, combining with pandas output, and keeping rich text intact when openpyxl edits the file later.
- How-toEmbed Images Inside Cells with xlsxwriterPut a product photo or status icon inside a cell rather than floating over the grid, so it sorts, filters and resizes with its row. Use xlsxwriter's embed_image, and know how it differs from insert_image.
- How-toChange the Default Font of an Excel Workbook with PythonMake a generated workbook use your house font everywhere — not just in the cells you styled: set XlsxWriter's default_format_properties (also through pandas), understand why changing the Normal style in openpyxl does nothing to existing cells, use a template so openpyxl inherits the right default, keep bold and coloured cells in the same family, and verify the fonts that actually render.
- How-toAdd a VBA Macro to a Workbook with xlsxwriterShip a macro-enabled .xlsm report from Python: extract a vbaProject.bin once from a workbook saved in Excel, attach it to every new file with xlsxwriter, wire a button to a macro, keep the workbook and sheet code names consistent, and check the result without opening Excel.
- How-toSet Workbook Properties Like Author and Title with PythonGive generated workbooks a proper title, author, company, keywords and custom properties such as the data source or row count. See what openpyxl and xlsxwriter can set, which fields each one overwrites on save, and how to read the properties back for auditing.
- How-toWrite Dynamic Array Formulas like FILTER and UNIQUE from PythonWrite spilling Excel formulas — FILTER, UNIQUE, SORT, SEQUENCE, XLOOKUP — from Python so they work when the file opens. Why openpyxl output shows #NAME? or an @, what the _xlfn prefix and array metadata are, and how xlsxwriter handles both.
Compare the options
Which tool or approach to pick, and why.
- ComparisonBuild a Budget vs Actual Variance Report in Excel with PythonTurn monthly actuals and a budget into the variance report finance reviews every month: month and year-to-date side by side, revenue and cost sections with subtotals and a net line, variances as live formulas where positive means favourable, unfavourable lines in red and a comment column for the reviewer.
- Comparisonopenpyxl vs xlsxwriter vs pandas.ExcelWriterUnderstand how pandas.ExcelWriter wraps the openpyxl and xlsxwriter engines, when each one wins, and write the same DataFrame three ways with a clear decision table.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
- FixFix "Filesize would require ZIP64 extensions" (LargeZipFile) in XlsxWriterVery large exports fail at close() with xlsxwriter.exceptions.FileSizeError: Filesize would require ZIP64 extensions, raised from zipfile.LargeZipFile. Learn what crosses the limit (one XML part over 2 GiB, not the .xlsx file size), estimate when your export will hit it, turn on use_zip64 in XlsxWriter or through pandas, and when to split the data or choose another format instead.
- FixFix xlsxwriter "Exception caught in workbook destructor"The xlsxwriter message "Exception caught in workbook destructor. Explicit close() may be required" comes from versions before 1.1, where an unclosed workbook was saved when garbage collected. Current versions print nothing and write nothing instead. Close every workbook explicitly, handle FileCreateError, and avoid writing half-built reports from a with block.
- FixFix XlsxWriter Overwriting an Existing WorkbookYou pointed XlsxWriter — or pandas with its default engine — at an existing workbook to add a sheet, and every other sheet vanished. XlsxWriter only creates new files and never reads old ones. See why, what "Append mode is not supported with xlsxwriter!" means, how to add or replace a sheet with openpyxl's append mode and if_sheet_exists, and how to guard against overwriting a file by accident.
Follow it through a track
Prefer an ordered route? These tracks cover xlsxwriter along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
xlsxwriter is part of Libraries, speed & errors.