How to Automate Excel Reports with Python (Beginner Guide)

If you build the same Excel report every week, Python can do it for you in seconds. In this guide you will use pandas and openpyxl to read data, summarize it, and save a clean report.

What You Need

• Python installed from python.org
• An Excel file with sales or other data
• Two packages: pandas and openpyxl

Step 1: Install the Packages

pip install pandas openpyxl

Step 2: Read Your Excel File

import pandas as pd

df = pd.read_excel('sales.xlsx')
print(df.head())

Step 3: Summarize the Data

Group the data to see totals by product or month. This example assumes columns named Product and Amount.

summary = df.groupby('Product')['Amount'].sum().reset_index()
summary = summary.sort_values('Amount', ascending=False)

Step 4: Save a New Report

with pd.ExcelWriter('weekly_report.xlsx') as writer:
    df.to_excel(writer, sheet_name='Data', index=False)
    summary.to_excel(writer, sheet_name='Summary', index=False)

Step 5: Run It Every Week

Save your code as a file such as report.py and run it with python report.py. You can also schedule it with Windows Task Scheduler or cron on Mac and Linux.

Tips for Better Reports

1. Keep column names consistent in your source file.
2. Clean missing values before summarizing.
3. Test on a copy of your file first.
4. Add the date to the report file name.

Frequently Asked Questions

Can Python replace Excel?

Not completely. Python is best for repeating tasks, while Excel remains great for quick analysis.

Is pandas free?

Yes. Pandas and openpyxl are free and open source.

Can it email the report too?

Yes. Python can send the finished file by email automatically.

Final Thoughts

Start with one report and expand from there. Need a custom automation? Contact AwaisTechHub.