Every office in India has at least one person whose Monday morning begins the same way. Open five spreadsheets. Copy data from three of them into a fourth. Apply the same formulas that were applied last week. Format the columns. Generate the summary table. Email it to the manager by 10 AM.
This person is doing work that a Python script can do in forty-five seconds.
The work is not simple in the sense that it requires no intelligence. It requires careful attention, understanding of the data, and consistent execution. But it is repetitive in the sense that the same steps happen every week on the same data in the same format. And repetitive work that follows a consistent pattern is exactly what Python automates best.
This guide walks through how to build that automation practically. Not the concept. The actual code, explained well enough that you understand what each piece is doing and why.
Why This Is More Valuable Than It Sounds
Before getting into the code, it is worth spending a moment on why Excel automation specifically is one of the most practically valuable Python skills a beginner can develop.
Almost every organization in India uses Excel or Google Sheets for something important. Sales reports. Inventory tracking. Financial summaries. HR data. Attendance records. Project tracking. The list is specific to each organization but the pattern is universal: data lives in spreadsheets, someone processes it manually on a schedule, and that processing takes time that could be better spent on the parts of their job that require actual judgment.
Python’s ability to read, process, and write Excel files means that anyone who learns this specific combination of skills can immediately create value in almost any organizational context. They do not need to work in tech. They do not need a data science role. The skill is useful in finance, HR, operations, sales, marketing, and any other function that produces or consumes spreadsheet data.
For students who are learning Python and wondering when it will become practically useful, Excel automation is often the first answer to that question.
What You Need Before Starting
Python must be installed on your machine. Version 3.8 or newer is recommended. If you do not have it installed, python.org has installers for Windows, macOS, and Linux.
Two libraries are needed for this guide. The first is openpyxl, which handles reading and writing Excel files directly, preserving formatting and working with multiple sheets. The second is pandas, which handles data manipulation and makes working with large datasets significantly more efficient.
Install both through the terminal or command prompt:
pip install openpyxl pandasThat is the entire setup. No special software. No paid tools. Just Python and two libraries that are free.
Understanding the Problem Before Writing Code
The most common mistake people make when automating Excel work is jumping straight into code before clearly understanding what they are automating. This produces scripts that solve the immediate problem but break the moment the data format changes slightly, because they were built around specific assumptions that were never made explicit.
Before writing a single line, answer these questions about the task you want to automate.
Where does the data come from? One file or multiple? Does the file name change week to week, or does it always have the same name? Is the data always in the same sheet, or does the sheet name change?
What transformations happen to the data? Is it filtered? Sorted? Aggregated? Are new columns calculated from existing ones?
What does the output look like? A new file? A new sheet in the same file? Specific formatting on certain cells? A summary table?
Who receives it and how? Is it saved to a shared folder? Emailed? Uploaded somewhere?
The answers to these questions are the specification for the script. Writing them down before coding prevents the situation where you build something that works in your test environment and fails on the actual data the first time it runs.
Part One: Reading Excel Data With Python
The foundation of any Excel automation is reading data correctly. This sounds straightforward and mostly is, but there are specific considerations that catch beginners off guard.
The first approach uses pandas, which is appropriate when you need to process data as a table, filter rows, calculate new columns, or aggregate values:
import pandas as pd
df = pd.read_excel('sales_data.xlsx', sheet_name='January')
print(df.head())
print(df.dtypes)
print(df.shape)
print(df.isnull().sum())Running this on any Excel file shows the first five rows, the data type of each column, the total number of rows and columns, and how many missing values exist in each column. These four pieces of information tell you almost everything you need to know about whether the data is in the shape you expected before you start doing anything with it.
A common real-world problem is that dates in Excel files often do not read as dates in Python. They sometimes read as strings, sometimes as timestamps, and sometimes as integers depending on how they were formatted in Excel. This causes failures that are confusing if you are not expecting them:
import pandas as pd
from datetime import datetime
df = pd.read_excel('sales_data.xlsx',
sheet_name='January',
parse_dates=['Order Date', 'Delivery Date'])
df['Order Date'] = pd.to_datetime(df['Order Date'], errors='coerce')
df['Month'] = df['Order Date'].dt.month
df['Week'] = df['Order Date'].dt.isocalendar().week
print(df[['Order Date', 'Month', 'Week']].head(10))The parse_dates parameter tells pandas to try interpreting specified columns as dates. The errors=’coerce’ parameter means that values that cannot be parsed as dates become NaT, which is pandas equivalent of null for dates, rather than causing an error that stops the script.
Reading multiple sheets from the same file is straightforward:
import pandas as pd
excel_file = pd.ExcelFile('quarterly_report.xlsx')
print(excel_file.sheet_names)
all_sheets = {}
for sheet_name in excel_file.sheet_names:
all_sheets[sheet_name] = pd.read_excel(excel_file, sheet_name=sheet_name)
print(f"Sheet '{sheet_name}': {all_sheets[sheet_name].shape[0]} rows")Reading multiple files from a folder and combining them into one DataFrame is one of the most commonly needed operations in report automation, since monthly data is often stored in separate files:
import pandas as pd
import os
from pathlib import Path
data_folder = Path('monthly_reports')
all_dataframes = []
for file_path in data_folder.glob('*.xlsx'):
df = pd.read_excel(file_path)
df['Source File'] = file_path.name
df['Month'] = file_path.stem
all_dataframes.append(df)
print(f"Loaded: {file_path.name} ({len(df)} rows)")
if all_dataframes:
combined_df = pd.concat(all_dataframes, ignore_index=True)
print(f"\nTotal rows combined: {len(combined_df)}")
else:
print("No Excel files found in the folder")Part Two: Processing and Analyzing the Data
Reading data is the input. Processing it is where the actual work of automation happens.
The most common processing operations in report automation are filtering rows that meet certain criteria, calculating new columns from existing ones, aggregating data by category, and ranking or sorting the results.
A complete example that does all of these on a sales dataset:
import pandas as pd
import numpy as np
df = pd.read_excel('sales_data.xlsx')
print("Columns:", df.columns.tolist())
print("Shape:", df.shape)
print("\nMissing values:")
print(df.isnull().sum())
df = df.dropna(subset=['Sales Amount', 'Product', 'Region'])
df['Sales Amount'] = pd.to_numeric(df['Sales Amount'], errors='coerce')
df = df[df['Sales Amount'] > 0]
df['Order Date'] = pd.to_datetime(df['Order Date'], errors='coerce')
df['Month'] = df['Order Date'].dt.strftime('%B %Y')
df['Quarter'] = df['Order Date'].dt.to_period('Q').astype(str)
df['Revenue Category'] = pd.cut(
df['Sales Amount'],
bins=[0, 10000, 50000, 100000, float('inf')],
labels=['Small', 'Medium', 'Large', 'Enterprise']
)
regional_summary = df.groupby('Region').agg(
Total_Sales=('Sales Amount', 'sum'),
Average_Sale=('Sales Amount', 'mean'),
Number_of_Orders=('Sales Amount', 'count'),
Largest_Order=('Sales Amount', 'max')
).round(2)
regional_summary['Rank'] = regional_summary['Total_Sales'].rank(
ascending=False, method='min'
).astype(int)
regional_summary = regional_summary.sort_values('Total_Sales', ascending=False)
product_monthly = df.pivot_table(
values='Sales Amount',
index='Product',
columns='Month',
aggfunc='sum',
fill_value=0
)
print("\nRegional Summary:")
print(regional_summary)
print("\nProduct Monthly Breakdown:")
print(product_monthly)The pivot table at the end produces the kind of cross-tabulation that normally requires manually building a pivot table in Excel, selecting fields, and formatting the output. Python produces it in one function call that runs identically every time on any data that has the same structure.
Part Three: Writing Results Back to Excel With Formatting
This is the part that separates a useful automation from an impressive one. Writing data to an Excel file is straightforward. Writing it in a format that looks professional and is immediately usable by the person who receives it requires openpyxl.
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import (Font, PatternFill, Alignment,
Border, Side, numbers)
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.chart import BarChart, Reference
from datetime import datetime
def create_formatted_report(regional_summary, product_monthly, output_path):
wb = Workbook()
ws_summary = wb.active
ws_summary.title = "Regional Summary"
header_font = Font(name='Calibri', bold=True, size=11, color='FFFFFF')
header_fill = PatternFill(start_color='1F4E79', end_color='1F4E79',
fill_type='solid')
border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
title_cell = ws_summary['A1']
title_cell.value = f"Regional Sales Report — Generated {datetime.now().strftime('%d %B %Y')}"
title_cell.font = Font(name='Calibri', bold=True, size=14, color='1F4E79')
ws_summary.merge_cells('A1:F1')
ws_summary['A1'].alignment = Alignment(horizontal='center')
ws_summary.append([])
headers = ['Region', 'Total Sales (₹)', 'Average Sale (₹)',
'Number of Orders', 'Largest Order (₹)', 'Rank']
ws_summary.append(headers)
header_row = ws_summary[3]
for cell in header_row:
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal='center', vertical='center')
cell.border = border
alt_fill = PatternFill(start_color='D6E4F0', end_color='D6E4F0',
fill_type='solid')
for row_idx, (index, row) in enumerate(regional_summary.iterrows(), start=4):
ws_summary.cell(row=row_idx, column=1, value=index)
ws_summary.cell(row=row_idx, column=2, value=row['Total_Sales'])
ws_summary.cell(row=row_idx, column=3, value=row['Average_Sale'])
ws_summary.cell(row=row_idx, column=4, value=row['Number_of_Orders'])
ws_summary.cell(row=row_idx, column=5, value=row['Largest_Order'])
ws_summary.cell(row=row_idx, column=6, value=row['Rank'])
for col in range(1, 7):
cell = ws_summary.cell(row=row_idx, column=col)
cell.border = border
cell.alignment = Alignment(horizontal='center')
if row_idx % 2 == 0:
cell.fill = alt_fill
if col in [2, 3, 5]:
cell.number_format = '₹#,##0.00'
column_widths = [20, 18, 18, 16, 18, 8]
for col, width in enumerate(column_widths, start=1):
ws_summary.column_dimensions[
ws_summary.cell(row=1, column=col).column_letter
].width = width
ws_summary.row_dimensions[3].height = 25
ws_summary.freeze_panes = 'A4'
chart = BarChart()
chart.type = "col"
chart.title = "Total Sales by Region"
chart.y_axis.title = "Sales Amount (₹)"
chart.x_axis.title = "Region"
chart.style = 10
chart.width = 20
chart.height = 12
last_data_row = 3 + len(regional_summary)
data_ref = Reference(ws_summary, min_col=2, min_row=3, max_row=last_data_row)
categories_ref = Reference(ws_summary, min_col=1, min_row=4, max_row=last_data_row)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)
ws_summary.add_chart(chart, f'H3')
ws_products = wb.create_sheet("Product Breakdown")
ws_products['A1'].value = "Product-wise Monthly Sales"
ws_products['A1'].font = Font(name='Calibri', bold=True, size=14, color='1F4E79')
ws_products.append([])
for row in dataframe_to_rows(product_monthly.reset_index(), index=False, header=True):
ws_products.append(row)
for cell in ws_products[3]:
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal='center')
cell.border = border
for row in ws_products.iter_rows(min_row=4):
for cell in row:
cell.border = border
if cell.column > 1:
cell.number_format = '₹#,##0'
cell.alignment = Alignment(horizontal='right')
for column in ws_products.columns:
max_length = max(len(str(cell.value or '')) for cell in column)
ws_products.column_dimensions[column[0].column_letter].width = max(max_length + 2, 12)
ws_products.freeze_panes = 'B4'
wb.save(output_path)
print(f"Report saved: {output_path}")
return output_pathThis function creates a properly formatted Excel report with a title, colored headers, alternating row colors, currency formatting, frozen panes, and a bar chart, everything that a report received from a human analyst would normally contain. The output is indistinguishable from a manually formatted report except that it takes two seconds to generate instead of thirty minutes.
Part Four: Sending the Report Automatically
The final piece of full report automation is delivery. If the report is sent by email every Monday, the email step can be automated alongside the generation step:
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
from datetime import datetime
import os
def send_report_email(report_path, recipients, sender_email, sender_password):
msg = MIMEMultipart()
msg['From'] = sender_email
msg['To'] = ', '.join(recipients)
msg['Subject'] = f"Weekly Sales Report — {datetime.now().strftime('%d %B %Y')}"
body = f"""Hello,
Please find attached the weekly sales report generated automatically for the week ending {datetime.now().strftime('%d %B %Y')}.
The report contains:
- Regional sales summary with rankings
- Product-wise monthly breakdown
- Visual chart of regional performance
This report was generated automatically. Please contact the team if you notice any discrepancies.
Best regards,
Automated Reporting System"""
msg.attach(MIMEText(body, 'plain'))
with open(report_path, 'rb') as attachment:
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header(
'Content-Disposition',
f'attachment; filename={os.path.basename(report_path)}'
)
msg.attach(part)
try:
server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login(sender_email, sender_password)
server.sendmail(sender_email, recipients, msg.as_string())
server.quit()
print(f"Report sent successfully to {len(recipients)} recipients")
except Exception as e:
print(f"Failed to send email: {e}")For Gmail, this requires generating an app password rather than using the account password directly, which involves enabling two-factor authentication and creating an app-specific password in Google account settings.
Part Five: Putting It All Together
A complete automation script that reads data, processes it, generates a formatted report, and emails it:
import pandas as pd
from pathlib import Path
from datetime import datetime
import schedule
import time
def run_weekly_report():
print(f"Starting report generation: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
try:
df = pd.read_excel('sales_data.xlsx')
df = df.dropna(subset=['Sales Amount', 'Product', 'Region'])
df['Sales Amount'] = pd.to_numeric(df['Sales Amount'], errors='coerce')
df['Order Date'] = pd.to_datetime(df['Order Date'], errors='coerce')
df['Month'] = df['Order Date'].dt.strftime('%B %Y')
regional_summary = df.groupby('Region').agg(
Total_Sales=('Sales Amount', 'sum'),
Average_Sale=('Sales Amount', 'mean'),
Number_of_Orders=('Sales Amount', 'count'),
Largest_Order=('Sales Amount', 'max')
).round(2)
regional_summary['Rank'] = regional_summary['Total_Sales'].rank(
ascending=False, method='min').astype(int)
regional_summary = regional_summary.sort_values('Total_Sales', ascending=False)
product_monthly = df.pivot_table(
values='Sales Amount',
index='Product',
columns='Month',
aggfunc='sum',
fill_value=0
)
output_path = f"reports/Sales_Report_{datetime.now().strftime('%Y%m%d')}.xlsx"
Path('reports').mkdir(exist_ok=True)
create_formatted_report(regional_summary, product_monthly, output_path)
send_report_email(
report_path=output_path,
recipients=['manager@company.com', 'team@company.com'],
sender_email='your_email@gmail.com',
sender_password='your_app_password'
)
print("Report generation and delivery completed successfully")
except FileNotFoundError:
print("Error: sales_data.xlsx not found")
except Exception as e:
print(f"Error during report generation: {e}")
schedule.every().monday.at("09:00").do(run_weekly_report)
print("Report scheduler started. Running every Monday at 9:00 AM.")
run_weekly_report()
while True:
schedule.run_pending()
time.sleep(60)This script, once configured with the correct file paths and email credentials, runs automatically every Monday at 9 AM, processes the latest sales data, generates a formatted Excel report, and emails it to the specified recipients. The entire process that previously took a person thirty to sixty minutes of careful manual work runs in under a minute without human intervention.
What This Skill Is Actually Worth
The ability to automate Excel reporting with Python is valuable in a way that is immediately quantifiable. If a task takes forty-five minutes weekly and Python can do it in one minute, the automation saves forty-four minutes per week, which is roughly three and a half hours per month, forty-four hours per year. For a team of five people each doing similar manual reporting, that is two hundred and twenty hours per year of saved time.
Organizations understand this value clearly. A developer who can identify manual repetitive processes and automate them is demonstrating something that goes beyond technical skill: they are demonstrating the ability to create tangible business value from programming knowledge.
For students who are learning Python and want something concrete to show in an interview, a working Excel automation script is one of the most immediately impressive portfolio pieces possible, not because it is technically complex but because every interviewer has seen the manual version of this problem and immediately understands the value of the automated solution.
A complete Python career guide covering how this skill fits into different career paths is available here: https://www.tuxacademy.org/python-career-guide-beyond-programming-india-2026/
For students in Greater Noida West who want to build these skills with structured guidance and direct feedback from industry experienced trainers, the Python program details are available here: https://www.tuxacademy.org/courses/programming/python-programming-training-course-greater-noida/
Frequently Asked Questions
Does this work with Google Sheets as well as Excel?
Pandas can read Excel files directly with read_excel. For Google Sheets, the process is slightly different and requires using the Google Sheets API with the gspread library. The data manipulation logic is identical once the data is loaded into a DataFrame, but the connection step differs. A separate guide on Google Sheets automation with Python covers this specific workflow.
What happens if the Excel file format changes?
Scripts that read specific column names by name, which this guide uses throughout, will fail if column names change. Building validation at the start of the script that checks that expected columns exist and raises a clear error message if they do not produces scripts that fail with useful error messages rather than cryptic ones when the data format changes.
Can this run on a schedule without keeping the computer on?
Yes. The script can be deployed to a cloud server using AWS EC2, Azure, or Google Cloud, which runs continuously. Alternatively, a small Raspberry Pi or any always-on device can run the scheduler. For very simple cases, Windows Task Scheduler or Linux cron can trigger the script at scheduled times without requiring the script itself to manage scheduling.
How long does it take to learn this from scratch?
Someone with basic Python knowledge, specifically comfort with variables, loops, functions, and file reading, can build a working version of the basic report automation in this guide in two to three days of focused learning. The formatted output and email components add another two to three days. Total time from Python beginner to having a working Excel automation script is approximately two to three weeks of consistent daily practice.
Is openpyxl the only option for Excel formatting in Python?
No. xlsxwriter is an alternative library that some developers prefer for its chart creation capabilities. xlwings allows bidirectional interaction with Excel that is running on your computer, which enables more complex automation scenarios but requires Excel to be installed. openpyxl is the most commonly recommended starting point because it handles the majority of practical Excel automation needs without external dependencies.
Final Thought
The Monday morning Excel report that takes forty-five minutes every week is not a small problem. Across all the organizations in India where someone is doing this manually right now, it represents millions of hours of human time applied to work that could be done in seconds.
Python does not eliminate the need for people who understand the data and know what the report should show. It eliminates the mechanical execution of producing the report once that understanding exists. The person who previously spent forty-five minutes copying data between spreadsheets can spend those forty-five minutes on analysis, interpretation, and the kind of thinking that actually requires a human.
That shift, from mechanical execution to genuine thinking, is what makes Excel automation with Python more valuable than the hours it saves suggests. It changes what the work is, not just how long it takes.
A complete Python roadmap covering the full learning path from fundamentals to career-ready skills is available here: https://www.tuxacademy.org/python-full-course-roadmap-for-beginners/
Call to Action
Build Python skills that produce immediate, demonstrable value from the very first projects you build.
TuxAcademy’s Python program in Greater Noida West is built around exactly this kind of practical, applicable project work. Students leave with automation scripts, data analysis tools, and portfolio projects that solve real problems rather than tutorial exercises that demonstrate only that the tutorial was followed.
Website: https://www.tuxacademy.org/
Course: https://www.tuxacademy.org/courses/programming/python-programming-training-course-greater-noida/
Email: info@tuxacademy.org
Phone: +91-7982029314
Attend a free demo class and build your first working automation in the first session.
Our Location
TuxAcademy is at SA209, 2nd Floor, Town Central, Ek Murti Chowk, Greater Noida West 201009.
Students from Alpha 1 Greater Noida, Cherry County, Amrapali Dream Valley, Gaur City, Sector 16B Greater Noida West, and Techzone 4 find the institute conveniently accessible via the Greater Noida West Link Road passing through Ek Murti Chowk. Students from Sharda University, Galgotias University, Bennett University, and Noida International University reach us via Knowledge Park Metro Station and the Noida-Greater Noida Expressway.