How to Read Excel Files With Pandas: A Step-by-Step Guide

pandas.read_excel() turns a spreadsheet into a DataFrame in a single line. The hard part isn't the read — it's making that read survive a scheduled job, where sheets get renamed, exports prepend junk rows, numbers arrive as text, and files occasionally go missing. This guide builds a reliable reader one capability at a time. Every snippet is runnable: the second step writes a small workbook so you can paste each example and watch it work.

Step 1: Install the parsing engine

pandas doesn't parse Excel itself — it delegates to an engine. openpyxl handles modern .xlsx files. (For legacy .xls, install xlrd==1.2.0; xlrd 2.0 dropped .xls support.)

pip install pandas openpyxl

Step 2: Create a sample workbook

So every example below runs as-is, generate a small workbook with a Raw_Data sheet:

import pandas as pd

sample = pd.DataFrame({
    "Order_ID": [1001, 1002, 1003, 1004],
    "SKU": ["A-100", "B-200", "A-100", "C-300"],
    "Quantity": [3, 1, 5, 2],
    "Unit_Price": [19.99, 49.50, 19.99, 8.75],
    "Region": ["North", "South", "North", "West"],
    "Transaction_Date": ["2024-01-05", "2024-01-06", "2024-01-07", "2024-01-08"],
})
sample.to_excel("sales_data.xlsx", sheet_name="Raw_Data", index=False)

Step 3: Read a sheet into a DataFrame

With no extra arguments, read_excel reads the first sheet and treats row 0 as the header:

import pandas as pd

df = pd.read_excel("sales_data.xlsx")
print(df.head())

Step 4: Target a specific sheet and columns

Real workbooks mix metadata, summaries, and raw data across tabs. Name the tab with sheet_name, and load only the columns you need with usecols to keep memory down on wide files. nrows caps how many rows are read:

df = pd.read_excel(
    "sales_data.xlsx",
    sheet_name="Raw_Data",
    usecols=["Order_ID", "SKU", "Quantity", "Unit_Price"],
    nrows=1000,
)

Step 5: Skip metadata rows and realign the header

Automated exports often prepend a title and a timestamp above the real header row. Recreate that layout in a second sheet, then use skiprows to drop the junk and header to pick the real header:

# Add an "Export" sheet whose header sits below two title rows
with pd.ExcelWriter("sales_data.xlsx", engine="openpyxl", mode="a") as writer:
    pd.DataFrame([
        ["Monthly Export", None, None, None],
        ["Generated 2024-02-01", None, None, None],
        ["Order_ID", "SKU", "Quantity", "Unit_Price"],
        [2001, "A-100", 4, 19.99],
        [2002, "B-200", 2, 49.50],
    ]).to_excel(writer, sheet_name="Export", index=False, header=False)

df = pd.read_excel(
    "sales_data.xlsx",
    sheet_name="Export",
    skiprows=2,   # drop the title and timestamp rows
    header=0,     # the next row becomes the column names
)
print(df.columns.tolist())

Step 6: Control dtypes and parse dates

Excel stores dates as serial numbers and frequently coerces numbers to text. Pin the types you depend on and parse date columns explicitly, so downstream aggregation doesn't silently break:

df = pd.read_excel(
    "sales_data.xlsx",
    sheet_name="Raw_Data",
    parse_dates=["Transaction_Date"],
    dtype={
        "Quantity": "int64",
        "Unit_Price": "float64",
        "Region": "category",
    },
)
print(df.dtypes)

Reading large files efficiently

read_excel loads the entire sheet into memory and has no chunksize option, so for large workbooks the goal is to read less:

• Inspect the tabs without loading any data, then read only what you need:

print(pd.ExcelFile("sales_data.xlsx").sheet_names)

• Pass usecols and sheet_name so you never materialize columns or tabs you won't use.
• For static reference data you read repeatedly, convert it once to Parquet or CSV — both parse faster than .xlsx and can be streamed in chunks.

A reusable, defensive reader

In a scheduled job, the read should fail loudly with a clear message rather than crash with a stack trace. This wrapper checks the file exists, surfaces a missing-engine error in plain language, and falls back to a sibling CSV if one is present:

import pandas as pd
from pathlib import Path

def read_excel_safe(path, **kwargs):
    """Read an Excel file with clear errors and a CSV fallback."""
    path = Path(path)
    if not path.exists():
        raise FileNotFoundError(f"Source not found: {path}")
    try:
        return pd.read_excel(path, engine="openpyxl", **kwargs)
    except ImportError as exc:
        raise RuntimeError("Install the Excel engine: pip install openpyxl") from exc
    except Exception:
        csv_fallback = path.with_suffix(".csv")
        if csv_fallback.exists():
            return pd.read_csv(csv_fallback)
        raise

df = read_excel_safe("sales_data.xlsx", sheet_name="Raw_Data")
print(f"Loaded {len(df)} rows")

Troubleshooting common failures

• ModuleNotFoundError: No module named 'openpyxl' → pandas doesn't bundle an Excel engine; install it with pip install openpyxl.
• ValueError: Excel file format cannot be determined → pass engine="openpyxl", or check the file isn't actually a renamed CSV or HTML export.
• An engine error on a .xlsb file → binary workbooks need a dedicated engine; add engine="pyxlsb" (or engine="calamine").
• Columns misaligned → re-check the skiprows count. Merged header cells often need header=[0, 1] to read as a MultiIndex.

Conclusion

The defensive reader pattern — explicit path check, engine specified, CSV fallback — converts unpredictable import failures into clear, actionable error messages. Once you have that wrapper, the step-by-step additions (sheet_name, usecols, skiprows, dtype) are independent knobs you can tune per file without touching the error-handling skeleton.

Where to go next

Once a single file reads cleanly, the same patterns extend to multi-file and multi-sheet pipelines. For the broader workflow — batching files, validating schemas, and exporting results — see Reading Excel Files with Pandas and the Getting Started with Python Excel Automation overview.