Generate and Edit Excel Files with Python

Generating and editing Excel spreadsheets with Python is one of the most valuable skills for anyone looking to boost productivity at work. Microsoft Excel is the world’s most-used data analysis tool, but manipulating large volumes of information manually is slow and prone to human error. Python offers powerful libraries that turn hours of manual work into seconds of code execution. This practical guide teaches you how to automate your reports and manipulate data professionally, even if you are just starting out in programming.

Why automate Excel with Python?

Python handles large data volumes far better than conventional Excel. If you have ever tried to open a spreadsheet with hundreds of thousands of rows and watched your computer slow to a crawl, you know the problem. With the right code, you process that data in memory efficiently. Another key benefit is reproducibility: once the script is ready, you can run it monthly or daily with a single click, guaranteeing that the format and formulas are always applied consistently. You can even clean messy data in Python before writing it to Excel, combining both workflows.

Key libraries for Excel manipulation

Two main libraries handle .xlsx and .csv files in Python: Pandas for data manipulation and analysis, and Openpyxl for granular control over cells, styles, and formulas. Install both with a single command:

pip install pandas openpyxl

pip install pandas openpyxl

Creating a spreadsheet with Pandas

Pandas uses DataFrames to represent tabular data in memory. Creating a spreadsheet from a dictionary and saving it to Excel is just a few lines:

import pandas as pd

# Creating the data
sales_data = {
    'Product': ['Laptop', 'Mouse', 'Keyboard', 'Monitor'],
    'Quantity': [10, 50, 30, 15],
    'Unit Price': [3500, 80, 150, 900]
}

# Converting to a DataFrame
df = pd.DataFrame(sales_data)

# Saving to Excel
df.to_excel('initial_sales.xlsx', index=False)
print('File saved successfully!')

import pandas as pd

# Creating the data
sales_data = {
    "Product": ["Laptop", "Mouse", "Keyboard", "Monitor"],

Reading and editing existing files

# Read the file we created earlier
df = pd.read_excel('initial_sales.xlsx')

# Create a new Total column
df['Total'] = df['Quantity'] * df['Unit Price']

# Filter only high-stock products
df_filtered = df[df['Quantity'] > 20]

# Display the result
print(df_filtered)

# Read the file we created earlier
df = pd.read_excel("initial_sales.xlsx")

# Create a new Total column
df["Total"] = df["Quantity"] * df["Unit Price"]

Applying styles with Openpyxl

Pandas is great for data, but for visual formatting (bold headers, colored cells) you need Openpyxl directly:

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

# Load the existing file
wb = load_workbook('initial_sales.xlsx')
ws = wb.active

# Style: bold white text on blue background
header_font = Font(bold=True, color="FFFFFF")
fill = PatternFill(start_color="1F4E79", end_color="1F4E79", fill_type="solid")

# Apply style to header row
for cell in ws[1]:
    cell.font = header_font
    cell.fill = fill

wb.save('styled_sales.xlsx')

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

Adding Excel formulas

Openpyxl lets you embed native Excel formulas directly into cells. When the user opens the file in Excel, the formula calculates automatically:

# Insert a SUM formula into cell D6
ws['D6'] = "=SUM(D2:D5)"
wb.save('sales_with_formulas.xlsx')

# Insert a SUM formula into cell D6
ws["D6"] = "=SUM(D2:D5)"
wb.save("sales_with_formulas.xlsx")

Pandas vs Openpyxl: when to use each

For file generation with complex structure and data in one script, combine both: use Pandas to build and save the data, then load it with Openpyxl to apply formatting. To further automate your workflows, see automating Python tasks with GitHub Actions.

Frequently asked questions

Can I work with .csv files too?

Yes. Pandas handles CSV natively with pd.read_csv() and df.to_csv(). No additional library is needed.

How do I write to a specific sheet?

Use the sheet_name parameter in Pandas: df.to_excel('file.xlsx', sheet_name='Sales'). With Openpyxl, use wb['SheetName'] to select a sheet.

Can I generate charts with Python in Excel?

Yes. Openpyxl supports creating bar, line, and pie charts directly in the .xlsx file using its BarChart, LineChart, and PieChart classes.

How do I protect a sheet with a password?

Use ws.protection.sheet = True and ws.protection.password = 'mypassword' with Openpyxl before saving the workbook.

Automating Excel with Python transforms repetitive reporting work into a one-click process. Start with the examples above, adapt them to your real data, and gradually add styling and formulas to produce professional-grade spreadsheets with minimal effort.