How to Automate Excel Tasks with Python: Complete Tutorial 2026

💡 Real example: A financial analyst in Toronto spent 4 hours every Friday pulling data from 12 Excel files and creating a consolidated report. After learning Python Excel automation, the same task runs automatically every Friday at 8 AM and lands in her inbox. She gained back 200 hours per year.

Feature	Python	VBA / Macros
Requires Excel installed	No ✓	Yes ✗
Handle 1M+ rows easily	Yes ✓	Slow ✗
Connect to APIs & databases	Yes ✓	Limited △
Run on servers / cloud	Yes ✓	No ✗
Version control (Git)	Yes ✓	No ✗
Transfer skills to other tools	Yes ✓	Excel only ✗
Learning resources & community	Huge ✓	Limited △
Automate Excel UI clicks	Via xlwings △	Yes ✓

Verdict: Python wins for 90% of Excel automation scenarios. VBA is only preferable when you specifically need to click buttons or interact with the Excel UI in old legacy files. For everything else — data processing, report generation, bulk operations — Python is faster, more powerful, and far more maintainable.

Quick decision guide: Use openpyxl for creating and formatting .xlsx files. Use pandas for data analysis and bulk processing. Use xlwings when you need to control a live Excel session. For most automation tasks, you'll use openpyxl + pandas together.

pip install openpyxl pandas xlwings

# Verify installation
python -c "import openpyxl, pandas, xlwings; print('All installed!')"

# For .xls files (older format)
pip install xlrd

import openpyxl

# Load an existing Excel file
wb = openpyxl.load_workbook('sales_report.xlsx')

# Access a specific sheet
ws = wb['Q1 Sales']

# Read a specific cell value
revenue = ws['B2'].value
print(f"Revenue: {revenue}")

# Loop through all rows
for row in ws.iter_rows(min_row=2, values_only=True):
    product, sales, region = row[0], row[1], row[2]
    print(f"{product}: ${sales:,} ({region})")

# Get sheet dimensions
print(f"Sheet has {ws.max_row} rows and {ws.max_column} columns")

# List all sheet names
print(wb.sheetnames)

import pandas as pd

# Read the first sheet
df = pd.read_excel('sales_report.xlsx')

# Read a specific sheet by name
df = pd.read_excel('sales_report.xlsx', sheet_name='Q1 Sales')

# Read multiple sheets at once
all_sheets = pd.read_excel('sales_report.xlsx', sheet_name=None)
for sheet_name, df in all_sheets.items():
    print(f"Sheet '{sheet_name}': {len(df)} rows")

# Basic data exploration
print(df.head())           # First 5 rows
print(df.describe())       # Statistics
print(df.dtypes)           # Column types

# Filter rows
high_sales = df[df['Sales'] > 10000]
toronto_data = df[df['Region'] == 'Toronto']

# Calculate totals
total_revenue = df['Sales'].sum()
print(f"Total Revenue: ${total_revenue:,.2f}")

import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill

# Create a new workbook
wb = openpyxl.Workbook()
ws = wb.active
ws.title = 'Sales Report'

# Write headers
headers = ['Product', 'Q1 Sales', 'Q2 Sales', 'Q3 Sales', 'Total']
for col, header in enumerate(headers, start=1):
    ws.cell(row=1, column=col, value=header)

# Write data rows
sales_data = [
    ['Widget A', 12500, 14200, 11800],
    ['Widget B', 8900, 9100, 10500],
    ['Widget C', 21000, 19800, 22400],
]

for row_num, row_data in enumerate(sales_data, start=2):
    for col, value in enumerate(row_data, start=1):
        ws.cell(row=row_num, column=col, value=value)
    # Add SUM formula in the Total column
    ws.cell(row=row_num, column=5, value=f"=SUM(B{row_num}:D{row_num})")

# Save the file
wb.save('sales_report_auto.xlsx')
print("Excel file created!")

import pandas as pd

# Write a DataFrame to Excel
df = pd.DataFrame({
    'Product': ['Widget A', 'Widget B', 'Widget C'],
    'Q1 Sales': [12500, 8900, 21000],
    'Q2 Sales': [14200, 9100, 19800],
})
df['Total'] = df[['Q1 Sales', 'Q2 Sales']].sum(axis=1)

# Write to Excel
df.to_excel('output.xlsx', index=False)

# Write multiple DataFrames to different sheets
with pd.ExcelWriter('multi_sheet_report.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sales', index=False)
    df_summary = df.groupby('Product')['Total'].sum().reset_index()
    df_summary.to_excel(writer, sheet_name='Summary', index=False)

print("Multi-sheet report created!")

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = openpyxl.Workbook()
ws = wb.active

# Style the header row
header_fill = PatternFill(start_color="1E40AF", end_color="1E40AF", fill_type="solid")
header_font = Font(name='Calibri', bold=True, color="FFFFFF", size=12)
center_align = Alignment(horizontal='center', vertical='center')

headers = ['Product', 'Revenue', 'Growth', 'Status']
for col, header in enumerate(headers, start=1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = center_align

# Highlight rows based on values (conditional-style)
data = [
    ['Widget A', 125000, 0.15, 'On Track'],
    ['Widget B', 89000, -0.05, 'At Risk'],
    ['Widget C', 210000, 0.28, 'Exceeding'],
]

for row_idx, row_data in enumerate(data, start=2):
    for col_idx, value in enumerate(row_data, start=1):
        cell = ws.cell(row=row_idx, column=col_idx, value=value)

    # Color-code the Status column
    status_cell = ws.cell(row=row_idx, column=4)
    if row_data[3] == 'Exceeding':
        status_cell.fill = PatternFill(start_color="D1FAE5", fill_type="solid")
        status_cell.font = Font(color="065F46", bold=True)
    elif row_data[3] == 'At Risk':
        status_cell.fill = PatternFill(start_color="FEE2E2", fill_type="solid")
        status_cell.font = Font(color="991B1B", bold=True)

# Set column widths
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 12
ws.column_dimensions['D'].width = 15

# Freeze the header row
ws.freeze_panes = 'A2'

wb.save('formatted_report.xlsx')
print("Formatted report saved!")

💡 Pro tip: Excel colors use hex codes without the # sign. 1E40AF is the same as #1E40AF — just drop the hash. You can use any color picker to find the hex code you want.

import pandas as pd
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils.dataframe import dataframe_to_rows

# Step 1: Load and clean raw data
df = pd.read_excel('raw_sales_data.xlsx')

# Remove duplicates and blank rows
df = df.drop_duplicates()
df = df.dropna(subset=['Product', 'Revenue'])

# Fix data types
df['Revenue'] = pd.to_numeric(df['Revenue'], errors='coerce')
df['Date'] = pd.to_datetime(df['Date'])

# Step 2: Aggregate data
monthly_summary = df.groupby(
    [df['Date'].dt.strftime('%Y-%m'), 'Region']
).agg(
    Total_Revenue=('Revenue', 'sum'),
    Transactions=('Revenue', 'count'),
    Avg_Revenue=('Revenue', 'mean')
).reset_index()

# Step 3: Write to Excel with formatting
wb = openpyxl.Workbook()
ws = wb.active
ws.title = 'Monthly Summary'

# Write DataFrame to sheet
for r_idx, row in enumerate(
    dataframe_to_rows(monthly_summary, index=False, header=True), start=1
):
    for c_idx, value in enumerate(row, start=1):
        ws.cell(row=r_idx, column=c_idx, value=value)

# Style the header row
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill(start_color="1E40AF", fill_type="solid")
    cell.alignment = Alignment(horizontal='center')

wb.save('executive_report.xlsx')
print("Executive report generated!")

C:\Python311\python.exe C:\scripts\generate_report.py

# Run: crontab -e  then add:
0 8 * * 1 /usr/bin/python3 /home/user/scripts/generate_report.py

import schedule
import time

def generate_weekly_report():
    # Your excel automation code here
    print("Generating weekly Excel report...")

# Run every Monday at 8:00 AM
schedule.every().monday.at("08:00").do(generate_weekly_report)

while True:
    schedule.run_pending()
    time.sleep(60)

How to Automate Reports with Python

Automate Excel, PDF, and HTML reports. Save 20+ hours per week.

Python for Data Automation Without Data Science

Practical guide for non-technical professionals: CSV, Excel, databases.

Python Automation Guide: Complete Tutorial for Beginners

Master Python automation from scratch with hands-on examples.