Automate Excel Using Python in 2025: Step-by-Step Guide

Tanmay Sinha

2 weeks ago

If you’ve ever spent hours manually updating Excel sheets, you know how boring and time-consuming it can be. What if I told you that you could automate all those repetitive tasks with just a few lines of Python code?

In this guide, I’ll show you how to automate Excel using Python in 2025. Whether you’re a beginner or an experienced programmer, you’ll learn how to save hours of work every week. Let’s dive in!

Why Automate Excel with Python?

Excel is a powerful tool, but it has its limits. Here’s why Python is the perfect solution:

Save Time: Automate tasks like data entry, formatting, and report generation.
Avoid Errors: No more typos or formula mistakes.
Handle Big Data: Python can process thousands of rows in seconds.
Custom Workflows: Create solutions tailored to your needs.

What You’ll Need

Before we start, make sure you have:

Python Installed: Download it from python.org.
Libraries: We’ll use pandas for data manipulation and openpyxl for working with Excel files.
An Excel File: Use a sample file like sales_data.xlsx to follow along.

Suggested Read: What If JavaScript Never Existed?

Step 1: Install Python Libraries

First, let’s install the libraries we need. Open your terminal or command prompt and run:

pip install pandas openpyxl

Pandas: A library for data manipulation (think of it as Excel on steroids).
Openpyxl: A library for reading and writing Excel files.

Step 2: Load an Excel File

Let’s start by loading an Excel file into Python.

import pandas as pd  

# Load the Excel file  
df = pd.read_excel("sales_data.xlsx", engine="openpyxl")  

# Display the first 5 rows  
print(df.head())

What’s Happening Here?

pd.read_excel() reads the Excel file into a DataFrame (a table-like structure).
df.head() shows the first 5 rows of the data.

Step 3: Automate Data Entry

Imagine you have a column for Units Sold and another for Price Per Unit. You want to calculate the Total Sales for each row. Here’s how:

# Add a new column "Total Sales"  
df["Total Sales"] = df["Units Sold"] * df["Price Per Unit"]  

# Save the updated file  
df.to_excel("updated_sales.xlsx", index=False)

What’s Happening Here?

We create a new column Total Sales by multiplying Units Sold and Price Per Unit.
df.to_excel() saves the updated data to a new Excel file.

Step 4: Generate Monthly Reports

Let’s say you want to generate a report for January 2025. Here’s how to filter the data and save it to a new sheet:

# Filter January 2025 sales  
january_sales = df[df["Month"] == "January 2025"]  

# Save to a new sheet  
with pd.ExcelWriter("reports.xlsx", engine="openpyxl") as writer:  
    january_sales.to_excel(writer, sheet_name="January Report")

What’s Happening Here?

We filter rows where the Month is January 2025.
pd.ExcelWriter() creates a new Excel file with a sheet named January Report.

Step 5: Automate Formatting

Python can also format your Excel sheets. Let’s make the Total Sales column bold and add a currency symbol.

from openpyxl.styles import Font  

# Load the Excel file  
from openpyxl import load_workbook  
wb = load_workbook("updated_sales.xlsx")  
ws = wb.active  

# Format the "Total Sales" column  
for row in ws.iter_rows(min_row=2, min_col=4, max_col=4):  
    for cell in row:  
        cell.font = Font(bold=True)  
        cell.number_format = "$#,##0.00"  

# Save the formatted file  
wb.save("formatted_sales.xlsx")

What’s Happening Here?

We use openpyxl to load the Excel file and apply formatting.
The Total Sales column is made bold, and numbers are formatted as currency.

Step 6: Schedule Your Script

What if you want to run this script every Monday at 9 AM? Use the schedule library to automate it:

import schedule  
import time  

def automate_excel():  
    # Your automation code here  
    print("Excel automation complete!")  

# Schedule the task  
schedule.every().monday.at("09:00").do(automate_excel)  

# Keep the script running  
while True:  
    schedule.run_pending()  
    time.sleep(1)

What’s Happening Here?

The script runs automate_excel() every Monday at 9 AM.
schedule.run_pending() keeps the script running in the background.

Bonus: Advanced Automation Ideas

Once you’re comfortable with the basics, try these advanced tasks:

Send Automated Emails: Use smtplib to email reports to your team.
Scrape Data: Use BeautifulSoup to scrape data from websites and save it to Excel.
Create Dashboards: Use matplotlib to create charts and embed them in Excel.

FAQs

1. Can I automate Excel on Mac or Windows?

Yes! Python works on both Mac and Windows. Just install the libraries and follow the same steps.

2. What if my Excel file has passwords?

Use the msoffcrypto-tool library to unlock password-protected files.

3. Do I need to know Python to automate Excel?

Not at all! This guide is beginner-friendly, and you can copy-paste the code to get started.

Conclusion

Automating Excel with Python is like having a personal assistant who never gets tired. Whether you’re calculating totals, generating reports, or formatting sheets, Python can do it all in seconds.

By following this guide, you’ve learned how to:

Load and manipulate Excel files.
Automate data entry and report generation.
Format your sheets like a pro.
Schedule scripts to run automatically.

Now it’s your turn! Pick a task you do every day in Excel and try automating it with Python. If you get stuck, leave a comment below, and I’ll help you out.

Happy coding! 🚀

Author
Recent Posts

Tanmay Sinha

Founder & CEO at Mr Programmer

Founder & CEO of MrProgrammer.in | I help Learning Freaks to Step into the World of Programming and Our Mission is to Provide High Quality and Valuable Content to Our Users!

We believe that everyone has the potential to learn to code, and we are committed to making that happen. Our courses are designed to be engaging and interactive, and we offer a variety of learning paths to meet the needs of all learners.

We are also committed to providing our learners with the support they need to succeed, and we offer a variety of resources to help learners learn at their own pace.

If you are interested in learning to code, we encourage you to check out our courses. We are confident that you will find our content to be high-quality, valuable, and makes sense.