To import an Excel workbook with pandas, use pd.read_excel(): df = pd.read_excel('sales.xlsx', sheet_name='January'). Install pandas and a compatible Excel-reading engine first; for most modern .xlsx files, that engine is openpyxl.
What pandas Excel import does
pandas reads worksheet data into a DataFrame, a table-like object with labeled rows and columns. You can then filter, reshape, summarize, or combine that data with other sources. The function handles common spreadsheet details such as selecting a worksheet, choosing columns, recognizing missing values, and interpreting many date cells.
Importing is not the same as opening a workbook in Excel. pandas does not preserve the workbook’s visual formatting, charts, formulas as editable formulas, or other sheet-level features in a DataFrame. It focuses on the cell values and their tabular structure. If you need to keep the original workbook intact, read it without overwriting it and save any transformed data to a separate file.
Install pandas and an Excel engine
pandas does not include every optional library needed to read every spreadsheet format. For a typical Windows, macOS, or Linux Python environment, install pandas and the engine used for modern Excel workbooks with:
python -m pip install pandas openpyxl
Run the command in the same Python environment that runs your script or notebook. If you use a managed environment such as Anaconda, install packages through that environment’s package manager or its configured terminal so the notebook can see them. An import error for openpyxl usually means it is missing from the active environment, not that the workbook is necessarily damaged.
The appropriate engine depends on the file type. Common choices are:
openpyxlfor.xlsxand.xlsmworkbooks.xlrdfor older Excel.xlsfiles.pyxlsbfor Excel binary.xlsbfiles.odffor OpenDocument spreadsheets such as.ods.
pandas can often select an engine from the file extension, but the required engine still has to be installed. You can specify one explicitly with the engine argument when diagnosing a problem or working with an unusual file. The optional python-calamine engine is another choice for several spreadsheet formats; check the pandas documentation for current format and version support before relying on it.
Read a workbook and choose a worksheet
With the packages installed, import pandas using its customary alias and pass a workbook path to read_excel:
import pandas as pd
df = pd.read_excel('sales.xlsx')
print(df.head())
By default, pandas reads the first worksheet, whose position is zero. Use a worksheet name when the workbook has several tabs, or use a zero-based position when that is more convenient:
january = pd.read_excel('sales.xlsx', sheet_name='January')
second_sheet = pd.read_excel('sales.xlsx', sheet_name=1)
Set sheet_name=None to read every worksheet. The result is a dictionary mapping sheet names to DataFrames, rather than one combined DataFrame. You can also request selected worksheets with a list of names or positions. If you intend to combine sheets, first check that their columns and meanings match; pandas does not automatically know whether two tabs represent compatible records.
Select rows, columns, and header structure
Real workbooks often have title lines, notes, blank rows, or only a few relevant columns. Use skiprows to skip leading rows, nrows to limit how many data rows are read, and usecols to select columns by Excel letters, ranges, names, or a callable. For example:
df = pd.read_excel(
'sales.xlsx',
sheet_name='Orders',
skiprows=2,
usecols='A:D',
nrows=500
)
Here, pandas skips the first two worksheet rows, reads columns A through D, and limits the data to 500 rows after the skipped rows. If the column names are on a different row, set header to that row’s zero-based position in the worksheet. Set header=None when the sheet has no header row; pandas then assigns integer column labels. Use names to provide your own labels, taking care to align their number with the columns being read.
For a workbook with multiple header rows, header can be a list of row positions, producing hierarchical column labels. This can be useful for reports where a top row groups measures beneath broader headings, but it may be easier to flatten those labels after import if later analysis expects simple column names.
Control data types, dates, and missing values
Excel stores cell values in ways that do not always match the data type you want in Python. pandas infers types from the values it finds, so a mostly numeric column containing a text marker may become text or otherwise require cleanup. Use dtype to request a type for selected columns, or converters to apply a function to values as they are read. When both are supplied for the same column, converters take precedence.
df = pd.read_excel(
'customers.xlsx',
dtype={'Customer ID': 'string'},
na_values=['—', 'not recorded']
)
This treats values in the Customer ID column as strings and recognizes the listed markers as missing data. That matters for identifiers that should not be used in arithmetic. However, a leading zero displayed only through Excel’s number formatting is not part of the underlying numeric value. Reading that cell as a string cannot reliably reconstruct a display-only zero; store identifiers as text in the workbook or apply a known formatting rule after import.
Excel date cells are commonly recognized as dates, but text dates can be ambiguous or inconsistent. The parse_dates option can request date parsing for specified columns. If parsing fails or the source contains mixed formats, import the column and convert it deliberately with pd.to_datetime(), using an explicit format when you know one. For numbers formatted with a decimal comma or thousands separator, review the decimal and thousands arguments rather than assuming the file’s locale will be inferred exactly as intended.
Handle messy spreadsheets and merged cells
A worksheet can look like a clean table to a person while containing extra structure that complicates import. Common examples include merged title cells, subtotals between records, footnotes beneath the data, repeated header rows, and formulas mixed with hand-entered values. Inspect the first rows before deciding that every row belongs in the dataset:
preview = pd.read_excel('report.xlsx', sheet_name='Data', header=None, nrows=12)
print(preview)
Reading a small preview with header=None can reveal where the actual column headings start. You can then set skiprows or header appropriately. If repeated headings or totals remain among the records, filter those rows after import using a reliable column condition rather than deleting rows by position without checking the data.
Merged cells are designed for presentation, not rectangular datasets. They can leave blank values in places where a table needs a repeated category. After import, determine whether those blanks should inherit a value from the row above, represent genuine missing information, or mark a section boundary. Do not fill every blank automatically: doing so can turn intentionally empty cells into misleading data.
Formula cells are another frequent source of confusion. pandas reads workbook content through an Excel engine; it does not calculate spreadsheet formulas. A formula’s cached result may be available, depending on how the workbook was last recalculated and how the engine reads it. If results are missing or stale, open and recalculate the workbook in spreadsheet software, or reproduce the calculation in Python. pandas does not return a fully editable Excel formula model in the DataFrame.
Read multiple sheets and larger files efficiently
For a small workbook, calling read_excel once per sheet is straightforward. If you need several tabs from the same file, pd.ExcelFile can open the workbook once and then parse selected sheets:
with pd.ExcelFile('monthly_reports.xlsx') as workbook:
print(workbook.sheet_names)
march = workbook.parse('March')
april = workbook.parse('April')
This also lets you inspect available sheet names before choosing one. It does not turn Excel into a streaming database: ordinary Excel import generally loads the requested worksheet data into memory. For a very large workbook, limit columns and rows with usecols and nrows, and avoid loading every sheet unless necessary. If the source is a repeatable data feed rather than a spreadsheet report, a text or columnar format may be a better fit for large-scale processing.
For files stored online or in cloud-backed locations, pandas can accept supported URLs and file-like objects, but access may depend on authentication, network settings, and optional dependencies. A local path is the simplest starting point when troubleshooting. Confirm that the script can access the exact file path and that the file is not an HTML download or an incomplete export renamed with an Excel extension.
Common import errors and how to fix them
Many failures come from a mismatch between the file, its engine, and the environment. Work through these checks before changing the workbook:
- Missing optional dependency: install the engine required for the file type in the Python environment running the code.
- File not found: verify the spelling, extension, current working directory, and full path. In Python, a raw string such as
r'C:\data\sales.xlsx'can make Windows paths easier to read. - Unsupported or incorrect format: check the actual file type, not just its extension. A CSV or web page renamed to
.xlsxis not an Excel workbook. - Worksheet not found: inspect
workbook.sheet_namesfor exact names, including spaces and punctuation. - Unexpected column names or rows: preview the sheet with
header=None, then adjustheader,skiprows, orusecols. - Permission or lock issue: close applications that may be holding the file, or copy it to a location your process can read.
If a workbook is encrypted, corrupted, or in a format the selected engine cannot parse, changing sheet_name or dtype will not solve the underlying issue. Obtain an accessible, valid copy or export the data in a supported format. Keep the original unchanged while investigating so that cleanup attempts do not destroy source information.
Frequently Asked Questions
How do I import a specific Excel sheet in pandas?
Pass its tab name to sheet_name, as in pd.read_excel('file.xlsx', sheet_name='Data'). You can also pass a zero-based position, such as sheet_name=0 for the first sheet.
How do I read all sheets in an Excel file?
Use pd.read_excel('file.xlsx', sheet_name=None). pandas returns a dictionary of DataFrames keyed by worksheet name; it does not combine the sheets automatically.
Why does pandas say there is no Excel writer or reader engine?
Usually, the optional library for that workbook format is not installed in the active Python environment. Install the appropriate engine, such as openpyxl for a typical .xlsx file, and restart the notebook kernel if needed.
Can pandas import an Excel file without openpyxl?
Yes, if another supported engine is installed and appropriate for that file. For example, older .xls files commonly use xlrd, while .xlsb files use pyxlsb. Engine support depends on the workbook format.
How do I import an Excel file with no header row?
Set header=None in read_excel. pandas will use numbered column labels unless you supply your own with the names argument.
When to use another import method
Use read_excel when the source is genuinely an Excel or supported spreadsheet workbook and you need its worksheet structure. If the workbook is actually a plain comma-separated export, pd.read_csv() is usually more direct and can offer better performance for large text datasets. If you need to preserve formatting, macros, formulas, charts, or workbook layout while editing, pandas is not a full workbook editor; consider a spreadsheet application or a library designed to manipulate Excel files. Choosing based on the data you need—not merely the file extension—helps avoid import surprises.
Related
- Data Validation From a Table in Excel: Setup and Fixes
- Power Query Text to Columns: Split Data Step by Step
- Data Validation Framework: Design, Rules, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Power Query Divide by Zero Error: How to Fix It QuicklyTo fix a divide-by-zero error in Power Query, check whether the divisor is zero or null before dividing,…
- Excel Hyperlink Not Working After Copying? Causes and FixesIf an Excel hyperlink stops working after you copy it, first check whether the paste kept the hyperlink…
- Data Validation Error in Excel: Causes and FixesA data validation error in Excel means an entry does not meet a rule set for that cell,…
- Excel File Open in Another Application Error: FixesIf Excel says a file is open in another application, first close the workbook everywhere it may be…
- Excel Print Area Too Small? Fix Scaling and MarginsIf an Excel printout is too small, first check the print scaling: in Windows Excel, open File >…
- Excel UPPER Function Not Working? Fix Common CausesIf Excel’s UPPER function is not working, first check that the formula is entered as =UPPER(A2), with the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.