Why does pd.read_excel('sales.xlsx') return a KeyError on 'Sheet1'? Why do dates from column D2:D15 show up as floats instead of datetime objects? Why does it work fine in Jupyter but crash in your production script with a PermissionError?
The answer isn’t version mismatches or missing packages — it’s that Excel files aren’t just tables. They’re layered objects: formatting, merged cells, hidden rows, named ranges, and multiple sheets with inconsistent headers. And Python doesn’t care about your Excel ribbon. It only sees the raw XML or OLE structure — unless you tell it exactly how to look.
Quick Answer
Use pandas.read_excel() for simple cases — but always specify sheet_name, dtype, and parse_dates; for complex files (merged headers, multiple tables, password-protected sheets), switch to openpyxl or xlwings to read cell-by-cell like Excel itself does. Never rely on default sheet inference — sheet_name=0 is safer than 'Sheet1'.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| pandas.read_excel() | 1. Install pandas + openpyxl 2. pd.read_excel('data.xlsx', sheet_name='Q3', usecols='A:C', skiprows=2) | Clean, flat tables with consistent headers | Fails on merged cells, formulas, or protected sheets |
| openpyxl (cell-level) | 1. Load workbook: wb = load_workbook('report.xlsx')2. Access sheet: ws = wb['Summary']3. Read A1: ws['A1'].value or range B2:C10 | Files with merged headers, formulas, or custom formatting | No automatic dtype conversion — you’ll parse dates manually |
| xlwings (Excel engine) | 1. Launch Excel app: app = xw.App(visible=False)2. Open book: wb = app.books.open('dashboard.xlsm')3. Read range: wb.sheets[0].range('A1:E20').options(pd.DataFrame).value | Macros, .xlsm files, or when you need Excel’s calculation engine | Windows-only (unless using xlwings-server), slower, requires Excel installed |
| pyxlsb (for .xlsb) | 1. Install pyxlsb 2. readxlsb('archive.xlsb', sheet_name='RawData') | Large binary Excel files (.xlsb) common in finance teams | No support for formulas or charts — only values |
| pandas + ExcelFile | 1. exc = pd.ExcelFile('multi_sheet.xlsx')2. exc.sheet_names → ['Sales', 'Targets', 'Notes']3. df = exc.parse('Sales', header=1) | Reading multiple sheets *once*, then reusing the parsed file object | Still uses same underlying engines — won’t fix parsing bugs |
Method 1 Deep Dive
Let’s say your team drops q3_forecast.xlsx every Friday at 9 a.m. It has three sheets: Actuals, Forecast, and Notes. The Actuals sheet starts at row 4 — rows 1–3 contain title, department name, and a blank line. Column headers sit in row 4: A4 = 'Region', B4 = 'Product', C4 = 'Units Sold', D4 = 'Revenue', E4 = 'Date'. But E5:E12 contains dates like 2024-09-01, 2024-09-08 — and pandas reads them as strings unless told otherwise.
Here’s what *actually* works:
import pandas as pd
df = pd.read_excel(
'q3_forecast.xlsx',
sheet_name='Actuals',
skiprows=3, # skip first 3 rows
usecols='A:E', # only read columns A through E
parse_dates=['Date'], # convert column 'Date' to datetime64
dtype={'Region': 'category', 'Units Sold': 'int32'}
)That skiprows=3 is critical — without it, pandas treats row 4 as the header and shifts all data down. You’ll get NaN in the first row and misaligned types. Also: never use header=4 expecting row 4 to be header — header is zero-indexed, so header=3 means “use row 4”, but skiprows=3 is clearer and less error-prone.
Surprising tip: If your Excel file has filters applied (like AutoFilter dropdowns in row 4), pandas ignores them — but openpyxl will see the filter state. So if your analyst filtered to ‘APAC’ before saving, read_excel() still reads *all* rows. No filtering survives export.
Method 2 Deep Dive
Now imagine exec_summary.xlsx — opened by your CFO. It has merged cells across A1:B2 (“Q3 Performance”), a logo in cell E1, and actual numbers starting at A5. Columns are inconsistent: A5 = 'North', B5 = 'Q3 Target', C5 = 'Q3 Actual', D5 = 'Variance'. But row 6 has no ‘North’ label — it’s merged with A5. Pandas gives up here. Enter openpyxl.
Install it: pip install openpyxl. Then:
from openpyxl import load_workbook
wb = load_workbook('exec_summary.xlsx')
ws = wb['Summary']
# Read merged cell A1:B2 manually
top_left = ws['A1']
value = top_left.value # returns 'Q3 Performance'
# Read data block starting at A5
data = []
for row in ws.iter_rows(min_row=5, max_row=12, min_col=1, max_col=4, values_only=True):
data.append(row)
df = pd.DataFrame(data, columns=['Region', 'Target', 'Actual', 'Variance'])Notice values_only=True — without it, you get Cell objects, not values. And min_row=5 skips the merged title and logo rows. This method also lets you inspect formatting: ws['C6'].number_format returns '$#,##0.00' — useful if you need to preserve currency display logic.
Keyboard shortcut tip: In Excel, press Alt + D + E to open the “Edit Links” dialog — a quick way to check if your .xlsx pulls data from external sources (like SQL or another workbook). If it does, read_excel() won’t refresh those links. Only xlwings can trigger recalculation.
Cheat Sheet
| Task | Code Snippet | Notes |
|---|---|---|
| Read first sheet, skip 2 rows | pd.read_excel(f, skiprows=2) | Always test with nrows=5 first |
| Read specific columns only | usecols='A,C:E' | Saves memory on wide files |
| Force date parsing | parse_dates=['Order Date'], date_parser=lambda x: pd.to_datetime(x, format='%Y-%m-%d') | Fixes ambiguous formats like '01/02/23' |
| List all sheet names | pd.ExcelFile(f).sheet_names | Run this *before* parsing — avoids KeyError |
| Read cell A1 from 'Metrics' sheet | ws = load_workbook(f)['Metrics']; ws['A1'].value | Works even if A1 is merged or formatted |
| Skip blank rows automatically | df.dropna(how='all') | Add after read_excel() — pandas doesn’t auto-skip |
| Handle password-protected files | xlwings only — app.api.Workbooks.Open(..., Password='secret') | No pure-Python solution exists |
