Loading an Excel Workbook
pd.read_excel loads a sheet of an Excel workbook into a DataFrame with openpyxl doing the reading, sheet_name picks the sheet by its name, skiprows steps over title rows above the table, and sheet_name=None loads every sheet at once as a dictionary of DataFrames.
Apprentice · Python · Updated
After this lesson you can
- Load one sheet of an Excel workbook with pd.read_excel, picking it with sheet_name and stepping over title rows with skiprows
- Load every sheet of a workbook with sheet_name=None, and take one sheet's DataFrame from the dictionary by its name
Key takeaways
- pd.read_excel reads an .xlsx workbook into a DataFrame. pandas hands the reading to openpyxl, which has to be installed in your virtual environment.
- With no sheet_name, you get the first sheet. sheet_name="2026" picks a sheet by its name, while a number such as 1 picks it by position, counting from 0.
- Title rows above a table end up as column names such as 'Unnamed: 1'. skiprows=2 steps over two of them, so the real header row becomes the column names.
- sheet_name=None loads every sheet, as a dictionary whose keys are the sheet names and whose values are DataFrames.
Offices, schools and clubs keep a lot of their data in Excel: a class roster, a budget, a sign-up sheet a coordinator has kept for years. Those workbooks are made for people to read, with a title on top and a tab for each year, so loading one into pandas means telling it which tab to read and where the table really starts.
The example is Sunnyside Farmers Market's vendor list, vendors.xlsx, kept by the market's coordinator: one sheet for each season, 2025 and 2026, with the market's vendors and their stalls. The market is our own made-up data. Here is the top of the 2026 sheet as a spreadsheet shows it, with its row numbers and column letters:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sunnyside Farmers Market: vendors for the 2026 season | ||||
| 2 | Saturdays, 8 a.m. to 1 p.m., Sunnyside Park | ||||
| 3 | vendor_id | vendor_name | stall | category | joined |
| 4 | V01 | Green Acre Greens | A1 | produce | 2019-05-04 |
| 5 | V02 | Bee Happy Honey | B3 | honey | 2020-06-06 |
Two title rows, then the header row, then one row for each vendor.
A first try with read_excel
pd.read_excel reads a workbook (pandas is imported as pd, read "p d"). Behind the scenes, pandas hands .xlsx files to openpyxl, a library that reads and writes Excel files, so openpyxl must be installed in your virtual environment too. Without it, read_excel stops with an ImportError that says importing openpyxl failed and reminds you to install it with pip.
Here's a first try, with nothing but the file name:
import pandas as pd
raw = pd.read_excel("vendors.xlsx")
raw.columns
Index(['Sunnyside Farmers Market: vendors for the 2025 season', 'Unnamed: 1',
'Unnamed: 2', 'Unnamed: 3', 'Unnamed: 4'],
dtype='str')
Two things went wrong. It read the 2025 sheet, because with no sheet named, read_excel reads the first one. And it took row 1, the title, as the column names: the title became one name, and the empty cells beside it became Unnamed: 1 to Unnamed: 4. The real header row ended up as a row of data. Column names like Unnamed: 1 are the sign of title rows above a table.
The right sheet, from the right row
Two arguments fix both. sheet_name picks the sheet, and skiprows=2 steps over the first two rows, so the third row, the real header, becomes the column names:
vendors = pd.read_excel("vendors.xlsx", sheet_name="2026", skiprows=2)
vendors.head(4)
vendor_id vendor_name stall category joined
0 V01 Green Acre Greens A1 produce 2019-05-04
1 V02 Bee Happy Honey B3 honey 2020-06-06
2 V03 Rise & Shine Bakery C2 baked goods 2018-05-05
3 V04 Petal Patch Flowers A4 flowers 2021-05-01
head(4) shows only the first four rows (A First Look: head, info and describe covers it). The table now starts where it should, and the types came along:
vendors.dtypes
vendor_id str
vendor_name str
stall str
category str
joined datetime64[us]
dtype: object
Excel stores real dates, so joined arrives as dates (datetime64) rather than text, ready for date math later.
Sheet names are text
The sheet is named 2026, and it's tempting to write sheet_name=2026. That's a number, though, and a number means a sheet's position, counting from 0:
pd.read_excel("vendors.xlsx", sheet_name=2026)
ValueError: Worksheet index 2026 is invalid, 2 worksheets found
pandas went looking for the 2,027th sheet. sheet_name=1 would read the second sheet, which happens to be 2026 here, until someone adds a tab in front of it. Naming the sheet as text, "2026", keeps working whatever order the tabs are in. (A wrong name, such as "2024", stops with ValueError: Worksheet named '2024' not found.)
Every sheet at once
To compare seasons, load every sheet in one call with sheet_name=None. The result isn't one DataFrame but a dictionary: each key is a sheet's name, and each value is that sheet's DataFrame.
sheets = pd.read_excel("vendors.xlsx", sheet_name=None, skiprows=2)
print(type(sheets))
print(list(sheets))
<class 'dict'>
['2025', '2026']
skiprows=2 applied to every sheet, which works because both sheets have the same two title rows. Take one sheet out of the dictionary by its name, the same way you read any dictionary:
last_year = sheets["2025"]
print(last_year.shape)
(15, 5)
Fifteen vendors last season too, though not all the same ones.
Check yourself
2 questions to try on the lesson page.
So what
Workbooks are laid out for people, so sheet_name and skiprows tell pandas which tab to read and where the table starts, and a column called Unnamed: 1 is your cue to look.