Reading & writing workbooks
Reading Excel files
read_excel, openpyxl and the options that decide what actually lands in your DataFrame.
Start with the full guides
The long-form treatment of reading excel files, with the reasoning the shorter pages assume.
- GuideReading Excel Files with PandasLoad Excel workbooks into pandas DataFrames: pick the engine, target sheets and columns, control dtypes, and read multi-sheet files — with runnable examples.
- GuideExtracting Data from Messy Excel SheetsGet clean tables out of worksheets built for people: shifting banners, several tables per sheet, two-row headers, subtotal rows, hidden rows and status colours — with pandas and openpyxl.
- GuideReading Excel with Polars and ArrowRead and write Excel with Polars and Arrow-backed engines: polars.read_excel, the calamine reader, lazy pipelines, Parquet conversion, and when pandas is still the better tool.
- OverviewGetting Started with Python Excel AutomationLearn how to read, transform, and write Excel files with Python using pandas and openpyxl — the libraries, the pipeline pattern, and runnable starter code.
- GuideUsing openpyxl for Excel File ManipulationA runnable, step-by-step guide to creating, reading, editing, styling, and saving .xlsx files with openpyxl — workbooks, sheets, cells, formatting, and images.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toRead an Excel File from a URL or Bytes in PythonLoad a workbook straight from HTTP, S3, a database blob or an upload: BytesIO, content-type checks, authentication, retries, streaming downloads and caching.
- How-toHow to Read Excel Files With Pandas: A Step-by-Step GuideA copy-paste-runnable walkthrough for reading Excel files with pandas — from a one-line read to sheet and column targeting, header realignment, dtype control, and a defensive reader.
- How-toKeep Leading Zeros When Reading Excel with pandasCustomer IDs, ZIP codes and phone numbers lose their leading zeros in read_excel. Fix it for text cells with dtype, and for numbers that only look padded because of a 00000 format, by reading the format with openpyxl.
- How-toList Sheet Names and Column Headers Without Loading the DataFind out what a workbook contains before reading it: sheet names with pandas and openpyxl, the header row of every sheet with nrows=0, hidden sheets and chartsheets, the used range of each sheet, and a catalogue of a whole folder of files — in milliseconds, not seconds.
- How-toRead a Cell Range Like B3:F20 into a pandas DataFramePull exactly one block of cells out of a worksheet — a table that starts at B3 and ends at F20, with other content around it. Do it with read_excel's usecols, skiprows and nrows, or with openpyxl and an A1 range string, and turn it into one reusable function.
- How-toRead a Named Range into pandasLoad the cells behind an Excel named range into a DataFrame: look the name up with openpyxl, resolve workbook- and sheet-scoped names, handle ranges that span several areas or point at #REF!, and read named ranges from files you cannot edit.
- How-toRead an Excel File That Is Open in ExcelRead a workbook that someone has open in Excel without errors or surprises: why pandas sees the last saved version and not what is on screen, how to take a consistent snapshot copy and retry when the file is locked or caught mid-save, how to tell when a file is open, and how to read the live, unsaved values from the running Excel with xlwings.
- How-toRead Yes/No and TRUE/FALSE Columns as Booleans with pandasExcel sheets store flags as TRUE/FALSE cells, "Yes"/"No" text, Y/N codes, 1/0 or a tick mark. See what read_excel does with each, why true_values quietly skips columns with blanks or odd spellings, and how to convert every flag column into a reliable nullable boolean.
- How-toRead Excel Files Inside a ZIP Archive with PythonRead workbooks straight out of a .zip without unpacking it: list the members, open one with zipfile and pass it to pd.read_excel, loop over every workbook while skipping __MACOSX and lock-file junk, read archives downloaded into memory, handle password-protected ZIPs, and understand the errors pandas gives when handed the archive itself.
- How-toRead Excel Sheets Whose Names Change with pandasFind the right sheet when its name changes every month, differs in case or spacing, moves position or hides behind a hidden tab — by pattern, by date in the name, by visibility or by the columns it contains.
- How-toRead Only Some Rows of an Excel File with pandasRead the first rows, a window in the middle, every nth row or the last rows of an Excel sheet with pandas — nrows, skiprows, skipfooter, and which of them actually saves time.
- How-toRead Specific Columns From Excel With PandasUse read_excel usecols to load only the columns you need by name, index, Excel-letter range, or callable — combine it with sheet_name and dtype to save memory.
- How-toSkip Rows and Set the Header When Reading Excel with pandasRead Excel files with title blocks, banners and multi-row headers using pandas — skiprows, header, nrows, usecols, and finding the header row automatically when it moves.
- How-toUse Converters When Reading Excel with pandasClean values as they are read — account codes with leading zeros, currency text, Y/N flags and mixed date cells — with read_excel converters, and know how they interact with dtype and empty cells.
- How-toUse Nullable Data Types When Reading Excel with pandasStop blank cells turning whole integer and TRUE/FALSE columns into floats: read Excel with dtype_backend="numpy_nullable" or "pyarrow", set Int64 and boolean per column, see why convert_dtypes after the read is too late for booleans, and write the NA values back as empty cells.
- How-toFind the Last Row with Data in openpyxlws.max_row counts formatted empty cells, stale dimensions and cells you merely looked at. Find the real last row with data — for the whole sheet or one column — and append in the right place.
- How-toIterate over Rows and Columns with openpyxlWalk an Excel sheet cell by cell in Python — iter_rows and iter_cols, values_only, bounded ranges, the max_row trap, and building dicts keyed by header name.
- How-toRead a Cell Value From Excel With openpyxlRead Excel cell values with openpyxl: load_workbook, ws['B2'].value, ws.cell(), iter_rows, ranges, and data_only for cached formula results instead of formula text.
- How-toStandardise Excel Column Names with pandasTurn messy Excel headers — stray spaces, line breaks, symbols, duplicates, blanks and dates as headers — into consistent snake_case names, map synonyms from different sources to one canonical schema, and check required columns before processing.
- How-toExtract Hyperlink URLs from Excel Cells with openpyxlpandas reads the text a link displays, never the address behind it. Get the URLs out of Excel cells with openpyxl — inserted links, mailto links, links to other sheets and HYPERLINK formulas — and handle workbooks too large for normal mode.
- How-toExtract Report Header Fields from an Excel Sheet with PythonMany exported reports put the branch, period, currency and run date in label-value pairs above the table. Read those fields with openpyxl wherever they sit, find where the table starts, parse the period into real dates and attach the fields to every row before combining files.
- How-toFind the Header Row in an Excel Sheet with a Shifting LayoutExports that start their table on row 3 one month and row 6 the next break skiprows. Scan for the row that holds your required labels, then read from there — with aliases and column offsets handled.
- How-toRead an Excel Sheet Where Records Run Across ColumnsSome sheets are laid out sideways: field names down column A and one record per column — supplier comparisons, product spec sheets, quotes side by side. Read them with header=None and index_col=0, transpose, fix the column types that transposing mixes up, handle several blocks on one sheet, and write the tidy table back.
- How-toRead Cell Colours and Font Styles as Data with openpyxlWhen a spreadsheet uses green for done and red for blocked, the colour is the data. Read fill colours, bold and strikethrough with openpyxl and turn them into real columns.
- How-toRead Excel with Multi-Row Headers in pandasLoad a sheet whose column headers span two rows — a merged quarter above Revenue and Units — into a MultiIndex, then select, flatten or reshape it into a tidy table.
- How-toRead Multiple Tables from One Excel SheetPull several stacked or side-by-side tables out of a single worksheet with pandas: split on blank rows and columns, find blocks by their header label, or read named Excel tables.
- How-toSkip Hidden and Filtered Rows When Reading Excelpandas reads every row, including the ones hidden by a filter or collapsed in a group. Use openpyxl to find hidden rows, columns and sheets, and read only what the user can see.
- How-toStop Reading at Notes and Footers Below the DataMany Excel reports put a total row, a blank line and footnotes such as "Source:" or "* provisional" under the table, and pandas reads them all as data. Find where the table really ends — the first blank row, the first row whose key does not look like a key, or a footer marker — read exactly that many rows, keep the footnotes as metadata, and turn footnote markers inside cells into a flag column.
- How-toTell Blank Cells, Empty Strings and Zeros Apart in Excel DataA cell that looks empty in Excel can be truly empty, a formula returning "", a typed space, a zero hidden by its number format, or a placeholder like N/A — and pandas collapses most of them into NaN. See what read_excel and openpyxl actually return for each, classify every cell, and choose a policy before you count or fill blanks.
- How-toLoad an Excel File into a SQL Database with pandasTake a spreadsheet people filled in and get it into a table safely: normalise the columns, pin the types, stage the rows, validate them, then merge in one transaction that either succeeds or leaves nothing behind.
- How-toRead an Excel File with polars.read_excelRead xlsx into Polars: pick sheets, set the header row, control column types with schema_overrides, handle dates and nulls, and convert to pandas when you need to.
- How-toRead Multiple Excel Sheets with PolarsRead the month sheets of a workbook into one Polars DataFrame — list sheet names without loading data, pick sheets by pattern, force consistent column types across sheets, stack them with the right concat mode, turn sheet names into dates and repeat across many workbooks.
- How-toSpeed Up pandas Excel Reads with the calamine EngineSwap openpyxl for calamine in pandas.read_excel: install it, measure the difference, understand the trade-offs, and know which reads it will not help.
- How-toValidate Excel Columns Before Import with pandasCheck an incoming workbook's sheet, headers, column order and required fields with pandas before a single row reaches your pipeline — with clear, actionable failure messages.
- How-toHandle the 1904 Date System in Excel Files with PythonSome workbooks count dates from 1 January 1904 instead of 1900, so the same serial number means a date four years and one day later. Detect which system a file uses with openpyxl, convert raw serial numbers with the right origin, combine files that use different systems, and write workbooks in either system.
- How-toRead a Large Excel File in Chunks with pandaspandas has no chunksize for Excel — build one with an openpyxl row generator, aggregate chunk by chunk, and keep peak memory flat on files with hundreds of thousands of rows.
- How-toReduce Memory When Reading Excel with pandasTwo different numbers decide whether a big Excel file fits in memory — the peak while the engine parses the workbook and the size of the DataFrame you keep. Measure both, then cut them with usecols, categorical and smaller numeric types, the right engine and, when even that is too much, streaming rows with openpyxl.
- How-toSpeed Up openpyxl with read_only ModeHow openpyxl's read_only mode streams a worksheet instead of building it, what values_only=True saves, the limits you accept in exchange, and the file handle you must close.
- How-toTake a Random Sample of Rows from a Large Excel FilePull a fair random sample from an Excel sheet too big to load comfortably: reservoir sampling in one streaming pass with a fixed seed, a stratified sample that keeps every region represented, the pandas skiprows trick when you know the row count, why "the first 10,000 rows" is not a sample, and checks that the sample's figures match the full file.
- How-toRead an Excel File from SharePoint or OneDrive into pandasDownload a workbook from a SharePoint library or a user's OneDrive with Microsoft Graph and read it straight into pandas from memory — resolving sites and drives, skipping unchanged files, and handling whole folders.
- How-toRead and Write Excel Online Ranges with Microsoft GraphEdit a workbook in OneDrive or SharePoint in place: read a range or the used range into pandas, write a DataFrame back with the right address, send dates and number formats correctly, and avoid the null-means-skip trap.
- How-toRead Untrusted Excel Files Safely in PythonAccepting .xlsx uploads from customers or the web? Check what the file really is, reject decompression bombs before parsing, make sure openpyxl uses defusedxml, never trust cached formula results, spot macros and external links, and parse in a separate process with memory and time limits.
- How-toRead an Excel File Directly from Amazon S3 with pandasLoad a workbook from S3 into a DataFrame without a temporary file: BytesIO reads, paginated prefix scans, readable error translation, and a Parquet cache for repeat runs.
- How-toRead Cell Values as Displayed in Excel with Pythonopenpyxl and pandas return the stored value — 1234.5 — not the text Excel shows, such as (1,235) or 05/03/2026. Get the displayed text two ways — exported by LibreOffice, or formatted in Python by a small function that handles common number and date codes — and check them against each other.
- How-toRead an Excel Table by Name into pandaspandas has no option to read an Excel table by its name, but openpyxl knows every table's exact range. Find a table anywhere in the workbook, skip its totals row, get formula results, and load it into a DataFrame.
- How-toRead and Write Cell Ranges with win32comMove data between Python and Excel in single calls: block reads, the nested shape writes require, sizing ranges with End(xlUp), and Value versus Value2.
- How-toRead and Write a Live Excel Workbook with xlwingsDrive the open application rather than the file: connect to a running workbook, read and write ranges, force a recalculation, read what the formulas produced, and clean up so no invisible Excel is left behind.
- How-toConvert Fixed-Width Text Files to Excel with pandasRead fixed-width exports from banks, payroll and mainframe systems with pandas.read_fwf and write them to Excel: column positions from the layout document, leading zeros kept, implied decimals, header, detail and trailer records in one file, record counts checked against the trailer, and the layout kept in a spreadsheet.
- How-toRead and Write .ods Files with PythonHandle OpenDocument spreadsheets from Python with pandas and odfpy — reading sheets, writing output, the formatting limits, and converting .ods to .xlsx when you need more.
- How-toRead Apple Numbers Files in Pythonpandas cannot open .numbers files, but the numbers-parser package can: list the sheets and tables inside a Numbers document, turn a table into a DataFrame without the padding of empty rows and columns, read typed values and formatted text, handle several tables per sheet, convert a whole document to .xlsx with LibreOffice, and write a Numbers file back.
- How-toRead European CSV Exports with Semicolons and Decimal CommasCSV files saved by European Excel and ERP systems use semicolons between fields, commas as decimal points, dots as thousands separators and a Windows code page. Read them into pandas correctly — and avoid the thousands option silently turning dates like 31.01.2026 into the number 31012026.
- How-toRead .xls Files in Python with xlrd and pandasOpen legacy .xls workbooks from Python: install xlrd 2.x, read sheets into DataFrames, decode the date serials xlrd returns, and handle the 65,536-row ceiling.
- How-toRead Binary .xlsb Excel Files in PythonRead Excel Binary Workbooks (.xlsb) with pandas — the pyxlsb and calamine engines, why dates arrive as numbers with pyxlsb, how error cells look, reading sheet by sheet with pyxlsb directly, and converting .xlsb to .xlsx.
- How-toHandle Merged Cells When Reading Excel with pandasMerged cells read as one value and a run of blanks. Fix them in pandas with forward fill, detect merges with openpyxl, and unmerge a workbook before importing it.
- How-toRead Excel Rows into Dictionaries with openpyxlTurn each row of a sheet into a dictionary keyed by the header — the csv.DictReader pattern for .xlsx files. Stream rows with iter_rows(values_only=True), skip blank rows, clean up missing and duplicate headers, build lookups keyed by ID, and load rows into dataclasses without pandas.
- How-toRead Formula Results with openpyxl data_onlyWhy openpyxl returns None for formula cells with data_only=True, how cached values really work, and four reliable ways to get the numbers a workbook is supposed to contain.
- How-toRead All Sheets from an Excel File into DataFramessheet_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.
Compare the options
Which tool or approach to pick, and why.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
- FixFix Schema and Type Errors When Reading Excel with PolarsPolars reads Excel fast, but its type inference can turn mismatched cells into nulls without a word. This page shows how a code like A-77 or a text date silently disappears, how infer_schema_length and schema_overrides change that, how to read raw text and cast with a report of every failure, and how to handle blank and duplicate headers, empty sheets and mixed date formats.
- FixFix BadZipFile When Reading an Excel File in Pythonzipfile.BadZipFile: File is not a zip file — why openpyxl and pandas raise it, how to identify the real format, and how to recover the data or repair the workbook.
- FixFix the "Data Validation extension is not supported" Warning in openpyxlWhat openpyxl's "Data Validation extension is not supported and will be removed" warning means, when it is harmless, how to silence it narrowly, and how to rebuild the dropdowns it drops before you save the workbook.
- FixFix Duplicate Column Names When Reading Excel with pandasWhen a sheet has two columns with the same header, pandas renames the second to "Sales.1", and near-duplicates such as "Sales " and "sales" slip through as separate columns. Find every duplicate, decide what each column really is, give them unique names from a two-row header or an explicit mapping, and stop surprises such as to_dict silently dropping data.
- FixFix Formula Cells Reading as NaN (or 0) in pandaspd.read_excel returns NaN — or a suspicious 0 — for every formula cell in a workbook generated by a script, while the same file shows the right numbers in Excel. The cause is a missing cached result, not a bad read. Detect it, then fix it by recalculating with LibreOffice, computing the values in pandas, or writing cached results when the file is created.
- FixFix KeyError from Spaces in Excel Column Namespandas raises KeyError for a column you can see in the sheet: the heading has a trailing space, a non-breaking space, a line break typed with Alt+Enter, a zero-width character or a different dash. Show the hidden characters with repr and unicodedata, suggest the nearest real column name, normalise the headings once after reading, and make the lookup fail with a useful message.
- FixFix "Missing optional dependency 'openpyxl'" in pandaspandas reads and writes Excel through separate engine packages, and the error means the engine is not importable from the Python that runs your code. Find which interpreter is running, install into that one, and handle the version-too-old variant.
- FixFix read_excel Returning an Empty DataFrameFind out why pandas read_excel returns an empty DataFrame — or one full of NaN — for a file that clearly has data: the wrong sheet read by default, skiprows or header set past the data, usecols pointing at empty columns, skipfooter or nrows trimming everything, and formulas with no saved results; with a diagnostic that inspects the workbook and suggests the right arguments.
- FixFix "Unnamed: 0" Columns When Reading Excel with pandasColumns called "Unnamed: 0", "Unnamed: 3" or "Unnamed: 0_level_1" appear when a header cell is empty. Find which of the four causes produced them and apply the matching fix — index_col, usecols, header rows or a cleanup step.
- FixFix "Usecols do not match columns" in pandas read_excelValueError: Usecols do not match columns, columns expected but not found — and its cousin "Defining usecols with out-of-bounds indices is not allowed" — mean the names or positions you asked for are not in the header pandas actually read. Print the real header, account for title rows, trailing spaces and renamed columns, and select columns with a forgiving callable so a cosmetic change in the source does not break the job.
- FixFix "Workbook contains no default style" Warning in openpyxlopenpyxl warns "Workbook contains no default style, apply openpyxl's default" when a file's stylesheet lacks a Normal style. The data is fine — but the related "no stylesheet" warning can turn dates into numbers. Tell them apart, silence the harmless one precisely and repair the files.
- FixFix "Worksheet index 0 is invalid, 0 worksheets found"pandas raises "ValueError: Worksheet index 0 is invalid, 0 worksheets found" when openpyxl opens an .xlsx and finds no worksheets, even though Excel shows the data. The usual cause is a file from another tool whose internal paths differ in upper and lower case. Diagnose it by comparing the workbook's relationships with the zip contents, then read it with calamine or repair the paths.
- FixFix "XLRDError: Excel xlsx file; not supported" in Pythonxlrd 2.0 reads only legacy .xls files, so any code path that sends an .xlsx workbook to xlrd now fails. Find where xlrd is being chosen, switch that path to openpyxl, and port old xlrd-style code without pinning an outdated version.
Follow it through a track
Prefer an ordered route? These tracks cover reading excel files along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Reading Excel files is part of Reading & writing workbooks.