Formatting and Charting Excel Reports with Python

df.to_excel("report.xlsx") produces a workbook that is correct and ugly: black Calibri on white, raw 0.4815 instead of 48.15%, dates rendered as five-digit serial numbers, and columns too narrow to read. Nobody trusts a report that looks like a database dump. This is the report-polishing leg of automating Excel with Python — the last mile that turns that plain grid into something a finance director will open, scan, and act on without complaining.

Everything here is for working Python developers who already write DataFrames to Excel and now need the output to look deliberate: a styled header row, money formatted as money, a chart that summarizes the table above it, and the company logo in the top-left corner. Almost all of it is plain openpyxl, with a note on where xlsxwriter is the faster tool. Every code block below builds its own tiny workbook and runs top to bottom, so you can paste and execute without any data of your own.

What you will learn

This guide links four focused guides, each going deep on one piece of the polished-report problem:

• Styling Excel Cells with openpyxl — fonts, fills, borders, alignment, column widths, and freeze panes.
• Applying Number and Date Formats in Excel — currency, percentage, thousands separators, and date format codes.
• Creating Charts in Excel with openpyxl — bar, line, and pie charts driven by worksheet ranges.
• Inserting Images and Logos into Excel — embedding logos and images for branded output.

The four runnable sections below are a tour of all four at once: style a header, format money and dates, add a bar chart, and drop in a logo.

The styling library landscape

Three libraries write .xlsx files, but they make different trade-offs around editing existing files versus building new ones fast. Picking the wrong one is the most common reason a styling script becomes painful.

The decision is mechanical:

• Already have a file (a pandas export, a template, last month's report)? Use openpyxl — it is the only one of the three that can open a workbook, keep its existing formatting, and add to it.
• Generating a fresh report from scratch and want maximum speed on a big sheet? Use xlsxwriter, either directly or as the pandas engine: df.to_excel("out.xlsx", engine="xlsxwriter").
• Just need the data on a sheet with no styling? Plain pandas.to_excel is fine.

The realistic pattern for a weekly report is pandas to write the data, then openpyxl to dress it: pandas turns your DataFrame into rows, you re-open the file with load_workbook, and you apply styles, formats, charts, and a logo. That two-step flow is what most of this track teaches, and it builds on Using openpyxl for Excel File Manipulation and Writing DataFrames to Excel with Pandas.

Install both engines so every example below runs:

pip install pandas openpyxl

Style a header row and autosize columns

The single biggest visual upgrade is a styled header: bold white text on a colored fill, centered, with a thin border under it, and columns wide enough to read. Here we write a small regional sales table with pandas, then re-open it with openpyxl to apply Font, PatternFill, Alignment, and Border, and fit each column to its widest value.

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

# 1. pandas writes the raw data
sales = pd.DataFrame({
    "Region": ["North", "South", "East", "West"],
    "Orders": [128, 94, 156, 73],
    "Revenue": [25640.50, 18890.00, 31200.75, 14005.25],
})
sales.to_excel("sales_report.xlsx", sheet_name="Sales", index=False)

# 2. openpyxl re-opens it and styles the header
wb = load_workbook("sales_report.xlsx")
ws = wb["Sales"]

header_font = Font(bold=True, color="FFFFFF", size=12)
header_fill = PatternFill("solid", fgColor="305496")
center = Alignment(horizontal="center", vertical="center")
thin_bottom = Border(bottom=Side(style="thin", color="1F3864"))

for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center
    cell.border = thin_bottom

# Autosize: widen each column to its longest value
for column_cells in ws.columns:
    longest = max(len(str(c.value)) for c in column_cells if c.value is not None)
    ws.column_dimensions[column_cells[0].column_letter].width = longest + 3

ws.freeze_panes = "A2"  # keep the header visible while scrolling
wb.save("sales_report.xlsx")
print("Styled header and fitted", ws.max_column, "columns")

Excel has no "autofit" you can call from a file writer — the fitted width comes from measuring the text yourself, which is exactly what the loop does. The full set of styling primitives lives in Styling Excel Cells with openpyxl.

Apply currency and date number formats

A number format is a display rule: it changes how a value looks, not the value itself, so 1234.5 stays a number you can sum while showing as $1,234.50. You set it with cell.number_format using Excel's format codes. Here we add an order date and a price to a tiny table, then format the date as dd-mmm-yyyy and the price as currency.

import datetime as dt
from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active
ws.title = "Orders"

ws.append(["Order", "Order Date", "Amount"])
for cell in ws[1]:
    cell.font = Font(bold=True)

rows = [
    ("A-1001", dt.date(2026, 6, 1), 1234.50),
    ("A-1002", dt.date(2026, 6, 3),  879.00),
    ("A-1003", dt.date(2026, 6, 4), 2410.75),
]
for order, order_date, amount in rows:
    ws.append([order, order_date, amount])

# Format the date column (B) and currency column (C)
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
    row[1].number_format = "dd-mmm-yyyy"          # 01-Jun-2026
    row[2].number_format = '"$"#,##0.00'          # $1,234.50

wb.save("orders_formatted.xlsx")
total = sum(amount for _, _, amount in rows)
print(f"Wrote 3 orders, total still summable: ${total:,.2f}")

The amounts remain real numbers — Excel can total column C even though it displays dollar signs. For percentages, thousands separators, and locale-aware currency codes, see Applying Number and Date Formats in Excel.

Add a bar chart from worksheet data

A chart in openpyxl points at ranges already on the sheet — you give it a Reference to the data and to the category labels, and Excel renders it live, so the chart updates if the cells change. Here we write four regions of revenue and anchor a BarChart next to the table.

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
ws.title = "Revenue"

ws.append(["Region", "Revenue"])
for region, revenue in [("North", 25640), ("South", 18890),
                        ("East", 31200), ("West", 14005)]:
    ws.append([region, revenue])

chart = BarChart()
chart.type = "col"
chart.title = "Revenue by Region"
chart.y_axis.title = "Revenue ($)"
chart.x_axis.title = "Region"

data = Reference(ws, min_col=2, min_row=1, max_row=5)   # include header for series name
cats = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

ws.add_chart(chart, "D2")   # top-left corner of the chart
wb.save("revenue_chart.xlsx")
print("Embedded a", chart.type, "chart anchored at D2")

The chart is a native Excel object, not a pasted image, so users can restyle it in Excel and it redraws if the data changes. Line and pie variants follow the same Reference pattern in Creating Charts in Excel with openpyxl.

Insert a logo image

Branding a report usually means a logo in the top-left corner. openpyxl embeds raster images (PNG, JPEG) through openpyxl.drawing.image.Image, anchored to a cell. This needs Pillow, which openpyxl uses to read the image — the example generates a tiny PNG inline so it runs with nothing to download.

pip install openpyxl pillow

from openpyxl import Workbook
from openpyxl.drawing.image import Image as XLImage
from PIL import Image as PILImage

# Build a small placeholder logo so the example is self-contained
PILImage.new("RGB", (120, 40), color="#305496").save("logo.png")

wb = Workbook()
ws = wb.active
ws.title = "Report"

# Reserve space for the logo, then start the title below it
ws.row_dimensions[1].height = 34
ws["A3"] = "Weekly Sales Report"

logo = XLImage("logo.png")
logo.width, logo.height = 120, 40   # in pixels
ws.add_image(logo, "A1")            # anchor top-left

wb.save("branded_report.xlsx")
print("Embedded logo.png at cell A1")

The image is copied into the workbook, so the saved .xlsx is self-contained — you can email it and the logo travels with it. See Inserting Images and Logos into Excel for sizing, aspect ratio, and headers/footers.

Assemble the full report

Each technique above stands alone, but a real weekly report layers all four onto one sheet in a fixed order: pandas writes the data, then openpyxl styles the header, formats the numbers, fits the columns, adds a chart, and finally drops the logo and title into a few rows reserved at the top. The order matters — every openpyxl step must run after the pandas write, because to_excel rewrites plain cells and would wipe any styling applied before it.

The trick that makes it fit together is startrow: telling pandas to begin the table a few rows down leaves an empty band at the top for branding, and everything else is addressed relative to that header row.

import datetime as dt
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, Reference
from openpyxl.drawing.image import Image as XLImage
from PIL import Image as PILImage

# 1. pandas writes the raw table, leaving three rows at the top for branding
sales = pd.DataFrame({
    "Region": ["North", "South", "East", "West"],
    "Orders": [128, 94, 156, 73],
    "Revenue": [25640.50, 18890.00, 31200.75, 14005.25],
    "Updated": [dt.date(2026, 6, 12)] * 4,
})
sales.to_excel("weekly_report.xlsx", sheet_name="Sales", index=False, startrow=3)

# 2. re-open with openpyxl to dress it
wb = load_workbook("weekly_report.xlsx")
ws = wb["Sales"]
HEADER_ROW = 4                       # pandas put the header on Excel row 4

# House style, defined once and reused
header_font = Font(bold=True, color="FFFFFF", size=12)
header_fill = PatternFill("solid", fgColor="305496")
center = Alignment(horizontal="center", vertical="center")
thin_bottom = Border(bottom=Side(style="thin", color="1F3864"))

for cell in ws[HEADER_ROW]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center
    cell.border = thin_bottom

# 3. number and date formats on the body rows (Revenue = C, Updated = D)
for row in ws.iter_rows(min_row=HEADER_ROW + 1, max_row=ws.max_row):
    row[2].number_format = '"$"#,##0.00'
    row[3].number_format = "dd-mmm-yyyy"

# 4. fit each column to its widest value
for column_cells in ws.columns:
    longest = max((len(str(c.value)) for c in column_cells if c.value is not None), default=0)
    ws.column_dimensions[column_cells[0].column_letter].width = longest + 3

# 5. a live bar chart beside the table
chart = BarChart()
chart.type = "col"
chart.title = "Revenue by Region"
data = Reference(ws, min_col=3, min_row=HEADER_ROW, max_row=ws.max_row)   # include header
cats = Reference(ws, min_col=1, min_row=HEADER_ROW + 1, max_row=ws.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "F4")

# 6. logo and title into the reserved top rows
PILImage.new("RGB", (120, 40), color="#305496").save("logo.png")
logo = XLImage("logo.png")
logo.width, logo.height = 120, 40
ws.add_image(logo, "A1")
ws["A3"] = "Weekly Sales Report"
ws["A3"].font = Font(bold=True, size=14)

ws.freeze_panes = f"A{HEADER_ROW + 1}"   # keep branding + header visible
wb.save("weekly_report.xlsx")
print("Assembled report:", ws.max_column, "cols,", ws.max_row - HEADER_ROW, "data rows")

Define the house style once — the Font, PatternFill, Alignment, and Border objects at the top — and reuse it across every report so the whole set looks like it came from the same team. When one combination of font, fill, and border repeats often, promote it to a NamedStyle, covered in Styling Excel Cells with openpyxl. Once this assembled report is reliable, the natural next move is to run it unattended and send it out, which is exactly what Automating Reporting Workflows and Building Multi-Sheet Excel Dashboards cover.

Pitfalls and safe in-place edits

The formatting step edits a file that already holds data, so a handful of ordering and object rules decide whether your polish survives:

• Style last, always. Any pandas.to_excel write emits plain cells with default formatting. If you style a sheet and then let pandas rewrite it, the styling is gone. Re-open with load_workbook after every pandas write and apply styles as the final step.
• Style objects are immutable. You cannot flip one attribute — cell.font.bold = True silently does nothing on a shared object. Build a fresh Font/PatternFill/Border/Alignment and assign it, carrying over old values with Font(name=cell.font.name, bold=True) when needed.
• A number format never changes the value. '"$"#,##0.00' is display only, so the cell stays a real number Excel can sum. But writing "$1,234.50" as a string does break the total — keep amounts numeric and let the format do the presentation. See Applying Number and Date Formats in Excel for the format codes.
• Merged cells keep only the top-left value. Merging a range clears every other cell, so set the value and styling on the top-left cell before or after merging deliberately, not in a loop over the whole range.
• Charts reference live ranges, not snapshots. If you delete or reorder the rows a Reference points at, the chart follows — anchor charts and images outside the data band (as the assembled example does) so later edits do not disturb them.
• load_workbook preserves what it reads. Loading an existing workbook keeps its fonts, fills, formats, charts, and images intact, so you can decorate last month's template without rebuilding it — the safe in-place edit that makes the pandas-then-openpyxl flow work.

For rule-driven formatting that reacts to the data itself — red fills on negative variance, data bars on stock levels — reach for Applying Conditional Formatting with openpyxl rather than hard-coding colors row by row.

Make the sheet explorable, not just pretty

Formatting decides how a report looks; filters and tables decide what a reader can do with it. On a sheet of more than a few dozen rows the second matters more, and it is two lines:

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = load_workbook("report.xlsx")
ws = wb["Orders"]

ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
table = Table(displayName="Orders", ref=ref)
table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True)
ws.add_table(table)          # filter dropdowns and banded rows in one object
ws.freeze_panes = "A2"       # the header stays put while the reader scrolls

wb.save("report.xlsx")

A table brings banded rows, its own filter and a name that formulas can use — =SUM(Orders[Revenue]) keeps working as the data grows, where =SUM(F2:F241) does not. The price is a stricter range: headers must be unique and non-empty, and the reference must cover exactly the header plus the data. Where a sheet has subtotal rows between groups or two blocks stacked on one tab, a plain ws.auto_filter.ref is the better fit.

Two habits make either version reliable. Derive the range from ws.max_row rather than from a constant, so it stays correct as the report grows. And apply the table or filter after pandas has written the sheet, because to_excel replaces the sheet wholesale and takes any decoration with it. Creating Excel tables and autofilters with Python covers grouping, protection and the total row that follows a filter.

Formatting has a performance budget

Styling is not free on the reader's side. Excel stores a style record per distinct combination of font, fill, border and number format, and a workbook that constructs a new Font object per cell accumulates thousands of them — which is why two files with identical row counts can differ tenfold in how long they take to open.

The same reasoning applies to formulas. A handful of summary formulas keeps a report interactive; fifty thousand SUMIFS calls make it crawl on every edit. Compute the bulk figures in pandas, write values, and reserve live formulas for the cells a reader might genuinely want to change.

A finishing checklist

Before a formatted report leaves your machine, five checks catch nearly every presentation complaint:

• Column widths wide enough that no figure shows as ####, with an allowance for the filter arrow.
• Number formats applied per column — currency, percentage and dates — rather than left as General.
• A frozen header, so column names survive scrolling.
• The right sheet active, so the workbook opens on the summary rather than on raw detail.
• No stray gridline-only rows or columns beyond the data, which inflate ws.max_row and confuse filters.

None of these is difficult, and all five are the kind of thing that is noticed only when missing. Wrapping them in one finalise(ws) function that every report calls is the simplest way to make the whole set habitual rather than remembered.

Design for the reader's screen

A report is read on a laptop, often projected, and occasionally printed. Three settings decide whether that experience is comfortable, and all three are one line each:

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb["Summary"]

ws.sheet_view.zoomScale = 100                  # not whatever the last author used
ws.page_setup.orientation = "landscape"        # wide tables print sideways
ws.page_setup.fitToWidth = 1                   # one page across, any number down
ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.print_title_rows = "1:1"                    # repeat the header on every printed page

wb.save("report.xlsx")

print_title_rows is the one people miss, and it is the difference between a printed report whose second page is a wall of anonymous numbers and one that reads correctly on paper. None of these settings affects the data — they are pure presentation — but each removes a small friction that readers otherwise blame on the report itself.

Frequently asked questions

Does styling survive pandas.to_excel? Only minimally. to_excel writes data and a basic bold header; it gives you no handle on fonts, fills, borders, number formats, charts, or images. The standard approach is to write the data with pandas, then re-open the file with openpyxl.load_workbook and apply everything else. openpyxl preserves any styling already in the file when it loads.

openpyxl or xlsxwriter for charts and styling? Use openpyxl when you need to open and modify an existing file — it is the only one that can read a workbook and keep its formatting. Use xlsxwriter when you are generating a brand-new file from scratch and want the fastest styled write; it has a rich chart and format API but is strictly write-only, so it cannot touch a file that already exists.

How do I autosize columns? There is no file-level "autofit." Measure the longest string in each column yourself and set ws.column_dimensions[letter].width to that length plus a small pad (the header-row example above does this). Width units are roughly the count of default-font characters, not pixels.

Do I need Excel installed to format and chart? No. openpyxl and xlsxwriter are pure Python and write the .xlsx file format directly, so they run on a headless Linux server or CI runner with no Excel anywhere. You only need Excel (via xlwings) when a live application must run macros or recalculate — which is not the case for formatting, number formats, charts, or images.

Will a number format break my totals? No. A number format only changes how a value is displayed. '"$"#,##0.00' makes 1234.5 show as $1,234.50, but Excel still stores a number and sums it normally. Formatting a real datetime.date with a date code is likewise display-only.

Key takeaways

• pandas writes correct but unstyled sheets; the polish comes from re-opening the file with openpyxl.
• Use openpyxl to edit existing files (it preserves styles), xlsxwriter for fast new-file builds, plain pandas for raw data.
• Header styling and fitted column widths are the cheapest, highest-impact upgrades.
• Number formats are display-only — your values stay summable and your dates stay real dates.
• Charts reference live worksheet ranges; logos embed into the file and travel with it.
• None of this needs Excel installed, so it runs unattended on a server.

Where to go next

Go deep on each piece of the polished report:

• Styling Excel Cells with openpyxl — fonts, fills, borders, alignment, column widths, and freeze panes.
• Applying Number and Date Formats in Excel — currency, percentage, thousands, and date format codes.
• Creating Charts in Excel with openpyxl — bar, line, and pie charts from worksheet ranges.
• Inserting Images and Logos into Excel — embedding logos and images into branded reports.

Related

• Python Excel Automation — up to the overview that ties reading, cleaning, formatting, and reporting together.
• Getting Started with Python Excel Automation — the read - transform - write foundation these examples build on.
• Automating Reporting Workflows — once a report looks right, put it on a schedule and email it, or export it to PDF.
• Advanced Data Transformation and Cleaning — clean and validate the data before you format it, including Applying Conditional Formatting with openpyxl for rule-based styling that reacts to the data.