How to Automate Excel with Python
Excel automation is one of the most-searched practical skills because the work is so repetitive: merging monthly files, cleaning messy data, and rebuilding the same report. Python with pandas and openpyxl does all three in seconds. You need Python installed and about 20 minutes.
Step 1: Install the two libraries
Open a terminal and run:
pip install pandas openpyxlStep 2: Merge and clean in one script
Suppose a folder holds monthly sales files with the same columns. This script reads them all, stacks them, removes exact duplicates and empty rows, and writes one clean file:
import pandas as pd, glob
files = glob.glob("sales_*.xlsx") # all monthly files in this folder
frames = [pd.read_excel(f) for f in files]
df = pd.concat(frames, ignore_index=True)
df = df.drop_duplicates().dropna(how="all")
df["Date"] = pd.to_datetime(df["Date"]) # normalise dates for sorting/filtering
df = df.sort_values("Date")
df.to_excel("sales_combined.xlsx", index=False)
print(f"Combined {len(files)} files into {len(df)} rows.")Step 3: Generate the report automatically
From the clean data, build a summary — total sales by product, for example — and write it with formatting to a new workbook:
import pandas as pd
from openpyxl.styles import Font
df = pd.read_excel("sales_combined.xlsx")
summary = df.groupby("Product")["Amount"].sum().reset_index()
summary.columns = ["Product", "Total Sales"]
summary = summary.sort_values("Total Sales", ascending=False)
summary.to_excel("sales_report.xlsx", index=False)
from openpyxl import load_workbook
wb = load_workbook("sales_report.xlsx")
ws = wb.active
ws["A1"].font = Font(bold=True)
ws["B1"].font = Font(bold=True)
ws.column_dimensions["A"].width = 25
ws.column_dimensions["B"].width = 18
wb.save("sales_report.xlsx")
print("Report written.")Step 4: Schedule it
On Windows, use Task Scheduler to run python report.py weekly; on macOS or Linux, add a cron line such as 0 8 * * 1 python3 /path/to/report.py (Mondays at 8am). Always keep the original files untouched — the script reads them and writes new outputs, so a bad run never destroys source data. Test on copies first, and add a row-count print (as above) so a silent empty result is obvious.
Common mistakes
Overwriting the source file instead of writing a new one; assuming every file has identical columns (check with df.columns first); and ignoring date formats — mixed date formats are the number-one cause of "the numbers look wrong" in automated reports.
Frequently asked questions
Do I need to know Python first?
Only the basics: running a script, installing packages, and reading error messages. This guide's scripts are complete starting points — change the file names and column names to match your data.
Can Python read my existing formatted Excel files?
Yes, pandas reads values, and openpyxl can read and write formatting. Very complex formatting (merged cells, macros) needs care — test on a copy before automating.
What about Google Sheets?
The same pandas logic works after exporting to Excel or CSV, or through the Google Sheets API for a live connection. For most office reporting, the Excel route is simpler and more reliable.