Python pandas handles Excel files mainly with read_excel() to load workbook data into a DataFrame and DataFrame.to_excel() to save data back to a workbook. These are pandas methods, not Excel worksheet formulas: they let Python import, inspect, transform, and export spreadsheet data.
What pandas Excel functions do
pandas is a Python library for working with structured data. An Excel worksheet with rows and columns can be represented as a pandas DataFrame, making it possible to filter records, calculate new columns, combine files, check missing values, and prepare reports with Python code. The data can then be written to an Excel workbook for people to review or use in another process.
The main interfaces are:
pandas.read_excel()reads a worksheet or worksheets into one or more DataFrames.DataFrame.to_excel()exports a DataFrame to an Excel workbook.pandas.ExcelFileprovides information about a workbook and can help when reading multiple sheets.pandas.ExcelWritermanages output to a workbook, including writing several sheets and, in supported cases, appending to an existing file.
These functions move tabular data between Python and Excel; they are not designed to reproduce every feature of the Excel application. In particular, pandas is strongest when the important content is the cell data rather than the workbook’s appearance or interactive features.
Reading a worksheet with read_excel()
Start by installing pandas and an Excel-reading engine. For a typical modern .xlsx file, a common setup is pip install pandas openpyxl. Then provide the file path to read_excel(). The following example reads the first worksheet into a DataFrame called df:
import pandas as pd
df = pd.read_excel('sales.xlsx', sheet_name=0)
print(df.head())
By default, pandas treats the first row as column names and reads the first worksheet. You can identify a sheet by its name, such as sheet_name='January', or by its zero-based position, where 0 means the first sheet and 1 the second. Setting sheet_name=None reads all sheets and returns a dictionary whose keys are sheet names and whose values are DataFrames.
Common options let you control what comes in:
usecols='A:D'selects columns A through D; column names or a list of names can also be used when appropriate.skiprows=2skips the first two rows, useful when a sheet begins with a title or notes.nrows=100limits the read to the first 100 data rows.header=Nonesays the sheet does not have a header row, whileheader=1uses the second row as column names.dtype={'PostalCode': 'string'}can preserve values such as postal codes as text rather than numbers.
For example, if a workbook has a heading above the actual table, selecting the correct header row can prevent pandas from interpreting the heading as a column name. Inspect the result with df.head(), df.columns, and df.dtypes before relying on it. Excel cells can contain mixed types, blank entries, or dates stored in different ways, so an imported table may need validation.
Controlling dates, types, and missing values
Excel’s displayed value is not always the same as its underlying type. A date may be stored as an Excel date serial, a text string, or a mixture across rows. pandas usually recognizes standard Excel date cells, but text dates may need conversion. One option is to pass the date column to parse_dates; another is to read the data and then use pd.to_datetime() so that conversion errors can be handled explicitly.
df = pd.read_excel('sales.xlsx', parse_dates=['OrderDate'])
Choose a type intentionally when formatting carries meaning. A product code such as 00127 should often be text because numeric conversion can discard its leading zeros. If an Excel cell contains a number formatted to display leading zeros, importing the numeric value cannot automatically recover those zeros as meaningful characters; use a known format or source rules to rebuild the identifier.
Blank Excel cells are generally represented as missing values, often NaN for numeric data. A blank is not necessarily the same as zero, an empty string, or a label such as N/A. Check how the source workbook uses these values before replacing them. The na_values option can identify additional strings that should count as missing, while keep_default_na controls pandas’ default missing-value markers.
Writing data with to_excel()
After transforming a DataFrame, call its to_excel() method with an output path:
df.to_excel('cleaned_sales.xlsx', index=False)
The index=False setting prevents pandas from adding the DataFrame’s row index as an extra spreadsheet column. By default, to_excel() writes the index, so omitting this option can produce an unexpected first column. The column labels are written by default as well; use header=False only when the output should not include them.
For larger or more specific exports, options include columns to choose which DataFrame columns to write, sheet_name to name the worksheet, and na_rep to choose how missing values appear in the output. The output path’s extension and the installed engine determine the workbook format. Check the saved file and its column order, date display, and missing-value representation, especially when another program will consume it.
Writing a DataFrame to an existing filename in the usual write mode replaces that file’s contents. Do not assume that exporting a DataFrame will update only a few cells while retaining all other workbook content. If the file matters, write to a new path first and compare the result before replacing the original.
Handling multiple sheets and workbook output
Use ExcelWriter when one output workbook needs several DataFrames on separate sheets. The context manager closes and saves the workbook when the block finishes:
with pd.ExcelWriter('report.xlsx') as writer:
summary.to_excel(writer, sheet_name='Summary', index=False)
detail.to_excel(writer, sheet_name='Detail', index=False)
For input, setting sheet_name=None is convenient when all worksheets have similar tabular structure:
sheets = pd.read_excel('monthly_reports.xlsx', sheet_name=None)
for name, frame in sheets.items():
print(name, frame.shape)
When worksheets have different layouts, process them separately rather than assuming every sheet has the same headers and data types. A title block on one sheet, for example, may require different skiprows or header settings.
Appending or replacing a sheet in an existing workbook is possible through ExcelWriter options, but behavior depends on the chosen engine and pandas version. For example, the openpyxl engine supports append mode for suitable workbook formats. An append operation is not the same as safe cell-by-cell editing: confirm the target sheet behavior and keep a backup, especially when the workbook includes formulas, macros, or other important features.
Excel engines and file formats
pandas relies on other Python packages, called engines, to interpret particular spreadsheet formats. The appropriate engine depends on the file type and the operation. For many .xlsx reads, pandas uses openpyxl; xlsxwriter is a common choice for writing new .xlsx files. Older .xls files generally require a compatible reader such as xlrd, while binary .xlsb files have different engine requirements. OpenDocument spreadsheets also use a separate engine.
If pandas reports that an optional dependency is missing, install the package required for that format and operation, then try again. You can specify an engine with the engine argument when automatic selection is unsuitable, for example pd.read_excel('data.xlsx', engine='openpyxl'). Avoid choosing an engine solely by habit: check the file extension, the pandas documentation for your installed version, and whether you are reading or writing.
The file extension matters too. A CSV file is plain text, not an Excel workbook, so use pd.read_csv() to read it and DataFrame.to_csv() to write it. Renaming a CSV file to end in .xlsx does not convert its contents into a real Excel workbook.
What pandas does not preserve or calculate
pandas focuses on cell values and tabular structure. Reading a workbook into a DataFrame and exporting it again generally does not preserve its complete presentation or behavior. Formatting, charts, conditional formatting, data validation, images, and complex workbook layouts may be lost or changed. A DataFrame export is therefore better understood as generating a data workbook than as editing an existing workbook while keeping every feature intact.
Excel formulas are another important distinction. pandas does not calculate formulas. When reading a formula cell, the result available to pandas depends on the cached value saved in the workbook by a spreadsheet application; it may be missing or out of date if the workbook has not recalculated. When pandas writes strings that look like formulas, it does not evaluate them. Use Excel or a compatible calculation tool when fresh formula results are required.
Macro-enabled workbooks need particular care. A normal pandas read-and-write workflow is not a reliable way to preserve VBA macros or all workbook features. If the task is to alter cells while retaining Excel-specific elements, consider a library intended for workbook editing, such as openpyxl, and follow its guidance for macro-enabled files. Make a copy before editing any valuable workbook.
Choosing pandas or another approach
pandas is a good fit when the job is primarily data work: importing rows, cleaning inconsistent values, merging several worksheets, generating summaries, or exporting a repeatable report. Its DataFrame operations can be easier to automate than manual edits when the same steps recur across many files.
Choose another tool when the main goal is different. Use Excel itself for hands-on formatting, formulas, charts, or review by spreadsheet users. Use openpyxl when Python must edit workbook cells or certain workbook features directly, while recognizing that it is not a full Excel calculation engine. For a simple delimited text file, pandas’ CSV functions or Python’s built-in CSV module may be more appropriate. The right choice depends on whether the priority is transforming data, preserving workbook features, or calculating spreadsheet formulas.
Frequently Asked Questions
What is the pandas function to read an Excel file?
Use pd.read_excel('file.xlsx') to read the first worksheet into a DataFrame. Supply sheet_name to choose a worksheet or read multiple sheets.
How do I save a pandas DataFrame to Excel?
Call df.to_excel('output.xlsx', index=False). The index=False argument avoids writing the DataFrame row index as an extra column.
Can pandas read every sheet in an Excel workbook?
Yes. Use pd.read_excel('file.xlsx', sheet_name=None). It returns a dictionary mapping each sheet name to a DataFrame, so check that each sheet has a layout your processing code can handle.
Does pandas preserve Excel formatting and formulas?
Not as a complete workbook-editing workflow. pandas primarily imports and exports data, does not calculate Excel formulas, and may not retain formatting or workbook features. Use Excel or a workbook-focused library when those features must be preserved or changed.
Why does read_excel say an engine is missing?
pandas needs an additional engine package for many Excel formats. Install the dependency appropriate to the file and operation, such as openpyxl for common .xlsx work, and consult the error message and pandas documentation for your version.
Related
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Between Two Excel Workbooks: A Simple GuideYes. In Excel for Microsoft 365 on Windows, VLOOKUP can search a table in another workbook by using…
- Excel QuickBooks: How to Add Formulas to Your DataTo add a formula to QuickBooks data in Excel, open the exported spreadsheet, select an empty cell, type…
- How to Compare Two Columns in Excel with VLOOKUP FormulaTo compare two columns in Excel, check whether each value in the first list appears in the second…
- Excel Filter Shortcut: Add, Remove, and Use FiltersIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to add filter dropdowns to a selected data range—or…
- HLOOKUP in Excel: Purpose, Examples, and LimitsHLOOKUP in Excel searches for a value across the top row of a table and returns a related…
- Excel: How Many Days Are in a Month? Formula GuideA month can have 28, 29, 30, or 31 days, so Excel needs a year and month—or a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.