How to Automate Excel Reports with Python

The Problem

Every week, the same routine: open five spreadsheets, copy data between tabs, update formulas, format cells, export to PDF, and email the result to three managers. It takes two hours. Sometimes more when the data is messy.

Manual Excel reporting is one of the most common time sinks in business operations. It's error-prone, tedious, and — most importantly — completely automatable.

If you recognise any of these patterns, this guide is for you:

• Copying data between spreadsheets weekly or monthly
• Manually updating charts and pivot tables
• Sending the same report to the same people on a schedule
• Spending more time formatting than analysing

The Solution

We'll use Python with openpyxl to build a script that:

1. Reads raw data from source files
2. Processes and summarises it
3. Generates a formatted Excel report
4. Saves it ready for distribution

The entire process runs in seconds instead of hours.

Implementation

Step 1: Install Dependencies

pip install openpyxl

Step 2: Read Source Data

from openpyxl import load_workbook
from datetime import datetime

def read_sales_data(filepath):
    """Read raw sales data from the source spreadsheet."""
    wb = load_workbook(filepath, read_only=True)
    ws = wb.active

    data = []
    for row in ws.iter_rows(min_row=2, values_only=True):
        data.append({
            'date': row[0],
            'product': row[1],
            'quantity': row[2],
            'revenue': row[3],
        })

    wb.close()
    return data

Step 3: Process and Summarise

from collections import defaultdict

def summarise_by_product(data):
    """Aggregate sales data by product."""
    summary = defaultdict(lambda: {'quantity': 0, 'revenue': 0})

    for row in data:
        product = row['product']
        summary[product]['quantity'] += row['quantity']
        summary[product]['revenue'] += row['revenue']

    return dict(summary)

Step 4: Generate the Report

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

def generate_report(summary, output_path):
    """Create a formatted Excel report from summarised data."""
    wb = Workbook()
    ws = wb.active
    ws.title = "Sales Report"

    # Header styling
    header_font = Font(bold=True, color="FFFFFF", size=12)
    header_fill = PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid")

    # Report title
    ws.merge_cells('A1:D1')
    ws['A1'] = f"Sales Report — {datetime.now().strftime('%B %Y')}"
    ws['A1'].font = Font(bold=True, size=16)

    # Column headers
    headers = ['Product', 'Units Sold', 'Revenue', 'Avg Price']
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=3, column=col, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal='center')

    # Data rows
    for row_idx, (product, stats) in enumerate(sorted(summary.items()), 4):
        ws.cell(row=row_idx, column=1, value=product)
        ws.cell(row=row_idx, column=2, value=stats['quantity'])
        ws.cell(row=row_idx, column=3, value=stats['revenue'])
        ws.cell(row=row_idx, column=3).number_format = '£#,##0.00'
        avg = stats['revenue'] / stats['quantity'] if stats['quantity'] else 0
        ws.cell(row=row_idx, column=4, value=round(avg, 2))
        ws.cell(row=row_idx, column=4).number_format = '£#,##0.00'

    # Auto-fit column widths
    for col in ws.columns:
        max_length = max(len(str(cell.value or '')) for cell in col)
        ws.column_dimensions[col[0].column_letter].width = max_length + 4

    wb.save(output_path)
    return output_path

Step 5: Put It All Together

if __name__ == '__main__':
    data = read_sales_data('raw_sales.xlsx')
    summary = summarise_by_product(data)
    output = generate_report(summary, f"report_{datetime.now().strftime('%Y%m%d')}.xlsx")
    print(f"Report generated: {output}")

The Result

What used to take 2+ hours of manual work now runs in under 10 seconds:

• Zero copy-paste errors — data flows directly from source to report
• Consistent formatting — every report looks professional
• Instant generation — run the script and the report is ready
• Schedulable — add a cron job or Windows Task Scheduler to run it automatically

One client reduced their weekly reporting time from 8 hours across their team to essentially zero, freeing up a full working day each week.

Next Steps

This is a basic example. Production automation systems typically add:

• Email delivery — automatically send reports to stakeholders
• Multiple data sources — pull from databases, APIs, and multiple files
• Error handling — validate data and alert on anomalies
• Dashboard generation — create charts and visual summaries

If you're spending hours on repetitive reporting, automation services can help you eliminate that entirely. Every system I build follows the same principle: identify the repetitive, automate the predictable, free up time for the work that matters.

Get in touch to discuss automating your reporting workflows.