Advanced Excel Automation: Add Formulas, Colors and Charts with Python

In our previous post "Excel Automation: Generate Your Monthly Report in Five Minutes with Python", we showed how pandas can sum, group and export a summary. But many readers asked: the numbers are calculated, yet the report still needs formulas, colors and charts before it looks presentable. Can that polish be automated too?

Yes. This time we switch to another Python package called openpyxl, built specifically for styling and laying out Excel files. Install it once, run one script, and it automatically adds formulas, applies colors and draws charts — a polished report in one go.

Step 1: Install openpyxl

Open Command Prompt and run:

pip install openpyxl

That's it — you're ready to code.

Step 2: What are we building?

Imagine a sales record with three columns: Product, Quantity and Unit Price. We want to automatically:

Fill the "Total" column with a formula (Quantity × Unit Price)
Style the header row with a white-on-orange background
Add a "Grand Total" row at the bottom using a SUM formula
Generate a bar chart so you can see which product sells best at a glance

Step 3: The full script

Create a new file called excel_report.py and paste in:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.chart import BarChart, Reference

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

# 1. Header row
ws.append(["Product", "Quantity", "Unit Price", "Total"])
for col in range(1, 5):
    c = ws.cell(row=1, column=col)
    c.font = Font(bold=True, color="FFFFFF")
    c.fill = PatternFill("solid", fgColor="F7941D")

# 2. Data rows
data = [
    ("Coffee Machine", 12, 899),
    ("Thermal Mug", 40, 128),
    ("Portable Fan", 25, 68),
    ("Bluetooth Speaker", 8, 399),
]
for row in data:
    ws.append(row)

# 3. Add formula: Total = Quantity x Unit Price
for r in range(2, 6):
    ws.cell(row=r, column=4).value = f"=B{r}*C{r}"

# 4. Grand total row
ws.append(["Grand Total", None, None, "=SUM(D2:D5)"])
ws.cell(row=6, column=4).font = Font(bold=True)

# 5. Bar chart
chart = BarChart()
chart.add_data(Reference(ws, min_col=4, min_row=1, max_row=5), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=5))
ws.add_chart(chart, "F2")

# 6. Save
wb.save("sales_report.xlsx")
print("Report saved: sales_report.xlsx")

Line by line

Workbook() — creates a new Excel file; wb.active is the first worksheet.
ws.append([...]) — the easiest way to add data row by row.
Font(bold=True, color="FFFFFF") + PatternFill("solid", fgColor="F7941D") — set the font and fill color (orange is our brand color; change it to anything you like).
f"=B{r}*C{r}" — this is the key part: writing a real Excel formula into a cell, so Excel computes it for you instead of you calculating by hand first.
BarChart() — creates a bar chart; add_data points to the values, set_categories sets the X-axis labels.
wb.save(...) — saves the file. Done.

Step 4: Run it

In Command Prompt, run:

python excel_report.py

You now have sales_report.xlsx — complete with formulas, colors and a chart. Next month, just update the data and run the script again for an instantly polished report.

Summary

openpyxl's real strength is handing all the tedious Excel formatting over to Python. Formulas, colors and charts are just the start — you can also auto-hide columns, merge cells, set column widths, and even generate a dozen departmental reports in one batch.