How to Automate Excel Reporting using Python Pandas

15 mins0
How to Automate Excel Reporting using Python Pandas

If you work with Excel reports every day, learning Excel Reporting in Python using Pandas will completely change the way you deal with data. Instead of hours of copying, pasting, and fixing formulas, you let a short Python script build the very same report in seconds. Throughout this tutorial, you will learn, via simple, real-world examples, how to automate Excel reports with Python Pandas. You’ll learn data cleaning, summarization, and then how to export polished Excel files that your team can open and use straight away in Excel.

Why Use Excel for Reporting in a Python Application?

Excel still runs reporting in many companies. Managers, finance teams, and clients often prefer to review numbers in an Excel workbook, even if your data pipeline lives in Python.

Using Excel for reporting in a Python application gives you the best of both worlds:

  • You build the logic, calculations, and automation in Python.
  • You deliver the final result in a familiar Excel file.
  • Your stakeholders can filter, sort, and tweak numbers without touching Python.
  • You can keep existing workflows (emailing Excel files, saving them to SharePoint, Teams, or a shared drive).

Pandas for Excel Reporting in Python

What is Pandas?

Pandas is a Python library that makes working with tables of data fast and simple. You can load CSVs, Excel files, databases, and APIs into DataFrames, which feel similar to Excel tables but with far more power.

When you focus on Excel Reporting in Python using Pandas, you use Pandas to:

  • Read raw files
  • Clean and transform data
  • Build summaries and KPIs
  • Export clean, ready-to-use Excel reports

How to Install Pandas

You install Pandas with one command in your terminal or command prompt:

pip install pandas openpyxl

The openpyxl package lets Pandas write modern .xlsx files smoothly. Key Benefits of Using Pandas for Excel Reporting:

  • Handles large files that make Excel slow or unstable
  • Cleans messy data (dates, duplicates, missing values) in seconds
  • Automates repeated steps instead of manual copy and paste
  • Produces consistent, error-free reports every time
  • Works well with CSVs, databases, APIs, and Excel in one flow
  • Easy to schedule scripts so reports arrive automatically

Python Script to Automate Excel Report Generation for Daily Sales

Let’s assume, you are a sales analyst. Every morning you need a summary of yesterday’s orders by region and product. This script builds that report from scratch.

import pandas as pd
from datetime import datetime

sales_data = {
    "order_id": [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008],
    "order_date": ["2024-11-05", "2024-11-05", "2024-11-05", "2024-11-05", 
                   "2024-11-04", "2024-11-05", "2024-11-05", "2024-11-05"],
    "region": ["North", "South", "North", "East", "South", "North", "East", "South"],
    "product": ["Widget A", "Widget B", "Widget A", "Widget C", "Widget B", "Widget C", "Widget A", "Widget B"],
    "revenue": [250.00, 180.00, 250.00, 320.00, 180.00, 400.00, 250.00, 180.00]
}

df = pd.DataFrame(sales_data)
df["order_date"] = pd.to_datetime(df["order_date"])
report_date = datetime(2024, 11, 5).date()
filtered_df = df[df["order_date"].dt.date == report_date]
summary = filtered_df.groupby(["region", "product"]).agg(
    total_revenue=("revenue", "sum"),
    order_count=("order_id", "count")
).reset_index()
summary = summary.sort_values(["region", "total_revenue"], ascending=[True, False])

with pd.ExcelWriter("daily_sales_report.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Daily Summary", index=False)
    workbook = writer.book
    worksheet = writer.sheets["Daily Summary"]
    for idx, col in enumerate(worksheet.columns):
        max_length = max(len(str(cell.value)) for cell in col)
        worksheet.column_dimensions[col[0].column_letter].width = max_length + 2

This script creates a clean Excel file with properly sized columns. Your sales team can open it and see exactly what sold where, without any manual work from you.

Create Automated Excel Reports with Pandas for Marketing KPIs

A marketing manager needs a weekly dashboard that merges Facebook, Google, and LinkedIn data. This example shows how to automate daily excel reporting tasks using Python for multiple channels.

import pandas as pd

facebook_data = {
    "channel": ["Facebook", "Facebook", "Facebook"],
    "campaign": ["Summer Sale", "Back to School", "Black Friday"],
    "impressions": [15000, 22000, 30000],
    "clicks": [450, 660, 900],
    "spend": [225.00, 330.00, 450.00],
    "conversions": [45, 66, 90]
}

google_data = {
    "channel": ["Google", "Google", "Google"],
    "campaign": ["Summer Sale", "Back to School", "Black Friday"],
    "impressions": [20000, 28000, 35000],
    "clicks": [800, 1120, 1400],
    "spend": [400.00, 560.00, 700.00],
    "conversions": [80, 112, 140]
}

linkedin_data = {
    "channel": ["LinkedIn", "LinkedIn", "LinkedIn"],
    "campaign": ["Summer Sale", "Back to School", "Black Friday"],
    "impressions": [8000, 12000, 15000],
    "clicks": [160, 240, 300],
    "spend": [320.00, 480.00, 600.00],
    "conversions": [16, 24, 30]
}

df_facebook = pd.DataFrame(facebook_data)
df_google = pd.DataFrame(google_data)
df_linkedin = pd.DataFrame(linkedin_data)
all_channels = pd.concat([df_facebook, df_google, df_linkedin], ignore_index=True)

kpi_summary = all_channels.groupby(["channel", "campaign"]).agg(
    impressions=("impressions", "sum"),
    clicks=("clicks", "sum"),
    spend=("spend", "sum"),
    conversions=("conversions", "sum")
).reset_index()

kpi_summary["ctr"] = (kpi_summary["clicks"] / kpi_summary["impressions"]).round(4)
kpi_summary["cpc"] = (kpi_summary["spend"] / kpi_summary["clicks"]).round(2)
kpi_summary["cpa"] = (kpi_summary["spend"] / kpi_summary["conversions"]).round(2)
kpi_summary = kpi_summary.sort_values(["channel", "spend"], ascending=[True, False])

with pd.ExcelWriter("weekly_marketing_report.xlsx", engine="openpyxl") as writer:
    all_channels.to_excel(writer, sheet_name="Raw Data", index=False)
    kpi_summary.to_excel(writer, sheet_name="KPI Summary", index=False)
    workbook = writer.book
    for sheet_name in ["Raw Data", "KPI Summary"]:
        worksheet = writer.sheets[sheet_name]
        for idx, col in enumerate(worksheet.columns):
            max_length = max(len(str(cell.value)) for cell in col)
            worksheet.column_dimensions[col[0].column_letter].width = max_length + 2

Your marketing team receives one file with raw data and a separate sheet of calculated KPIs. They can filter by channel or campaign directly in Excel.

Using Python Pandas for Excel Data Analysis Across Operations

Let’s take last example to understand how data analysis across different operation can be migrated as reports to spreedsheets using python and pandas. Assume you have multiple data sets like products, orders and customers and you need a report from combining these data sets.

import pandas as pd

products_data = [
    {"product_id": 1, "product_name": "Laptop", "category": "Electronics", "unit_cost": 650.0},
    {"product_id": 2, "product_name": "Monitor", "category": "Electronics", "unit_cost": 130.0},
    {"product_id": 3, "product_name": "Keyboard", "category": "Accessories", "unit_cost": 18.0},
    {"product_id": 4, "product_name": "Mouse", "category": "Accessories", "unit_cost": 9.0}
]

customers_data = [
    {"customer_id": 101, "customer_name": "Nova Retail", "segment": "Retail", "country": "Canada"},
    {"customer_id": 102, "customer_name": "Alfa Tech", "segment": "B2B", "country": "United Kingdom"},
    {"customer_id": 103, "customer_name": "Baltic Store", "segment": "Retail", "country": "Poland"},
    {"customer_id": 104, "customer_name": "Hudson Corp", "segment": "B2B", "country": "United States"}
]

orders_data = [
    {"order_id": 1001, "order_date": "2025-01-01", "customer_id": 101, "product_id": 1, "quantity": 3, "unit_price": 900.0},
    {"order_id": 1002, "order_date": "2025-01-02", "customer_id": 102, "product_id": 2, "quantity": 10, "unit_price": 180.0},
    {"order_id": 1003, "order_date": "2025-01-03", "customer_id": 103, "product_id": 3, "quantity": 40, "unit_price": 30.0},
    {"order_id": 1004, "order_date": "2025-01-03", "customer_id": 104, "product_id": 1, "quantity": 5, "unit_price": 950.0},
    {"order_id": 1005, "order_date": "2025-01-04", "customer_id": 101, "product_id": 4, "quantity": 25, "unit_price": 18.0},
    {"order_id": 1006, "order_date": "2025-01-04", "customer_id": 102, "product_id": 3, "quantity": 30, "unit_price": 28.0},
    {"order_id": 1007, "order_date": "2025-01-05", "customer_id": 103, "product_id": 2, "quantity": 6, "unit_price": 175.0},
    {"order_id": 1008, "order_date": "2025-01-05", "customer_id": 104, "product_id": 4, "quantity": 20, "unit_price": 20.0}
]

df_products = pd.DataFrame(products_data)
df_customers = pd.DataFrame(customers_data)
df_orders = pd.DataFrame(orders_data)

df_orders["order_date"] = pd.to_datetime(df_orders["order_date"])

orders_products = df_orders.merge(df_products, on="product_id", how="left")
full_data = orders_products.merge(df_customers, on="customer_id", how="left")

full_data["revenue"] = full_data["quantity"] * full_data["unit_price"]
full_data["cost"] = full_data["quantity"] * full_data["unit_cost"]
full_data["profit"] = full_data["revenue"] - full_data["cost"]

management_summary = (
    full_data
    .groupby(["country", "segment", "category"], as_index=False)
    .agg(
        total_revenue=("revenue", "sum"),
        total_profit=("profit", "sum"),
        orders=("order_id", "nunique"),
        customers=("customer_id", "nunique"),
        units_sold=("quantity", "sum")
    )
)

management_summary["avg_order_value"] = management_summary["total_revenue"] / management_summary["orders"]
management_summary["profit_margin"] = management_summary["total_profit"] / management_summary["total_revenue"]

with pd.ExcelWriter("operations_report_static.xlsx") as writer:
    df_products.to_excel(writer, sheet_name="Products", index=False)
    df_customers.to_excel(writer, sheet_name="Customers", index=False)
    df_orders.to_excel(writer, sheet_name="Orders", index=False)
    full_data.to_excel(writer, sheet_name="All Orders Detail", index=False)
    management_summary.to_excel(writer, sheet_name="Management Summary", index=False)

This example mimics real life reports and analysis for dashboards or reporting modules. You combine several tables, calculate revenue and profit, and send a clear summary to Excel.

Conclusion

Automating Excel Reporting in Python using Pandas will allow you to replace your manual tasks by using fast and dependable code in your scripts so that you are able to read raw data, cleanse it, summarize it, and then present the data in a way that can be accessed by all in an Excel file.

You start with one painful report and craft a script for this report. The goal is to become comfortable with the process and learn how to automate excel reports with python pandas. After this is accomplished, you move to other teams and files. Your business goes on to retain a friendly-looking Excel interface over the years while Python and Pandas work unnoticed behind the scenes to provide accurate reports.