Py LearnPythonForExcelThe Excel-user's path to Python
Home › Course › Module 07 › Reading Excel files with pandas

Reading Excel files with pandas

Module 07 · Working with Files8 min readBeginner

What you'll learn

  • Read a specific sheet of an Excel file
  • Read multiple sheets at once
  • Write a DataFrame back to .xlsx

The basic read

import pandas as pd
df = pd.read_excel("monthly_report.xlsx")

By default this reads the first sheet.

Pick a specific sheet

df = pd.read_excel("workbook.xlsx", sheet_name="January")
df = pd.read_excel("workbook.xlsx", sheet_name=2)   # by index, 0-based

Read every sheet into a dict

sheets = pd.read_excel("workbook.xlsx", sheet_name=None)
# sheets is a dict: {"January": df1, "February": df2, ...}

for name, df in sheets.items():
    print(name, len(df))

Skip header rows / pick a range

pd.read_excel("workbook.xlsx",
              sheet_name="Summary",
              skiprows=3,            # skip the title block
              usecols="B:F",         # read columns B through F
              nrows=50)

Write back

# One sheet
df.to_excel("output.xlsx", index=False, sheet_name="Results")

# Multiple sheets
with pd.ExcelWriter("output.xlsx") as writer:
    monthly.to_excel(writer, sheet_name="Monthly", index=False)
    yearly.to_excel(writer,  sheet_name="Yearly",  index=False)

Walkthrough: combine 12 monthly tabs into one

Read every sheet

sheets = pd.read_excel("2026_monthly.xlsx", sheet_name=None)

Tag each with its month and stack

frames = []
for name, df in sheets.items():
    df["month"] = name
    frames.append(df)

combined = pd.concat(frames, ignore_index=True)
print(combined.shape)

Save the combined version

combined.to_excel("2026_combined.xlsx", index=False, sheet_name="All")
💡 The dependency
pandas uses openpyxl under the hood for .xlsx. Anaconda includes it; if you used a different install: pip install openpyxl.

Key takeaways

  • pd.read_excel() with sheet_name=... picks one sheet; sheet_name=None returns all as a dict.
  • ExcelWriter writes multiple sheets to one file.
  • Combine many sheets with pd.concat([...]).

Sheet inventory

Write a function inventory(path) that returns a DataFrame with one row per sheet in a workbook, with columns: sheet_name, n_rows, n_cols.

📹 Video walkthrough
A video walkthrough of this lesson will be embedded here. Until then, the written walkthrough above mirrors what the video will cover step-for-step.