Formatting & presenting reports
Cell styling & layout
Fonts, fills, borders, widths, frozen panes, merged titles, hyperlinks and print setup.
Start with the full guides
The long-form treatment of cell styling & layout, with the reasoning the shorter pages assume.
- OverviewFormatting and Charting Excel Reports with PythonTurn plain pandas output into branded, stakeholder-ready Excel reports: cell styling, number and date formats, charts, and embedded logos with openpyxl.
- GuideStyling Excel Cells with openpyxlStyle Excel cells from Python with openpyxl: Font, PatternFill, Border, Alignment, header rows in a loop, merged titles, and reusable NamedStyles — all runnable.
- GuideBuilding Excel Reports with xlsxwriterThe write-only engine that formats fastest: reusable format objects, column-level formatting, charts, conditional formats, panes and constant-memory mode — and exactly when openpyxl is still the right choice.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- 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-toAdd Alternating Row Colors with openpyxlBand the rows of an Excel report so long lists are easy to follow — with static fills, a MOD(ROW()) conditional format that survives sorting, a SUBTOTAL-based rule that keeps alternating when rows are filtered, table stripes, and banding by group instead of by row.
- How-toAdd Borders to a Range with openpyxlopenpyxl sets borders per cell, and every assignment replaces the whole border. Build helpers that draw a light grid, an outline around a table, a heavy line under the header and a double line above totals — without wiping edges set earlier — and handle merged cells and immutable style objects correctly.
- How-toAdd Hyperlinks to Excel Cells with PythonAttach web, sheet, email and file links with openpyxl and xlsxwriter — including the style line that makes them look clickable, and when the HYPERLINK formula is the better choice.
- How-toApply a Reusable Style Theme Across an Excel ReportStop scattering fonts and fills through your script — define a theme once with NamedStyle, apply it by role, and keep every generated report visually consistent.
- How-toApply Built-In Cell Styles Like Good, Bad and NeutralUse Excel's own built-in cell styles from Python with openpyxl — Good, Bad and Neutral, Title and the headline styles, Input, Calculation, Total, Note and the accent styles: the exact names openpyxl expects, applying them by value, what happens when you add direct formatting on top, how they appear in Excel's Cell Styles gallery, and when conditional formatting is the better tool.
- How-toApply Gradient and Pattern Fills with openpyxlFill cells with solid colours, hatching patterns and two-colour gradients from Python: which colour a solid PatternFill really uses (and why a bgColor-only fill turns black), the pattern types and their foreground/background colours, linear and path GradientFill, fills in conditional formats, removing fills, and a rendered check of what each one looks like.
- 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-toHide Gridlines and Set the Zoom Level with openpyxlControl how a generated workbook looks the moment it opens: hide gridlines and headings, set the zoom, choose the selected cell and the first visible row, open in Page Layout view, hide zeros, set right-to-left sheets, and avoid the grouped-sheets trap when changing the active sheet.
- How-toIndent Cells to Show a Hierarchy with openpyxlLay out a chart of accounts, an organisation or a product tree the way people read hierarchies: children indented under their parent, parents in bold with rolled-up totals, and rows grouped so each branch can be collapsed. Work out each row's depth from a parent–child table, set Alignment(indent=...), roll values up the tree and add outline levels with openpyxl.
- How-toInsert Page Breaks Between Groups with openpyxlMake each region, customer or department start on a new printed page: find where each group ends, add manual row breaks with openpyxl, repeat the header on every page, add column breaks for wide sheets, clear old breaks, do the same with XlsxWriter, and prove the result by rendering a PDF and checking which group starts each page.
- How-toMerge Cells and Centre a Report Title with openpyxlAdd a merged, centred title banner to a generated report — merge_cells, styling the anchor cell, the MergedCell read-only trap, and why centre-across-selection is often better.
- How-toRotate Text in Excel Cells with openpyxlAngle header labels so a wide table of months, products or dates fits on screen, turn a side label vertical beside a block of rows, or stack letters top to bottom. Set textRotation with openpyxl — and understand its odd 0–180 and 255 values — size the header row so rotated text is not clipped, and do the same in xlsxwriter.
- How-toSet Column Width and Row Height in openpyxlSet Excel column widths and row heights from Python with openpyxl, build an auto-fit pass by measuring content, hide columns, and set sensible defaults — runnable.
- How-toSet the Print Area and Page Setup with openpyxlMake a generated workbook print properly: print area, landscape orientation, fit to width, repeated header rows, margins, headers and footers, and page breaks.
- How-toStyle Whole Columns and Rows Efficiently with openpyxlFormat entire columns and rows in large openpyxl workbooks without slowing the script down: what column and row styles actually do (and why they do not restyle cells that already hold data), reusing one Font object instead of creating thousands, named styles for the fastest path, styling only the used range, and checking the rendered result and the file's style count.
- How-toWrap Text and Align Cells with openpyxlMake long text readable in a generated Excel report — wrap text inside cells, set row heights that fit the wrapped lines, align text to the top so multi-line rows read cleanly, indent hierarchies, rotate narrow headers, centre a title across columns without merging and shrink text as a last resort.
- How-toWrite Rich Text in a Cell with openpyxlPut more than one format in a single cell — a red, bold OVERDUE inside a status line, a highlighted keyword in a comment, H₂O and m² with real subscripts and superscripts. Build CellRichText from TextBlock and InlineFont objects, generate it from data, read existing rich text back, and avoid the load-and-save that silently flattens it.
- How-toCopy Cell Styles from One Cell to Another with openpyxlCopy fonts, fills, borders, alignment and number formats between cells with openpyxl — within a sheet, across sheets and between workbooks — plus column widths, row heights and whole-row formatting for new data.
- How-toAuto-Fit Column Widths When Writing with pandasStop Excel reports opening with ##### and truncated headers — measure content width in Python and set column widths with xlsxwriter or openpyxl, including a reusable helper.
- How-toExport a Styled pandas DataFrame to ExcelUse pandas Styler to colour, bold and highlight a DataFrame, then write it to Excel with the styles intact — and learn which Styler features survive the trip, including why format() does not and what to use instead.
- How-toGenerate a Staff Rota in Excel with PythonBuild a weekly shift rota automatically from staff availability and cover requirements: model it as a small optimisation with SciPy's milp solver, enforce one shift a day, contracted maximums, no late-then-early turnarounds and fair weekends, respect shift preferences, then write a colour-coded Excel rota with live coverage checks and verify every rule.
- How-toAdd a Table of Contents Sheet with Hyperlinks in ExcelGive a multi-sheet workbook a front door — an index sheet with internal hyperlinks, row counts and back-links, generated from whatever sheets the workbook happens to contain.
- How-toFit an Excel Sheet to One Page Wide When Exporting to PDFStop wide Excel reports from spilling columns onto extra PDF pages — set fit-to-width with openpyxl, predict how small the text will get before converting, and when it gets too small, use landscape, narrower margins, hidden helper columns or a two-page-wide layout with repeated labels. Every layout measured in a LibreOffice PDF export.
- 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-toChoose and Customise Excel Table Styles with PythonPick the right look for an Excel table from Python: the 60 built-in styles and how their names work, the four banding and emphasis switches, styles in openpyxl and XlsxWriter, a custom company table style written with openpyxl, why typos in style names pass silently, and why LibreOffice and PDF conversions show tables unstyled unless you add direct formatting.
- How-toBuild a Calendar Sheet in Excel with PythonGenerate a printable month-grid calendar in a workbook: the grid from Python's calendar module, weekends shaded, public holidays marked, events from a spreadsheet list written into their day cells, ISO week numbers down the side, twelve months on one sheet or one per tab, and a layout that prints on one page.
- How-toFreeze the Header Row in Excel with openpyxlFreeze the header row in Excel from Python with openpyxl using ws.freeze_panes. Freeze rows, columns, or both, understand the anchor cell, and unfreeze — runnable.
- How-toFormat Cells in a Live Workbook with xlwingsStyle a workbook that is open in Excel from Python: fill colours, fonts, number formats and column widths through xlwings' own properties, borders and conditional formats through the .api escape hatch, an Excel table with a built-in style, and the screen-updating and range-at-once habits that keep formatting thousands of cells fast — plus how this differs from formatting a file with openpyxl.
- How-toClear Cell Contents and Formatting with openpyxlReproduce Excel's Clear menu in Python: clear values but keep formatting, reset formatting but keep values, remove comments and hyperlinks, or clear everything. Covers clearing a template's input area while keeping its formulas, and why merged cells make a simple clearing loop fail.
- How-toMerge Repeated Group Labels into Single Cells with openpyxlTurn a report where the region name repeats on every row into one merged, vertically centred label per group: find runs of equal values, merge them with openpyxl, handle nested groups so an inner label never spans two outer groups, keep borders tidy, and know when to hide repeats with conditional formatting instead so the sheet stays sortable.
- How-toUse Theme Colours and Tints in openpyxlColour Excel cells with the workbook theme instead of fixed RGB values using openpyxl: theme colour indexes and the swapped order of the first four, tints that lighten and darken like Excel's palette, resolving theme colours to hex for checks and HTML, and rebranding a whole report by changing the theme's accent colours — verified against a rendered copy.
- How-toWrite Clickable Links from a DataFrame Column to ExcelTurn a column of URLs into clickable links when writing a DataFrame to Excel: rely on xlsxwriter's automatic URL detection, write HYPERLINK formulas with friendly text, or set cell.hyperlink with openpyxl. Covers link text, internal sheet links, URL length limits and formula injection.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
Follow it through a track
Prefer an ordered route? These tracks cover cell styling & layout 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.
Cell styling & layout is part of Formatting & presenting reports.