# 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 November 19, 2026

By Gregor Dzierzon

## 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:

```python
import pandas as pd

raw = pd.read_excel("vendors.xlsx")
raw.columns
```

```text
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:

```python
vendors = pd.read_excel("vendors.xlsx", sheet_name="2026", skiprows=2)
vendors.head(4)
```

```text
  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:

```python
vendors.dtypes
```

```text
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:

```python
pd.read_excel("vendors.xlsx", sheet_name=2026)
```

```text
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.

```python
sheets = pd.read_excel("vendors.xlsx", sheet_name=None, skiprows=2)
print(type(sheets))
print(list(sheets))
```

```text
<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:

```python
last_year = sheets["2025"]
print(last_year.shape)
```

```text
(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.

## Key terms

- [read_excel()](https://www.ask-codey.com/glossary/read-excel)
- [openpyxl](https://www.ask-codey.com/glossary/openpyxl)

## Related

- [Loading a CSV File](https://www.ask-codey.com/lessons/pandas-read-csv)
- [Loading JSON, Flat and Nested](https://www.ask-codey.com/lessons/pandas-read-json)
- [DataFrames: Rows, Columns and the Index](https://www.ask-codey.com/lessons/pandas-dataframes)

Source: https://www.ask-codey.com/lessons/pandas-read-excel
