Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Read All Sheets from an Excel File into DataFrames

sheet_name=None returns a dictionary of every sheet — how to use it, why one ExcelFile beats repeated read_excel calls, how to filter and concatenate sheets, and what it costs on a large workbook.

A workbook with one sheet per month, per region or per site is one of the most common shapes in reporting, and pandas reads all of it in a single call — sheet_name=None returns a dictionary keyed by sheet name rather than a single DataFrame.

The call is easy. What is worth knowing is when it is the wrong tool (a hundred-sheet workbook you only need two sheets from), how to avoid re-parsing the file on every read, and how to combine the sheets without losing track of which row came from where. This guide is part of Working with Multiple Excel Sheets in Python.

What each sheet_name value returns Passing a name or an index returns one DataFrame. Passing a list returns a dictionary containing only those sheets. Passing None returns a dictionary of every sheet in the workbook, keyed by sheet name in workbook order. One argument decides the shape of what you get back sheet_name="Jan" one DataFrame an index works too: sheet_name=0 is the first sheet sheet_name=["Jan","Feb"] "Jan" "Feb" a dictionary with just the sheets you named sheet_name=None "Jan" "Feb" "Mar" every sheet, in workbook order, keyed by name

Prerequisites

Bash
pip install pandas openpyxl

openpyxl is the engine pandas uses for .xlsx. Legacy .xls files need xlrd instead, and the modern xlrd no longer reads .xlsx at all — if you are on a mixed pile of files, convert the old ones first.

Step 1: Build a multi-sheet workbook to read

Python
import pandas as pd

months = {
    "Jan": pd.DataFrame({"region": ["North", "South"], "amount": [120.0, 180.5]}),
    "Feb": pd.DataFrame({"region": ["North", "South"], "amount": [131.25, 175.0]}),
    "Mar": pd.DataFrame({"region": ["North", "South", "East"],
                         "amount": [140.0, 190.75, 60.0]}),
}

with pd.ExcelWriter("year.xlsx", engine="openpyxl") as writer:
    for name, frame in months.items():
        frame.to_excel(writer, sheet_name=name, index=False)
    pd.DataFrame({"note": ["internal working sheet"]}).to_excel(
        writer, sheet_name="_scratch", index=False)

The _scratch sheet is deliberate — real workbooks always have one, and it is the reason "read every sheet" is rarely the whole answer.

Step 2: Read every sheet at once

Python
sheets = pd.read_excel("year.xlsx", sheet_name=None)

print(type(sheets))                 # <class 'dict'>
print(list(sheets))                 # ['Jan', 'Feb', 'Mar', '_scratch']
print(sheets["Feb"])
#   region  amount
# 0  North  131.25
# 1  South  175.00

The dictionary preserves workbook order, and each value is an ordinary DataFrame. Every other read_excel argument still applies and is passed to each sheet — dtype, skiprows, usecols, na_values — which is useful when the sheets share a layout and unhelpful when they do not.

Step 3: Open the file once, parse many times

read_excel parses the whole file on every call. Calling it in a loop over sheet names re-reads the workbook once per sheet, which on a large file is the difference between one second and thirty:

Python
with pd.ExcelFile("year.xlsx") as xls:
    print(xls.sheet_names)                       # cheap: no data parsed yet

    wanted = [s for s in xls.sheet_names if not s.startswith("_")]
    frames = {name: xls.parse(name, dtype={"region": str}) for name in wanted}

print({name: len(f) for name, f in frames.items()})
# {'Jan': 2, 'Feb': 2, 'Mar': 3}

pd.ExcelFile also gives you sheet_names without reading any data, which is what makes the filtering above possible — you can decide which sheets are worth parsing before paying for them. Using it as a context manager closes the underlying file handle, which matters on Windows where an open handle blocks anything else from writing the file.

Repeated read_excel calls versus one ExcelFile Calling read_excel once per sheet opens and parses the entire workbook each time, so the cost multiplies by the number of sheets. Opening one ExcelFile parses the workbook once and each parse call only reads the sheet you asked for. read_excel in a loop parse whole workbook → take "Jan" parse whole workbook → take "Feb" parse whole workbook → take "Mar" cost × number of sheets on a 40-sheet workbook this is the whole runtime one pd.ExcelFile open + parse the workbook once parse("Jan") parse("Feb") parse("Mar") skip "_scratch" one parse, then cheap reads and sheet_names is available before any data is read

Step 4: Combine the sheets, keeping the source

Stacking the sheets is one call, and the only decision is how to record which sheet each row came from:

Python
combined = pd.concat(frames, names=["month", None]).reset_index(level=0)
print(combined.head())
#   month region  amount
# 0   Jan  North  120.00
# 1   Jan  South  180.50
# 2   Feb  North  131.25

Passing the dictionary straight to concat uses its keys as an outer index level, which reset_index(level=0) then turns into an ordinary column. The alternative — assigning the column inside a loop — is equivalent and sometimes clearer:

Python
combined = pd.concat(
    [frame.assign(month=name) for name, frame in frames.items()],
    ignore_index=True,
)

Either way, add the source column. A combined table with no record of which sheet a row came from cannot be checked against the original, and the first time a total looks wrong that is exactly what someone will want to do. Sheets whose columns differ align by name and fill NaN elsewhere, which is usually right — but check the column set first if the sheets were maintained by different people:

Python
column_sets = {name: tuple(f.columns) for name, f in frames.items()}
if len(set(column_sets.values())) > 1:
    print("sheets disagree on columns:", column_sets)

Step 4b: Check the sheets agree before you trust the total

Sheets that look alike rarely are. A month maintained by a different person acquires an extra column, loses one, or renames Amount to Net. concat aligns by name and fills the gaps with NaN, which is the right default and also the reason a quiet mismatch survives all the way to a total:

What concat does when one sheet renamed a column January and February both have region and amount. March calls the same field net. Concatenating produces three columns, with amount empty for March's rows and net empty for the others, so a total over amount silently omits March entirely. March renamed one column, and nothing raised Jan · region, amount Feb · region, amount Mar · region, net concat amount net 120.00 NaN 131.25 NaN NaN 140.00 df["amount"].sum() 251.25, not 391.25 March is simply absent Comparing the column sets before concatenating turns this into an error message rather than a total that is quietly a third too low

The same care applies to dtypes rather than names. A column read as float64 in eleven sheets and as object in the twelfth — because one cell holds "n/a" — concatenates without complaint into an object column, and every later numeric operation on it either fails or silently produces a string. Passing dtype and na_values to the read is the fix, and it costs nothing to apply to every sheet at once:

Python
frames = {name: xls.parse(name, dtype={"region": str},
                          na_values=["", "-", "n/a", "N/A", "TBC"])
          for name in wanted}

The check costs three lines and belongs in any job that reads sheets it does not control. Whether a mismatch should stop the run or be normalised — mapping net onto amount through a rename dictionary — depends on how the workbook is maintained, but the choice should be explicit. A silent NaN column is the one outcome nobody chose.

Step 5: Skip the sheets that are not data

Real workbooks carry cover sheets, notes, lookup tables and working areas. Filter by name pattern, and check the shape before trusting anything:

Python
import re

MONTH = re.compile(r"^(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)$")
REQUIRED = {"region", "amount"}

with pd.ExcelFile("year.xlsx") as xls:
    usable = {}
    for name in xls.sheet_names:
        if not MONTH.match(name):
            continue
        frame = xls.parse(name)
        missing = REQUIRED - set(frame.columns)
        if missing:
            print(f"skipping {name}: missing {sorted(missing)}")
            continue
        usable[name] = frame

print(f"{len(usable)} data sheet(s):", list(usable))

Reporting the skipped sheets rather than dropping them silently is the part that matters. A month that quietly failed the column check is a month missing from the total, and nothing downstream will notice.

Common pitfalls and gotchas

SymptomCauseFix
AttributeError on the resultsheet_name=None returns a dict, not a DataFrameIndex it by name, or concat
Very slow on a big workbookread_excel called once per sheetOne pd.ExcelFile, many parse calls
A sheet is missing from the resultName filter or a typo in the listPrint xls.sheet_names first
Combined frame has surprise NaN columnsSheets disagree on columnsCompare column sets before concatenating
Cannot tell which sheet a row came fromConcatenated without keysconcat(dict) or assign(month=name)
Sheet order differs from the tabsSorted somewhere along the wayDictionaries preserve workbook order; keep it
File locked on Windows afterwardsExcelFile never closedUse it as a context manager
.xls file will not readModern xlrd dropped .xlsx, old files need itInstall xlrd for .xls, or convert to .xlsx

Performance and scale notes

Reading every sheet means parsing every sheet, so the cost scales with total cells, not sheet count — a 40-sheet workbook of small tables is quick; three sheets of 200,000 rows is not. When you need a subset, filter the names first and parse only those; sheet_names is free.

If the workbook is large and you only need a few columns, pass usecols so pandas discards the rest during parsing rather than after. And if the same workbook is read repeatedly by a scheduled job, convert it once to CSV or Parquet per sheet and read from that — Read Large Excel Files in Chunks with pandas covers the trade-offs when a single sheet is the problem.

Conclusion

sheet_name=None gives you every sheet as a dictionary of DataFrames, which is the right call when the workbook is small and every tab is data. Once it is neither, open one pd.ExcelFile, use sheet_names to decide what is worth parsing, parse only those, and combine with the sheet name preserved as a column. Report the sheets you skipped, because a silently dropped month is the failure this pattern actually produces.

Frequently asked questions

What does sheet_name=None return? A dictionary keyed by sheet name, with a DataFrame for each sheet, in workbook order. sheet_name=0 returns one DataFrame, and a list returns a dictionary containing only those sheets.

Why use pd.ExcelFile instead of calling read_excel repeatedly? Each read_excel call parses the whole file again. pd.ExcelFile opens and parses it once, then every parse() call reads from that. On a large workbook the difference is several times faster.

How do I know the sheet names without loading the data? Open the file with pd.ExcelFile and read its sheet_names attribute, or use openpyxl's load_workbook with read_only=True and read wb.sheetnames.

How do I combine every sheet into one DataFrame?pd.concat over the dictionary's values, with keys or an added column so each row still says which sheet it came from.

Up to the parent guide:

Related guides: