How to Automate Excel Reports Using Python

If you spend hours every week inside Excel, manually updating the same reports, you know the process is tedious and ripe for human error. A misplaced formula or a copy-paste mistake can throw off your entire analysis. This guide will show you how to use Python to build an automated workflow that generates your Excel reports for you, freeing you from the repetitive manual work and ensuring your data is always accurate.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Automate Excel Reports with Python?

Stepping away from manual report building in Excel might feel daunting, but the benefits are massive. For anyone who lives in Excel, using a simple Python script is like giving your reporting process superpowers.

• Save Tremendous Amounts of Time: The most significant advantage is time. A process that takes you two hours every Monday morning can be condensed into a script that runs automatically in under a minute.
• Eliminate Human Error: Manual data wrangling - filtering, sorting, copy-pasting - is where mistakes happen. A script performs the exact same steps in the exact same order, every single time. This consistency guarantees accuracy.
• Scale Your Reporting: Manual reporting slows down as data grows. Handling 50,000 rows in Excel by hand is painful, but for a Python script, it’s just another day at the office. Automation scales effortlessly without a drop in performance.
• Create More Sophisticated Reports: Python allows for more complex calculations, data merging from different sources, and customization that can be difficult or clunky to implement with Excel formulas alone.

Getting Your Environment Ready

Before you can automate anything, you need to set up your computer. This part is quick and you only have to do it once.

1. Install Python

If you don't already have Python installed, head over to the official Python website and download the latest version for your operating system (Windows or macOS). During installation on Windows, make sure to check the box that says "Add Python to PATH." This small step makes running Python from your command line much easier.

2. Install Key Libraries

Libraries are pre-written bundles of code that help you perform specific tasks without having to write everything from scratch. For Excel automation, we'll need a few key players. Open your command prompt (on Windows) or terminal (on macOS) and install them using Python's package manager, pip.

Just run this one command:

pip install pandas openpyxl xlsxwriter

Here's a quick breakdown of what these libraries do:

• pandas: This is the most crucial library for data analysis in Python. It's the equivalent of having ultra-powerful Excel sheets and Pivot Tables that you control with code. We'll use it to read, clean, and analyze our data.
• openpyxl: This library is the engine that pandas uses behind the scenes to read and write .xlsx files. You often won't use it directly, but pandas needs it to be installed.
• xlsxwriter: While pandas can write basic Excel files, xlsxwriter gives you advanced formatting capabilities. It lets you create charts, adjust column widths, add conditional formatting, and much more, allowing you to create polished, presentation-ready reports.

Step-by-Step: Building Your Automated Report

Now that the setup is done, let's build a practical example. Imagine we have a spreadsheet of raw sales data named raw_sales_data.xlsx. It contains columns for OrderDate, Region, Product, UnitsSold, and PricePerUnit.

Our goal is to automate a weekly report that calculates total revenue by region, highlights the top-selling products, and saves it all in a nicely formatted new Excel file.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Reading Your Excel Data into Python

First, create a new file named report_automation.py. We'll start by importing the pandas library and using it to load our Excel data. Think of this as the code equivalent of opening an Excel file.

import pandas as pd

# Define the file path to your raw data
file_path = 'raw_sales_data.xlsx'

# Read the Excel file into a pandas DataFrame
# A DataFrame is like a programmable spreadsheet table
try:
    df = pd.read_excel(file_path)
    print("File loaded successfully!")
    print(df.head()) # Print the first 5 rows to check
except FileNotFoundError:
    print(f"Error: The file '{file_path}' was not found.")

The df.head() command is a great way to quickly preview your data and ensure it loaded correctly without having to look at thousands of rows.

Step 2: Performing Calculations and Analyses

Here comes the fun part. Instead of manually adding new columns or creating pivot tables in Excel, we can do it all with a few lines of code. This is the core of the automation - all the "clicks" you'd normally perform are translated into code.

Let's add a "TotalRevenue" column and then summarize the data.

# --- Data Transformations ---

# Create a 'TotalRevenue' column by multiplying UnitsSold and PricePerUnit
df['TotalRevenue'] = df['UnitsSold'] * df['PricePerUnit']

# Ensure 'OrderDate' is a datetime type to enable time-based filtering
df['OrderDate'] = pd.to_datetime(df['OrderDate'])

# --- Data Analysis ---

# 1. Summarize total revenue by region (like a pivot table)
revenue_by_region = df.groupby('Region')['TotalRevenue'].sum().reset_index()
revenue_by_region = revenue_by_region.sort_values(by='TotalRevenue', ascending=False)
print("\nRevenue by Region:")
print(revenue_by_region)

# 2. Find the top 10 best-selling products by units sold
top_products = df.groupby('Product')['UnitsSold'].sum().reset_index()
top_products = top_products.sort_values(by='UnitsSold', ascending=False).head(10)
print("\nTop 10 Products:")
print(top_products)

In this block, groupby() does the same job as an Excel Pivot Table, instantly aggregating data for you. We've summarized revenue by region and identified top products in just a few lines. This replaces hundreds of manual clicks.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 3: Writing Everything to a New, Formatted Excel Report

Now that we have our summarized data (revenue_by_region and top_products), we need to save it into a new, organized Excel file. We'll use pandas' ExcelWriter with the xlsxwriter engine to place each summary on a separate sheet and even add a chart to visualize the regional performance.

output_path = 'formatted_sales_report.xlsx'

# Use the ExcelWriter to write multiple dataframes to one file
with pd.ExcelWriter(output_path, engine='xlsxwriter') as writer:
    # Write each dataframe to a different worksheet
    revenue_by_region.to_excel(writer, sheet_name='Revenue by Region', index=False)
    top_products.to_excel(writer, sheet_name='Top Products', index=False)
    df.to_excel(writer, sheet_name='Raw Data', index=False) # Optional: include the processed raw data

    # --- Add Formatting and Charts ---

    # Get the xlsxwriter workbook and worksheet objects
    workbook = writer.book
    regional_sheet = writer.sheets['Revenue by Region']

    # --- Create a bar chart ---
    chart = workbook.add_chart({'type': 'bar'})

    # Configure the series for the chart. The values are based on Excel-like cell notation.
    # We are graphing data from column B (TotalRevenue) from rows 2 to 5.
    chart.add_series({
        'name': 'Total Revenue',
        'categories': '=\'Revenue by Region\'!$A$2:$A$5', # A2 to A5 for Region names
        'values':     '=\'Revenue by Region\'!$B$2:$B$5', # B2 to B5 for Revenue values
    })
    
    # Add a chart title and axis labels
    chart.set_title({'name': 'Total Revenue by Region'})
    chart.set_x_axis({'name': 'Region'})
    chart.set_y_axis({'name': 'Revenue ($)'})

    # Insert the chart into the worksheet
    regional_sheet.insert_chart('D2', chart)

print(f"\nReport has been created successfully: {output_path}")

When you run this full script, it produces a polished Excel file with three sheets: a summary of revenue by region (complete with a bar chart!), a list of top-selling products, and a copy of the raw data. This entire report is now reproducible with a single click.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Putting It on Autopilot: Scheduling Your Script

Creating the script is the hard part - now you can make it run automatically. The goal is to set it and forget it. How you schedule it depends on your operating system:

• On Windows: Use the built-in Task Scheduler. You can create a new task that runs a command (e.g., python C:\path\to\your\report_automation.py) on a schedule, like every Monday at 8 AM.
• On macOS or Linux: Use cron. Cron is a utility that runs tasks (cron jobs) at specific times. You can set up a cron job to execute your Python script on any schedule you desire.

Once scheduled, your computer will automatically run the script, read the latest raw data, generate your polished report, and save it to a designated folder. You'll arrive at work with a fresh report waiting for you, no manual effort required.

Final Thoughts

By learning a few basic Python commands, you can completely transform your relationship with Excel reporting. You can move from being a data janitor who spends hours cleaning and organizing spreadsheets to an analyst who can focus on extracting insights. The process boils down to setting up your environment, using pandas to read and manipulate data, and writing the results back to a cleanly formatted Excel file.

While coding your own automation script is a powerful skill, we realize it's not for everyone. If you want the same automated, real-time reporting without touching any code, we've built Graphed for exactly that purpose. You connect your data sources directly - whether it's an ad platform, CRM, or even Google Sheets - and then just ask questions in plain English, like "Show me a dashboard of Shopify sales by campaign from Facebook Ads this quarter." We handle all the connections and visualizations automatically, giving you live dashboards that save you from the entire report creation loop.