How to Automate Excel With Python: Complete Beginner’s Guide

How to Automate Excel With Python: A Practical Guide for Beginners

Microsoft Excel is one of the most widely used tools in businesses. From sales reports and employee records to inventory management and daily operations, many companies use Excel to manage their data.

However, working with Excel manually can take a lot of time.

Imagine receiving hundreds of records every day. You need to clean the data, calculate totals, filter information, apply formatting, and prepare a report. Doing everything manually can become repetitive and increase the chances of mistakes.

This is where Python Excel automation becomes useful.

Python allows you to automate many Excel tasks using simple code. You can read Excel files, update records, create new worksheets, apply formatting, calculate totals, and generate reports automatically.

In this tutorial, you will learn how to automate Excel with Python using beginner-friendly examples and practical business scenarios.

By the end, you will understand how Python can help you save time and improve your daily Excel reporting work.

1. What Is Excel Automation With Python?

Excel automation with Python is the process of using Python programs to perform repetitive Excel tasks automatically instead of doing them manually.

For example, suppose you receive a daily sales Excel file containing:

• Order ID
• Product Name
• Quantity
• Selling Price
• Total Sales

Normally, you might open Excel, calculate the total sales, filter the records, and prepare a summary report manually.

Python can perform these operations automatically.

Common Excel tasks you can automate

1. Reading Excel files.
2. Creating new Excel files.
3. Updating existing records.
4. Adding new worksheets.
5. Cleaning duplicate records.
6. Filtering and sorting data.
7. Applying cell formatting.
8. Calculating totals and averages.
9. Creating daily and monthly reports.
10. Combining multiple Excel files.

Excel automation is especially useful for MIS executives, data analysts, operations teams, accountants, and business professionals.

2. Why Use Python for Excel Automation?

Excel provides many built-in features, but Python becomes useful when you need to repeat the same work regularly or process large amounts of data.

Here are some important benefits.

1. Saves Time

Python can process thousands of rows much faster than manually performing repetitive tasks.

2. Reduces Manual Errors

You can create a script that follows the same steps every time, reducing mistakes caused by repetitive manual work.

3. Handles Multiple Excel Files

Python can combine information from multiple Excel workbooks into one report.

4. Automates Daily Reports

You can create scripts to prepare daily, weekly, or monthly reports.

5. Supports Data Analysis

Python libraries allow you to clean, filter, group, and analyse Excel data.

6. Improves Productivity

Instead of spending hours on repetitive Excel work, you can focus on understanding the data and making better decisions.

3. Python Libraries Used for Excel Automation

Python has several libraries that make Excel automation easier.

The most commonly used libraries are Pandas, OpenPyXL, and XlsxWriter.

A. Pandas

Pandas is a popular Python library used for data analysis and manipulation.

It allows you to:

• Read Excel files.
• Filter records.
• Remove duplicates.
• Calculate totals.
• Group data.
• Combine multiple datasets.
• Export data into Excel.

Installation:

pip install pandas openpyxl

Import Pandas:

import pandas as pd

B. OpenPyXL

OpenPyXL is used to read, create, and modify Excel .xlsx workbooks.

It allows you to:

• Create worksheets.
• Update cell values.
• Apply font styles.
• Change background colours.
• Adjust column widths.
• Add borders.
• Work with formulas and other workbook features.

Installation:

pip install openpyxl

Import OpenPyXL:

import openpyxl

C. XlsxWriter

XlsxWriter is useful for creating new Excel .xlsx files with professional formatting.

It supports:

• Cell formatting.
• Conditional formatting.
• Charts.
• Formulas.
• Worksheet formatting.
• Tables.

Installation:

pip install XlsxWriter

Which library should you use?

Library	Best Use
Pandas	Data cleaning and analysis
OpenPyXL	Reading and editing existing Excel workbooks
XlsxWriter	Creating formatted Excel reports

For beginners, start with Pandas and OpenPyXL. Together, they cover many common Excel automation requirements.

4. How to Read an Excel File Using Python

The first step in Excel automation is learning how to read an Excel file.

Pandas provides the read_excel() function for this purpose.

Suppose you have an Excel file called sales.xlsx.

It contains the following information:

Order ID	Product	Quantity	Price
101	Laptop	2	45000
102	Mouse	5	500
103	Keyboard	3	1200
104	Monitor	2	12000

Python code:

import pandas as pd

df = pd.read_excel("sales.xlsx")

print(df)

Here:

• import pandas as pd imports the Pandas library.
• read_excel() reads the Excel workbook.
• df stores the data in a DataFrame.
• print(df) displays the information.

A DataFrame is a table-like structure containing rows and columns.

Read a Specific Worksheet

If your Excel workbook contains multiple worksheets, you can select one by its name.

import pandas as pd

df = pd.read_excel(
    "sales.xlsx",
    sheet_name="Sheet1"
)

print(df)

Read All Worksheets

You can also read all worksheets from a workbook.

import pandas as pd

all_sheets = pd.read_excel(
    "sales.xlsx",
    sheet_name=None
)

print(all_sheets.keys())

When you use sheet_name=None, Pandas returns a dictionary containing all worksheets as DataFrames.

5. How to Create an Excel File Using Python

Python can also create a new Excel file and save information into it.

For this example, we will use Pandas.

Python code:

import pandas as pd

data = {
    "Employee": ["Rahul", "Amit", "Priya", "Neha"],
    "Department": ["Sales", "Operations", "HR", "Finance"],
    "Salary": [25000, 30000, 28000, 32000]
}

df = pd.DataFrame(data)

df.to_excel(
    "employee_report.xlsx",
    index=False
)

print("Excel file created successfully.")

This code creates a new Excel file named employee_report.xlsx.

The index=False parameter prevents Pandas from adding an extra index column.

Understanding the Output

Employee	Department	Salary
Rahul	Sales	25000
Amit	Operations	30000
Priya	HR	28000
Neha	Finance	32000

This is useful when you want to generate employee records, sales reports, or operational summaries automatically.

6. How to Add a New Worksheet in Excel Using Python

Sometimes you need to add a new worksheet to an existing Excel workbook.

OpenPyXL makes this possible.

Python code:

from openpyxl import load_workbook

workbook = load_workbook("sales.xlsx")

workbook.create_sheet("Monthly Report")

workbook.save("sales.xlsx")

print("New worksheet added successfully.")

This creates a new worksheet called Monthly Report.

Important: load_workbook() opens an existing workbook. Make sure the file exists and is not being modified by another program while you run your script.

If you want to preserve the original workbook, save the modified workbook using a different filename.

7. How to Write Data Into Excel Cells Using Python

OpenPyXL allows you to update individual cells in an Excel worksheet.

Example:

from openpyxl import load_workbook

workbook = load_workbook("sales.xlsx")

sheet = workbook["Sheet1"]

sheet["A1"] = "Order ID"
sheet["B1"] = "Product"
sheet["C1"] = "Quantity"
sheet["D1"] = "Price"

sheet["A2"] = 101
sheet["B2"] = "Laptop"
sheet["C2"] = 2
sheet["D2"] = 45000

workbook.save("updated_sales.xlsx")

print("Excel data updated successfully.")

In this example:

• load_workbook() opens the workbook.
• workbook["Sheet1"] selects the worksheet.
• sheet["A1"] selects a particular cell.
• The assignment operator = updates the cell value.
• save() saves the changes.

Write Data Using Row and Column Numbers

You can also use row and column positions.

from openpyxl import Workbook

workbook = Workbook()

sheet = workbook.active

sheet.cell(row=1, column=1, value="Name")
sheet.cell(row=1, column=2, value="Department")

sheet.cell(row=2, column=1, value="Rahul")
sheet.cell(row=2, column=2, value="Operations")

workbook.save("employee.xlsx")

This is helpful when you are generating Excel reports dynamically.

8. How to Filter Excel Data Using Python

Filtering is one of the most common tasks in Excel.

For example, suppose you have a sales report and want to display only records where the quantity is greater than 2.

Pandas makes this simple.

Python code:

import pandas as pd

df = pd.read_excel("sales.xlsx")

filtered_data = df[df["Quantity"] > 2]

print(filtered_data)

filtered_data.to_excel(
    "filtered_sales.xlsx",
    index=False
)

This code selects records where the Quantity column contains values greater than 2.

Filter Data Based on Multiple Conditions

You can apply multiple conditions.

import pandas as pd

df = pd.read_excel("sales.xlsx")

filtered_data = df[
    (df["Quantity"] > 2) &
    (df["Price"] > 500)
]

print(filtered_data)

Here:

• & means AND.
• | means OR.
• Each condition should be placed inside parentheses.

This is useful for finding records that match specific business requirements.

9. How to Remove Duplicate Records From Excel

Duplicate records can create incorrect reports and calculations.

Python can identify and remove duplicate rows automatically.

Python code:

import pandas as pd

df = pd.read_excel("sales.xlsx")

df = df.drop_duplicates()

df.to_excel(
    "clean_sales.xlsx",
    index=False
)

print("Duplicate records removed.")

The drop_duplicates() method removes repeated rows.

Remove Duplicates Based on a Specific Column

Suppose you want to keep only one record for each Order ID.

import pandas as pd

df = pd.read_excel("sales.xlsx")

df = df.drop_duplicates(
    subset=["Order ID"],
    keep="first"
)

df.to_excel(
    "unique_orders.xlsx",
    index=False
)

The keep="first" option keeps the first matching record.

Always check which duplicate should be retained before cleaning important business data.

10. How to Calculate Total Sales Using Python

You can use Pandas to calculate totals without manually creating Excel formulas.

Suppose your Excel file contains Quantity and Price columns.

First, calculate the total sales for each row.

Python code:

import pandas as pd

df = pd.read_excel("sales.xlsx")

df["Total Sales"] = (
    df["Quantity"] * df["Price"]
)

print(df)

total_sales = df["Total Sales"].sum()

print("Total Sales:", total_sales)

Example Result

Product	Quantity	Price	Total Sales
Laptop	2	45000	90000
Mouse	5	500	2500
Keyboard	3	1200	3600
Monitor	2	12000	24000

Total Sales = 120100

You can save the calculated information into a new Excel file.

df.to_excel(
    "sales_summary.xlsx",
    index=False
)

This is useful for daily sales reports, revenue analysis, and business performance tracking.

11. How to Apply Formatting in Excel Using Python

A professional Excel report should be easy to read.

OpenPyXL allows you to format Excel cells using fonts, colours, borders, and alignment.

A. Apply Bold Formatting

from openpyxl import load_workbook
from openpyxl.styles import Font

workbook = load_workbook("sales.xlsx")

sheet = workbook.active

sheet["A1"].font = Font(bold=True)

workbook.save("formatted_sales.xlsx")

B. Change Cell Background Colour

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

workbook = load_workbook("sales.xlsx")

sheet = workbook.active

fill = PatternFill(
    fill_type="solid",
    fgColor="4472C4"
)

sheet["A1"].fill = fill

workbook.save("formatted_sales.xlsx")

C. Change Font Colour

from openpyxl import load_workbook
from openpyxl.styles import Font

workbook = load_workbook("sales.xlsx")

sheet = workbook.active

sheet["A1"].font = Font(
    bold=True,
    color="FFFFFF"
)

workbook.save("formatted_sales.xlsx")

D. Adjust Column Width

from openpyxl import load_workbook

workbook = load_workbook("sales.xlsx")

sheet = workbook.active

sheet.column_dimensions["A"].width = 20
sheet.column_dimensions["B"].width = 25
sheet.column_dimensions["C"].width = 15

workbook.save("formatted_sales.xlsx")

These formatting options help you create cleaner and more professional Excel reports.

12. How to Combine Multiple Excel Files Using Python

In many businesses, different teams maintain separate Excel files.

For example:

• January sales report.
• February sales report.
• March sales report.

You may need to combine all these files into one master report.

Pandas makes this task easier.

Python code:

import pandas as pd

files = [
    "january.xlsx",
    "february.xlsx",
    "march.xlsx"
]

dataframes = []

for file in files:
    df = pd.read_excel(file)
    dataframes.append(df)

combined_data = pd.concat(
    dataframes,
    ignore_index=True
)

combined_data.to_excel(
    "master_sales_report.xlsx",
    index=False
)

print("All Excel files combined successfully.")

How This Code Works

1. A list stores the names of the Excel files.
2. A loop reads each workbook.
3. The DataFrames are added to a list.
4. pd.concat() combines all the data.
5. The final result is saved as a master Excel report.

This technique is especially useful for monthly reporting and data consolidation.

13. How to Automate Excel Reports Using Python

Now let's create a practical business example.

Suppose you work in an operations or MIS department and receive a daily Excel report containing order details.

Your task is to:

1. Read the daily report.
2. Calculate total sales.
3. Remove duplicate records.
4. Create a summary.
5. Save the final report.

Here is a simple example.

Python code:

import pandas as pd

# Read Excel file
df = pd.read_excel("daily_sales.xlsx")

# Remove duplicate records
df = df.drop_duplicates()

# Calculate total sales
df["Total Sales"] = (
    df["Quantity"] * df["Price"]
)

# Create summary
summary = pd.DataFrame({
    "Metric": [
        "Total Orders",
        "Total Quantity",
        "Total Sales"
    ],
    "Value": [
        len(df),
        df["Quantity"].sum(),
        df["Total Sales"].sum()
    ]
})

# Save final report
with pd.ExcelWriter(
    "daily_sales_report.xlsx",
    engine="openpyxl"
) as writer:

    df.to_excel(
        writer,
        sheet_name="Sales Data",
        index=False
    )

    summary.to_excel(
        writer,
        sheet_name="Summary",
        index=False
    )

print("Daily report generated successfully.")

What Will This Automation Do?

The script creates a new Excel workbook with two worksheets:

Worksheet 1: Sales Data

Contains cleaned records and calculated total sales.

Worksheet 2: Summary

Contains important business metrics, including:

• Total Orders.
• Total Quantity.
• Total Sales.

Instead of preparing the report manually every day, you can run the same script whenever a new source file is available.

14. How to Automate Excel File Processing From a Folder

You may receive several Excel files in one folder and want to process all of them automatically.

Python's pathlib module can help you find Excel files.

Python code:

from pathlib import Path
import pandas as pd

folder_path = Path("reports")

excel_files = list(
    folder_path.glob("*.xlsx")
)

dataframes = []

for file in excel_files:

    df = pd.read_excel(file)

    dataframes.append(df)

if dataframes:

    combined_data = pd.concat(
        dataframes,
        ignore_index=True
    )

    combined_data.to_excel(
        "combined_report.xlsx",
        index=False
    )

    print("All reports processed.")

else:
    print("No Excel files found.")

This script searches for .xlsx files in the reports folder and combines their data.

Important: This example assumes that the Excel files have compatible column structures. If they contain different columns, you may need to clean or standardize them before combining.

15. Real-World Uses of Python Excel Automation

Python Excel automation can be useful across different departments and industries.

1. MIS Reporting

MIS teams regularly prepare reports for management.

Python can automate:

• Daily performance reports.
• Monthly business reports.
• Employee productivity reports.
• Operational performance summaries.
• Data consolidation.

2. Sales Analysis

Sales teams can automate revenue calculations, product performance reports, and monthly sales summaries.

3. HR Management

HR teams can process employee records, attendance data, salary information, and leave reports.

4. Inventory Management

Python can process stock records, identify low-stock products, and prepare inventory summaries.

5. E-commerce Operations

E-commerce teams can use Python to analyse:

• Orders.
• Returns.
• Cancellations.
• Seller performance.
• Delivery reports.
• Product sales.

6. Financial Reporting

Python can help consolidate financial records, calculate totals, and prepare structured reports.

16. Pandas vs OpenPyXL: Which Is Better?

Both libraries are useful, but they are designed for slightly different tasks.

Feature	Pandas	OpenPyXL
Read Excel data	Yes	Yes
Write Excel data	Yes	Yes
Filter records	Very convenient	Possible, but more manual
Remove duplicates	Built-in method	Requires custom logic
Calculate summaries	Very convenient	Requires custom logic
Format individual cells	Limited	Excellent
Change cell colours	Not its main purpose	Yes
Adjust column widths	Limited	Yes
Work with worksheets	Yes	Yes

My recommendation: Use Pandas for data processing, calculations, and cleaning. Use OpenPyXL when you need to control the appearance of an existing Excel workbook.

You can also use both libraries in the same project.

17. Common Mistakes Beginners Should Avoid

When automating Excel with Python, beginners sometimes face common problems.

1. Incorrect File Path

Python cannot read a workbook if the file path is incorrect.

Always check the file name and location.

2. Missing Python Libraries

If Pandas or OpenPyXL is not installed, Python may display a ModuleNotFoundError.

Install the required packages using pip.

3. Wrong Worksheet Name

If you specify a worksheet that does not exist, Pandas or OpenPyXL may raise an error.

Check the exact worksheet name before accessing it.

4. Overwriting Important Files

Writing to an existing filename can replace the previous report.

Use a different output filename or create a backup before making changes.

5. Incorrect Column Names

Python column names must match the names in your Excel file.

For example, Total Sales and Total_Sales are different column names.

6. Excel File Open in Another Program

An Excel workbook may be locked by another application, especially when you are trying to overwrite it.

Close the workbook before saving changes.

7. Reading Large Excel Files

Large workbooks can consume significant memory.

For large datasets, consider processing smaller sections or using a more suitable data storage system.

18. How to Start Learning Excel Automation With Python

If you are a beginner, follow this learning path.

Step 1: Learn Python Basics

Understand variables, lists, dictionaries, loops, conditions, and functions.

Step 2: Understand Excel Basics

Learn rows, columns, worksheets, tables, filters, and formulas.

Step 3: Learn Pandas

Practise reading Excel files, filtering data, removing duplicates, and calculating summaries.

Step 4: Learn OpenPyXL

Understand how to create worksheets, update cells, and format Excel reports.

Step 5: Build Small Projects

Start with a sales report, employee attendance report, or inventory summary.

Step 6: Automate Real Business Tasks

Use Python to process actual reporting workflows and reduce repetitive manual work.

19. Beginner Practice Projects

Here are some projects you can build to improve your Python Excel automation skills.

Project	What You Will Learn
Employee Attendance Report	Read and summarize attendance
Daily Sales Report	Calculate revenue and totals
Inventory Tracker	Identify low-stock products
Duplicate Data Cleaner	Find and remove duplicate records
Monthly MIS Report	Combine multiple Excel files
Employee Performance Report	Calculate productivity metrics
Order and Return Summary	Analyse order and return records

Start with one project and gradually add more features.

Frequently Asked Questions (FAQs)

1. Can Python automate Excel?

Yes. Python can read, write, update, format, filter, and combine Excel files using libraries such as Pandas and OpenPyXL.

2. Which Python library is best for Excel automation?

Pandas is useful for data cleaning and analysis, while OpenPyXL is useful for editing and formatting .xlsx workbooks.

3. Is Python Excel automation difficult for beginners?

No. Beginners can start with simple operations such as reading an Excel file and writing data into a new workbook.

4. Can Python create Excel reports automatically?

Yes. Python can process data, calculate business metrics, and save the results into formatted Excel workbooks.

5. Can Python combine multiple Excel files?

Yes. Pandas provides concat() to combine data from multiple Excel files when their structures are compatible.

6. Can Python remove duplicate rows in Excel?

Yes. Pandas provides the drop_duplicates() method to remove duplicate records.

7. Can Python apply formatting to Excel files?

Yes. OpenPyXL supports cell colours, fonts, borders, alignment, and column width adjustments.

8. Do I need Microsoft Excel installed to use Python?

You generally do not need Microsoft Excel installed to read and write standard .xlsx files using Pandas and OpenPyXL.

9. Can Python automate daily MIS reports?

Yes. Python can read daily data, clean records, calculate totals, and generate summary reports automatically.

10. Is Python better than Excel for automation?

They serve different purposes. Excel is useful for interactive spreadsheet work, while Python is especially useful for repeating complex tasks, processing multiple files, and building automated workflows.

Conclusion

Excel automation with Python is a valuable skill for anyone working with business data, reporting, or data analysis.

Instead of spending hours performing repetitive Excel tasks, you can write Python scripts to read files, clean data, calculate totals, combine workbooks, and generate reports.

In this guide, we explored Pandas, OpenPyXL, Excel file operations, data filtering, duplicate removal, formatting, and practical business automation examples.

Start with simple scripts and gradually build real-world projects.

If you work in MIS, operations, finance, sales, or data analysis, learning Python Excel automation can help you improve productivity and reduce repetitive manual work.

Keep learning and keep automating with ITTech Language!