Course contentsShow
← Data Science
Lesson 1127 of 2,14529. Data Wrangling WorkflowsFree lesson

Excel Files: Sheets, Ranges, and Formats

Read Excel files with pd.read_excel(), handle multiple sheets, cell ranges, and merged cells.

Excel Files: Sheets, Ranges, and Formats

Unlike CSV files with a single, flat structure, Excel files are multi-dimensional workbooks. They contain multiple sheets, complex formatting, merged cells, and empty report headers. Think of an Excel file as a layered notebook, whereas a CSV is just a plain sheet of text.

To handle these files accurately in pandas, pd.read_excel() provides a suite of parameters to target precise data regions:

  • sheet_name: Selects the specific tab by name or 0-based index.
  • skiprows: Bypasses the top rows, which is crucial for skipping visual report titles and empty space.
  • nrows: Limits the import to a specific number of data rows, stopping before unwanted summary footers.
  • usecols: Accepts Excel-style column ranges (like "A:C") to exclude irrelevant side columns.

Additionally, Excel files frequently feature merged cells (for example, a single "Category" label spanning multiple rows). When pandas reads merged cells, it extracts the value into the top-left cell and fills the remaining cells with NaN. We can repair these structural blanks using forward filling (.ffill()). It is critical to use forward-fill only to repair structurally grouped layouts like merged cells, rather than to invent missing values in actual recorded data.

Below, we first build a sample workbook simulating a formatted report, and then cleanly extract exactly the data we need.

import pandas as pd

# 1. Build a sample workbook with openpyxl
# We simulate merged 'Category' cells by leaving grouped rows as None
data = {
    "Category": ["Electronics", None, "Furniture", None],
    "Item": ["Laptop", "Mouse", "Desk", "Chair"],
    "Sales": [1200, 25, 450, 150],
    "Notes": ["Top seller", "Accessory", "Wood", "Ergonomic"]
}
df_setup = pd.DataFrame(data)

# Save to Excel, shifting down 2 rows to simulate a report title space
with pd.ExcelWriter("sales_report.xlsx", engine="openpyxl") as writer:
    df_setup.to_excel(writer, sheet_name="Q1_Sales", startrow=2, index=False)

# 2. Read the specific cell range and sheet precisely
# Skip the 2 blank header rows, read exactly 4 data rows, and grab only columns A to C
df = pd.read_excel(
    "sales_report.xlsx",
    sheet_name="Q1_Sales",
    skiprows=2,
    nrows=4,
    usecols="A:C"
)

print("Before forward-fill:")
print(df)

# 3. Repair structurally blank cells
# We use ffill() to propagate the category labels down.
df["Category"] = df["Category"].ffill()

print("\nAfter forward-fill:")
print(df)

Key Takeaway: Use sheet_name, skiprows, nrows, and usecols to surgically extract data from complex Excel workbooks, and use df["Column"].ffill() specifically to repair structurally merged cells without fabricating actual missing data.