How to automate Excel reports with Python (practical guide)

What it means to automate an Excel report

Most recurring reports follow the same steps every time: download or open several files, copy them into one sheet, filter, fix a few columns, build a pivot table, format it and email it. Automating the report means writing those steps once as a script, so the computer repeats them exactly, every time.

Python is a good fit because it reads and writes Excel, CSV, databases and APIs, and its data libraries are free and mature. The end result is still an Excel file your team already knows how to use; nobody has to change how they read the report.

The goal is not to eliminate Excel. It is to stop rebuilding the same workbook by hand, and to remove the copy-and-paste errors that creep in when someone is in a hurry on a Monday morning.

When automating is worth it, and when it is not

Before writing code, estimate how much time the report takes, how often it runs and how many errors it causes. A report that takes two hours every week is about a hundred hours a year; a report that runs once a year and changes format every time is usually not worth automating.

• Worth it: the report repeats on a schedule (daily, weekly, monthly).
• Worth it: the data comes from several files or systems that have to be combined.
• Worth it: copy-and-paste errors have caused real problems.
• Worth it: several people need the same report in different cuts (by branch, region or salesperson).
• Not worth it: the format is still changing every month and nobody agrees on the definition.
• Not worth it: the source data has no consistent structure, such as columns renamed by hand each time.
• Not worth it: the report is used once or twice a year.

What you need to install

You need Python 3 and two libraries. In a terminal, install them with pip install pandas openpyxl. pandas reads, combines and summarizes data; openpyxl is the engine pandas uses to write .xlsx files and the library you use for formatting.

If you want to send the report by email, Python's standard library already includes smtplib, so no extra package is needed. One practical tip: create a project folder with three subfolders, input, output and scripts, and never overwrite the original files. Keeping inputs untouched lets you rerun the report any time something looks off.

Step by step: from several files to one summary report

Here is a common scenario: each branch sends an Excel file with the same columns (date, branch, product, quantity, amount), and someone combines them into a monthly summary. The script below follows the same steps a person would.

• 1. Find the input files: files = sorted(Path('input').glob('*.xlsx')) lists every Excel file in the input folder.
• 2. Read and combine: sales = pd.concat([pd.read_excel(f) for f in files], ignore_index=True) builds one table from all of them.
• 3. Clean: sales = sales.dropna(subset=['amount']) removes rows without an amount, and sales['date'] = pd.to_datetime(sales['date']) makes sure dates are real dates and not text.
• 4. Summarize: summary = sales.pivot_table(index='branch', columns='product', values='amount', aggfunc='sum', fill_value=0) produces the same table you would build with a pivot table.
• 5. Write the workbook: inside with pd.ExcelWriter('output/report.xlsx', engine='openpyxl') as w: call summary.to_excel(w, sheet_name='Summary') and sales.to_excel(w, sheet_name='Detail', index=False).
• 6. Format: open the file with wb = load_workbook('output/report.xlsx'), set headers in bold, apply a number format, freeze the first row and adjust column widths, then call wb.save(...).

Making the output look professional

An automated report that looks worse than the manual one will not be trusted, even if the numbers are better. openpyxl lets you control the details: bold headers with a fill color, currency formats such as cell.number_format = '$#,##0.00', frozen panes with ws.freeze_panes = 'A2', column widths and even native Excel charts.

In practice, the cleanest approach is often a template. Design the workbook once in Excel, with your logo, colors and formulas, and let the script fill in only the data cells. People see the format they know, and the script stays simple.

How to run the report automatically

A script you have to remember to run is only half automated. To run it on a schedule, use the scheduler of the machine where it lives: Task Scheduler on Windows, cron on macOS and Linux. Both can run the script every Monday at 7:00, for example, so the report is ready before anyone arrives.

If the source files arrive by email or in a shared folder, the script can pick them up from there. If the report has to be emailed, smtplib can attach the file and send it to a distribution list. For important reports, add a simple check: if the input files are missing or the totals look wrong, send an alert instead of a broken report.

Common mistakes

Most failures in automated reports do not come from the code; they come from the data. These are the ones I see most often:

• Column names change. A file with "Amount" instead of "amount" breaks the script. Normalize headers (lowercase, no extra spaces) before using them.
• Dates read as text. Different date formats in different files need explicit conversion, or the summary groups the wrong months.
• Merged cells and title rows. Files designed for humans often have a title on top; skip those rows with skiprows or read from a fixed header row.
• Silent failures. A script that runs at night needs a log and an alert; otherwise you discover the problem when the report is already wrong.
• Overwriting the history. Save each report with the date in the file name.

Alternatives to Python: Power Query and macros

Python is not the only option. Excel includes Power Query, which can import, clean and combine data from several sources through a visual interface and refresh it with one click. For teams that live in Excel and whose data stays in files, Power Query solves many cases without code.

VBA macros can automate tasks inside Excel too, but they are harder to maintain, depend on the desktop app, and are often blocked by security policies. Python makes sense when you need to pull data from APIs or databases, process larger volumes, send emails or run without anyone opening Excel.

My experience automating data processes

At ZazPay I built automation and validation tools and data pipelines to analyze many repositories at scale. The lesson that transfers directly to Excel reports: run the automated version in parallel with the manual one until both match, and only then switch off the manual process.

If your report depends on many files, exceptions or people, I can build and document the automation for you, or review a script someone on your team already started.