Tutorials › Python › Excel Automation with Python

Excel Automation with Python

Replace manual Excel work with Python scripts — save hours every week

Many data analyst tasks that take hours in Excel take minutes with Python: combining 12 monthly files, applying the same formatting to 50 sheets, updating pivot tables with new data, and generating weekly reports automatically. This tutorial covers the most valuable Excel automation tasks using pandas and openpyxl.

Example

Automate a monthly report workflow
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
import os
from datetime import datetime

# TASK: Combine 12 monthly CSV files into one formatted Excel report

# Step 1: Load all CSV files
folder = "monthly_data/"
all_dfs = []
for filename in sorted(os.listdir(folder)):
    if filename.endswith(".csv"):
        df = pd.read_csv(os.path.join(folder, filename))
        df["Month"] = filename.replace(".csv", "")
        all_dfs.append(df)

combined = pd.concat(all_dfs, ignore_index=True)

# Step 2: Create summary by Region
summary = combined.groupby(["Region","Month"]).agg(
    Total_Revenue=("Revenue", "sum"),
    Order_Count=("OrderID", "count")
).reset_index()

# Step 3: Save to Excel with multiple sheets
output_file = f"monthly_report_{datetime.today().strftime('%Y%m')}.xlsx"
with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
    combined.to_excel(writer, sheet_name="Raw Data", index=False)
    summary.to_excel(writer, sheet_name="Summary", index=False)

print(f"Report saved: {output_file}")
print(f"Total rows: {len(combined):,}")
💡 This script replaces what typically takes a junior analyst 2 hours of manual work in Excel, and runs in under 30 seconds.

Key Points

  • ✓pd.ExcelWriter with multiple to_excel() calls creates a multi-sheet workbook
  • ✓openpyxl lets you apply formatting (font, fill, alignment) after writing data
  • ✓sorted(os.listdir()) ensures files are processed in alphabetical (usually chronological) order
  • ✓Automate recurring reports by scheduling the script to run on a fixed day each month
  • ✓This pattern — load → clean → aggregate → export — is the core of analyst automation

Practice Question

You want to save two DataFrames (raw_data and summary) to two sheets in one Excel file. Which approach is correct?