Your team shares Excel files with updated numbers every morning. You open each one manually, copy the data into Python, run your analysis, and paste results back. There’s a better way — Python reads and writes Excel files directly, without ever opening Excel.
In this tutorial, you will learn to:
- Install pandas and openpyxl for Excel file operations
- Write pandas DataFrames to .xlsx files with formatting
- Read Excel data back into pandas for analysis
- Work with multiple sheets in a single workbook
- Style Excel headers and auto-adjust column widths with openpyxl
Page Contents
Prerequisites
You should have Python installed and a working understanding of Python basics — variables, loops, and functions. Familiarity with f-strings and string formatting will help with the output sections. No prior Excel automation experience is needed.
What You’ll Need
Install the required libraries:
pip install pandas openpyxlpandas handles data operations. openpyxl is the engine that reads and writes .xlsx files.
Writing an Excel File
Let’s start by creating a financial report and saving it to Excel:
import pandas as pd
# Create sample data
data = {
'Month': ['Jan', 'Feb', 'Mar', 'Apr', 'May'],
'Revenue': [45000, 52000, 48000, 61000, 55000],
'Expenses': [32000, 35000, 31000, 39000, 36000]
}
df = pd.DataFrame(data)
# Calculate profit
df['Profit'] = df['Revenue'] - df['Expenses']
df['Margin'] = (df['Profit'] / df['Revenue'] * 100).round(1)
print("=== Financial Report ===")
print(df.to_string(index=False))Output
=== Financial Report ===
Month Revenue Expenses Profit Margin
Jan 45000 32000 13000 28.9
Feb 52000 35000 17000 32.7
Mar 48000 31000 17000 35.4
Apr 61000 39000 22000 36.1
May 55000 36000 19000 34.5# Save to Excel
df.to_excel('financial_report.xlsx', index=False, sheet_name='Monthly')
print("Saved to financial_report.xlsx")The to_excel() method writes the DataFrame to an .xlsx file. Set index=False to skip the DataFrame’s row numbers from the output.
Reading an Excel File
Reading data back from Excel is just as straightforward. Use pd.read_excel() to load the file into a DataFrame:
# Read the entire file
df = pd.read_excel('financial_report.xlsx', sheet_name='Monthly')
print(df)
# Read all sheets at once
all_sheets = pd.read_excel('financial_report.xlsx', sheet_name=None)
for sheet_name, sheet_df in all_sheets.items():
print(f"nSheet: {sheet_name}")
print(sheet_df)Setting sheet_name=None returns a dictionary where each key is a sheet name and each value is the corresponding DataFrame.
Financial Analysis Summary
Once your data is in a DataFrame, you can run any pandas analysis — aggregations, filtering, group-by operations. Here is a quick summary:
print(f"nTotal Revenue: ${df['Revenue'].sum():,}")
print(f"Total Profit: ${df['Profit'].sum():,}")
print(f"Average Margin: {df['Margin'].mean():.1f}%")
print(f"nBest Month: {df.loc[df['Profit'].idxmax(), 'Month']} (${df['Profit'].max():,})")Output
Total Revenue: $261,000
Total Profit: $88,000
Average Margin: 33.5%
Best Month: Apr ($22,000)Working with Multiple Sheets
Use pd.ExcelWriter as a context manager to write multiple DataFrames to different sheets in a single workbook:
with pd.ExcelWriter('combined_report.xlsx') as writer:
df.to_excel(writer, sheet_name='Monthly', index=False)
summary = pd.DataFrame({
'Metric': ['Total Revenue', 'Total Profit', 'Avg Margin'],
'Value': [f"${df['Revenue'].sum():,}", f"${df['Profit'].sum():,}", f"{df['Margin'].mean():.1f}%"]
})
summary.to_excel(writer, sheet_name='Summary', index=False)
print("Saved multi-sheet workbook!")Sheet Selection Options
| Parameter | What it does |
|---|---|
sheet_name='Sheet1' | Read a specific sheet by name |
sheet_name=0 | Read the first sheet by index |
sheet_name=None | Read all sheets into a dictionary |
sheet_name=['Jan', 'Feb'] | Read multiple specific sheets |
Formatting with openpyxl (Advanced)
For styling Excel files — bold headers, column widths, number formats — use openpyxl directly after pandas writes the data:
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
# Load and format
wb = load_workbook('financial_report.xlsx')
ws = wb.active
# Style header row
for cell in ws[1]:
cell.font = Font(bold=True, color='FFFFFF')
cell.fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid')
# Auto-adjust column widths
for column in ws.columns:
max_length = 0
column_letter = column[0].column_letter
for cell in column:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except TypeError:
pass
ws.column_dimensions[column_letter].width = max_length + 2
wb.save('financial_report_formatted.xlsx')
print("Formatted file saved!")The above loads the workbook pandas created, applies a blue header style, auto-sizes the columns, and saves a new file. openpyxl’s styling documentation covers borders, number formats, and cell alignment in detail.
Common Mistakes / Gotchas
- Forgetting the openpyxl engine — If you get a
ValueError: No engine for filetype 'xlsx', install openpyxl first withpip install openpyxl. pandas requires it for .xlsx files. - Writing to an open file — If you try to read a file that Excel currently has open, openpyxl raises a
PermissionError. Close the file in Excel before reading or writing. - Lost styling on re-save — If you load a formatted workbook with openpyxl and re-save it via pandas, all openpyxl styling is lost. Use openpyxl to write, not pandas, when you need to preserve existing formatting.
- Integer division in Python 3 —
df['Profit'] / df['Revenue']produces a float. Always use/not//for percentage margin calculations, or you’ll get 0.
Practical Example — Monthly Sales Report
Here is a complete end-to-end example that ties everything together — create data, save it, read it back, analyze it, add formatting, and save the final report:
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
# ── 1. Create the report data ──────────────────────────────────────────────
sales_data = {
'Product': ['Widget A', 'Widget B', 'Widget C', 'Widget D'],
'Units Sold': [1200, 850, 2100, 640],
'Unit Price': [29.99, 49.99, 14.99, 99.99],
'Cost per Unit': [12.00, 28.00, 6.50, 55.00]
}
df = pd.DataFrame(sales_data)
df['Revenue'] = df['Units Sold'] * df['Unit Price']
df['Cost'] = df['Units Sold'] * df['Cost per Unit']
df['Profit'] = df['Revenue'] - df['Cost']
df['Margin %'] = (df['Profit'] / df['Revenue'] * 100).round(1)
# ── 2. Save to Excel (pandas handles the heavy lifting) ────────────────────
with pd.ExcelWriter('monthly_sales.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='Sales', index=False)
summary = pd.DataFrame({
'Summary': ['Total Revenue', 'Total Profit', 'Best Seller'],
'Value': [
f"${df['Revenue'].sum():,.2f}",
f"${df['Profit'].sum():,.2f}",
df.loc[df['Revenue'].idxmax(), 'Product']
]
})
summary.to_excel(writer, sheet_name='Summary', index=False)
# ── 3. Style the workbook (openpyxl adds the polish) ──────────────────────
wb = load_workbook('monthly_sales.xlsx')
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
for cell in ws[1]:
cell.font = Font(bold=True, color='FFFFFF')
cell.fill = PatternFill(start_color='2E7D32', end_color='2E7D32', fill_type='solid')
for column in ws.columns:
max_len = max(len(str(cell.value or '')) for cell in column)
ws.column_dimensions[column[0].column_letter].width = max_len + 3
wb.save('monthly_sales_formatted.xlsx')
print("Done! Open monthly_sales_formatted.xlsx")Frequently Asked Questions
Can pandas read .xls files (older Excel format)?
No — .xls is a binary format no longer supported by pandas. You need openpyxl for .xlsx files or the xlrd library for .xls files. The easiest fix is to open the .xls file in Excel and save it as .xlsx.
How do I append data to an existing Excel file?
pandas does not natively support appending to an existing sheet. Use openpyxl to load the workbook, append rows to the worksheet, and save — or use a separate DataFrame merge approach if you’re adding structured data.
Can I read only specific rows or columns from an Excel file?
Yes. pd.read_excel() accepts usecols to select columns by name or index, and nrows to limit how many rows are read. This is useful for large files where you only need a subset of the data.
Summary & Next Steps
You now know how to read and write Excel files using Python — no manual copy-paste required. pandas handles the data side with to_excel() and read_excel(), while openpyxl adds professional styling to headers and columns.
If you’re new to pandas, start with Introduction to Python Programming to build a solid foundation. To learn more about data manipulation beyond Excel, see Python string methods for cleaning textual data in DataFrames.
pandas + openpyxl = automation. The next time a colleague sends a spreadsheet, run your script and skip the manual work entirely.
