Skip to content
Bamboo Weekly

pandas read_excel

Read Excel workbooks into data frames — one sheet at a time, or all of them at once.

What read_excel actually gives you

What do you do when the data you need arrived as a spreadsheet? A great deal of the world's public data lives in Excel files, and it arrives shaped for human readers rather than for programs: a title row across the top, a note in the margin, a cover page in front, and the actual table somewhere on the third tab. read_excel copes with all of that, provided you tell it what it is looking at.

The one thing to understand before anything else is that read_excel does not always return a data frame. What comes back depends entirely on sheet_name. Ask for one sheet and you get a data frame; ask for several, or for all of them, and you get a dictionary whose keys are sheet names. That single detail is the source of most of the confusion I see around this function.

Official documentation: pandas.read_excel

If your data is in a CSV file rather than a workbook, read_csv is the page you want — the two functions share most of their arguments, and this page sticks to what is genuinely Excel-specific.

The arguments that earn their keep

pd.read_excel(source,
              sheet_name='Units',        # which sheet; default is the first
              usecols='A:D',             # Excel column letters, or a list of names
              skiprows=3,                # step past a title block above the header
              header=0,                  # which row holds the column names
              skipfooter=20,             # drop trailing notes and source lines
              na_values=['..', '--'],    # what counts as missing
              dtype={'Start year': 'Int64'},  # override a guessed dtype
              nrows=100,                 # peek at a big workbook
              engine='calamine')         # which parser reads the file

Two forms worth knowing before you start guessing:

# What sheets does this workbook even have?
pd.ExcelFile(url).sheet_names

# Read several sheets at once -> a dict of {sheet_name: DataFrame}
pd.read_excel(url, sheet_name=['Units', 'CO2 Parameters'])

# Read every sheet
pd.read_excel(url, sheet_name=None)

usecols is the one argument whose Excel form has no CSV equivalent: alongside a list of column names or positions, it accepts a spreadsheet range as a string, so usecols='A:D' or usecols='A:C,F' means exactly what it means in Excel. That is a real convenience when a sheet has no usable header and you are working from the column letters you can see on screen.

Like read_csv, source can be a path or a URL — Pandas fetches it for you, so the examples below run without downloading anything first.

A worked example, on real data

The Global Coal Plant Tracker is a real workbook with several sheets, and Bamboo Weekly #64 built a puzzle around it. Start by asking what is in the file, rather than assuming:

import pandas as pd

url = ('https://www.bambooweekly.com/content/files/wp-content/uploads/2024/02/'
       'global-coal-plant-tracker-january-2024.xlsx')

pd.ExcelFile(url).sheet_names

['About', 'Units', 'Summaries (excl China)', 'CO2 Parameters']

The default would have given us About — a page of documentation, not data. The real table is in Units, and it has 40 columns when we want six:

df = pd.read_excel(url, sheet_name='Units',
                   usecols=['Country', 'Capacity (MW)', 'Status',
                            'Start year', 'Region',
                            'Annual CO2 (million tonnes / annum)'])

df.head()

     Country  Capacity (MW)        Status  Start year    Region  Annual CO2 (million tonnes / annum)
0    Albania          800.0     cancelled         NaN    Europe                                  3.1
1  Argentina          120.0     operating      2022.0  Americas                                  0.6
2  Argentina          120.0  construction      2023.0  Americas                                  0.6
3  Argentina          375.0     operating      1983.0  Americas                                  2.0
4  Australia          160.0       retired      1969.0   Oceania                                  1.0

That is 13,906 rows, one per coal-fired generating unit on earth.

Now try the fourth sheet, and watch a perfectly ordinary spreadsheet defeat the defaults:

pd.read_excel(url, sheet_name='CO2 Parameters').columns.tolist()

['Parameters Used to Calculate CO2 Emissions',
 'Unnamed: 1', 'Unnamed: 2', 'Unnamed: 3']

Those are not column names. That is a title banner sitting in the first row, spilling Unnamed: placeholders across the rest of the sheet. The real header is on row 3, and below the table are twenty more rows of unrelated notes. Three arguments fix all of it:

pd.read_excel(url, sheet_name='CO2 Parameters',
              skiprows=3, skipfooter=20, usecols='A:B')

                     Coal  Emission factor (kg of CO2 per TJ)
0                 lignite                              101000
1           subbituminous                               96100
2              bituminous                               94600
3              anthracite                               98300
4              waste coal                               94600
5                 unknown                               96100
6        lignite with CCS                               10100
7  subbituminous with CCS                                9610
8     bituminous with CCS                                9460
9        unknown with CCS                                9460

skiprows steps past the decoration, skipfooter drops the trailing notes, and usecols='A:B' takes the two columns I want by their spreadsheet letters. Nothing here needed cleaning afterward.

If you are going to read several sheets out of one workbook, open it once with pd.ExcelFile and hand that object to read_excel instead of the URL. The file is parsed a single time, and each read draws on the same open workbook:

with pd.ExcelFile(url) as xl:
    units = pd.read_excel(xl, sheet_name='Units', usecols=['Country'])
    params = pd.read_excel(xl, sheet_name='CO2 Parameters', skiprows=3, nrows=3)

Where it shows up in Bamboo Weekly

Sixty-one Bamboo Weekly issues read a spreadsheet, which makes read_excel one of the most-used loading functions in the whole archive. sheet_name appears in 33 of those solutions and header in 30, which tells you what real workbooks are like.

Bamboo Weekly #54 used the New York Fed's household debt report, a workbook that alternates data sheets with chart sheets. The solution reads the lot with pd.read_excel(filename, sheet_name=None, header=3, index_col=0) and then filters the resulting dictionary down to the sheets whose names contain "Data" — the clearest example I have of the dictionary being the point rather than an inconvenience.

Bamboo Weekly #20 worked with the World Bank inflation database, where the interesting numbers are spread over several tabs. It passes a list — sheet_name=['hcpi_a', 'ccpi_a', 'ppi_a'] — and gets back a dictionary with exactly those three keys.

Bamboo Weekly #16 hit the placeholder problem head on: the IEA marks missing values with .., which quietly turns a column of prices into text. The solution's first instinct was to filter the rows out and cast the column, and its second — the better one — was na_values=['..'], which fixes the problem at the point of reading.

Bamboo Weekly #31 took on a Census Bureau table with a three-row header, and reads it with header=[3,4,5], nrows=67, na_values='N'. Spreadsheets stack header rows the way read_csv files almost never do, so it is worth knowing that header accepts a list.

Six mistakes people make

Accepting the first sheet without looking. read_excel defaults to sheet zero, which in real workbooks is frequently a cover page or a table of contents. Run pd.ExcelFile(url).sheet_names first; it costs one line and saves a confusing debugging session.

Mistaking the multi-sheet dictionary for a data frame. Pass sheet_name=None or a list, and what comes back is a plain Python dictionary. Calling .head() on it raises AttributeError: 'dict' object has no attribute 'head'. Pick a sheet out of it by name, loop over .items(), or pd.concat the values — but do not expect data frame methods.

Fighting a title block instead of skipping it. If your columns come back as Unnamed: 1, Unnamed: 2, Unnamed: 3, the header is not where Pandas looked. A merged title cell does this, and so does any decorative row. Use skiprows to step past it, or header= to point at the real header row.

Assuming a whole-number column will be an integer. Start year in the coal workbook comes back as float64, showing years as 2022.0, because 3,625 units have no start year and NaN cannot live in an int64 column. Casting with .astype(int) then raises IntCastingNaNError. Ask for the nullable integer type instead, and the blanks survive as <NA>:

pd.read_excel(url, sheet_name='Units', usecols=['Country', 'Start year'],
              dtype={'Start year': 'Int64'}).head(3)

     Country  Start year
0    Albania        <NA>
1  Argentina        2022
2  Argentina        2023

Getting dates as meaningless integers. Excel stores dates as the number of days since 30 December 1899, and if a cell was never given a date format there is nothing for Pandas to recognize. A column of numbers around 45,000 is the tell. Convert it yourself:

pd.to_datetime(pd.Series([45292, 45658, 44927]), unit='D', origin='1899-12-30')

0   2024-01-01
1   2025-01-01
2   2023-01-01
dtype: datetime64[s]

The opposite case is a date stored as text, which parse_dates plus date_format='%b-%y' will handle. I can never remember whether the month is %m or %M either, so I keep strfti.me open and try the code against a real date until the answer looks right.

Reading the whole workbook when you wanted one sheet. sheet_name=None on the coal tracker takes 1.98 seconds; naming the sheet you want takes 0.26. Excel parsing is slow, and the cost scales with how many cells you make it walk.

That last point deserves one correction, because it is easy to guess wrong. usecols does not speed up the read — the parser still walks every cell in the sheet, and on this workbook six columns take 1.83 seconds against 1.86 for all forty. What usecols saves is memory: 2.7 MB instead of 24.2 MB. The argument that actually buys speed is engine. Pandas reads .xlsx with openpyxl by default, and python-calamine is dramatically faster — the same Units sheet takes 1.86 seconds with openpyxl and 0.38 with engine='calamine'. On a workbook you read once, ignore this. On one you reload all morning, it is the first thing to change.

Watch it

Excel parsing is slow enough to be worth attacking directly: What's 2,000x faster than read_excel in Pandas?. And for workbooks that fight back, Cleaning messy Excel data with Pandas. More Pandas videos on my YouTube channel.

Practice it

Work through a read_excel exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/read-excel/

Go deeper

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

If your data is in a text file rather than a workbook, see read_csv. If it is a table on a web page, see read_html.

See it on real data

Below are the 61 Bamboo Weekly exercises that use read_excel on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.