Python Excel AutomationPractical guides for developers
Start learning

Guide

DocumentationDeep dive

Formatting and Charting Excel Reports with Python

Turn plain pandas output into branded, stakeholder-ready Excel reports: cell styling, number and date formats, charts, and embedded logos with openpyxl.

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 track is about the last mile — turning 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 pillar links four clusters, each going deep on one piece of the polished-report problem:

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.

LibraryRoleReads existing files?StrengthsLimits
pandas to_excelDump raw tabular dataVia read_excelOne line from DataFrame to sheet; multi-sheet writesNo real styling control beyond a header bold via the engine
openpyxlStyle and edit workbooks cell by cellYes — loads and preserves existing stylesCharts, images, formats, conditional formatting; can re-open a pandas file and decorate itSlower on very large writes
xlsxwriterBuild new styled files in one passNo — write-onlyFastest styled writes; rich chart and format APICannot open or modify an existing file

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:

Bash
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.

Python
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.

Python
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.

Python
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.

Bash
pip install openpyxl pillow
Python
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.

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:

Related tracks: