If you spend part of every week renaming files, copying data between workbooks or building the same report, Python can take that work over. You do not need to become a programmer — a few dozen lines solve most office chores. This guide is the path I recommend to Excel users.

1. Set up (10 minutes)

1. Install Python from python.org. On Windows tick “Add python.exe to PATH”.
2. Open a terminal (search “cmd”) and check: python --version.
3. Install the libraries used below: pip install pandas openpyxl python-docx.
4. Use a simple editor — VS Code with the Python extension is free and excellent.

2. Files and folders with pathlib

from pathlib import Path
import shutil
from datetime import date

inbox = Path(r"C:\Users\me\Downloads")
archive = Path(r"C:\Reports") / date.today().strftime("%Y-%m")
archive.mkdir(parents=True, exist_ok=True)

for f in inbox.glob("Sales*.xlsx"):
    target = archive / f"{f.stem}_{date.today():%Y%m%d}{f.suffix}"
    shutil.move(f, target)
    print("moved", f.name, "->", target)

glob finds files by pattern, mkdir(exist_ok=True) creates folders safely, and shutil.move moves (and renames) in one step.

3. Read and summarise Excel with pandas

import pandas as pd

df = pd.read_excel("input/sales.xlsx", sheet_name="Data")
df["amount"] = df["Qty"] * df["Price"]
summary = df.groupby("Region", as_index=False)["amount"].sum()
summary.to_excel("output/summary.xlsx", index=False)

Step-by-step version with cleaning: Python for Excel users. Combining many files: merge Excel files with Python.

4. Format Excel output with openpyxl

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

wb = load_workbook("output/summary.xlsx")
ws = wb.active
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="217346")
for row in ws.iter_rows(min_row=2, min_col=2):
    for c in row:
        c.number_format = "#,##0"
ws.column_dimensions["A"].width = 14
wb.save("output/summary.xlsx")

pandas is best for data; openpyxl is best for formatting, formulas and specific cells. Use both.

5. Create a Word report

from docx import Document

doc = Document()
doc.add_heading("Monthly sales summary", level=1)
doc.add_paragraph(f"Total revenue: ₹{summary['amount'].sum():,.0f}")
table = doc.add_table(rows=1, cols=2)
table.rows[0].cells[0].text, table.rows[0].cells[1].text = "Region", "Revenue"
for _, r in summary.iterrows():
    cells = table.add_row().cells
    cells[0].text, cells[1].text = r["Region"], f"{r['amount']:,.0f}"
doc.save("output/summary.docx")

6. Send the report by email

With classic Outlook on Windows you can drive Outlook itself (pip install pywin32):

import win32com.client as win32
from pathlib import Path

ol = win32.Dispatch("Outlook.Application")
mail = ol.CreateItem(0)
mail.To = "[email protected]"
mail.Subject = "Monthly sales summary"
mail.Body = "Please find this month's summary attached."
mail.Attachments.Add(str(Path("output/summary.docx").resolve()))
mail.Display()   # change to mail.Send() once you trust it

7. Run it automatically

1. Create a file run.bat next to the script: cd /d C:\Automations\sales && python report.py >> log.txt 2>&1
2. Open Task Scheduler → Create Basic Task, choose Daily/Weekly and point it at run.bat.
3. Check log.txt after the first scheduled run.

8. Make scripts robust

• Log what happened (print to a log file, or the logging module).
• Validate inputs: stop with a clear message if an expected file or column is missing.
• Never overwrite originals: write to an output folder.
• Keep settings at the top of the script (paths, email addresses) so others can change them safely.

9. What to automate first

Not sure Python is the right tool? Compare options in which automation tool should you learn.