Python Pandas Excel to Dictionary: Practical Guide

How-to basics · Updated November 18, 2026

To convert an Excel worksheet to Python dictionaries with pandas, read it into a DataFrame with pd.read_excel(), then call df.to_dict(orient='records') for a list of row dictionaries. If you need one dictionary keyed by a column such as an ID, build that mapping from the records or set the ID as the index first.

Read an Excel worksheet with pandas

Pandas separates Excel reading from dictionary conversion. First, read_excel loads worksheet data into a DataFrame; then the DataFrame’s to_dict method shapes that data into Python’s dictionary structures. This two-step approach is useful because you can inspect, clean, or select columns between the steps.

For a typical .xlsx file, install pandas and an Excel-reading engine such as openpyxl if they are not already available in your Python environment:

import pandas as pd

df = pd.read_excel('sales.xlsx', sheet_name='January')
print(df.head())

The default sheet_name=0 reads the first worksheet. You can instead pass a worksheet name, as above, or a zero-based position such as sheet_name=1 for the second sheet. The default header behavior treats the first row as column names. Once loaded, df is a DataFrame, not yet a dictionary.

For common .xlsx files, pandas generally uses openpyxl as its engine. If the required package is missing, install it in the same Python environment that runs your script with pip install pandas openpyxl. Other Excel formats may require a different engine, so check the format and pandas documentation if a legacy workbook cannot be opened.

Choose the dictionary structure you need

The orient argument controls the output shape. This is the key decision: two people who say “convert Excel to a dictionary” may mean a dictionary of columns, a dictionary of rows, or a list of row dictionaries. For example, with a worksheet containing columns ID, Product, and Price, the main options are:

  • orient='records' returns a list of dictionaries, one per row. Each dictionary maps column names to cell values. It is often the most convenient shape for JSON-like processing or sending records to another function.
  • orient='dict', the default, returns a nested dictionary organized by column: each column name maps to another dictionary whose keys are the DataFrame index values and whose values are the cells in that column.
  • orient='index' returns a dictionary organized by row index. Each index value maps to a dictionary of column names and cell values.
  • orient='list' maps each column name to a list of its values. It is useful when you want column arrays rather than row records.

Here is a small example of the commonly used records form:

records = df.to_dict(orient='records')
print(records[0])

If the first row contains ID 101, a product called Notebook, and a price of 4.5, the first result resembles {'ID': 101, 'Product': 'Notebook', 'Price': 4.5}. Notice that this is a list containing dictionaries. It is not one dictionary keyed by the ID.

Create one dictionary keyed by an Excel column

When each row has a unique identifier and you want direct lookup, make that identifier the index and use orient='index':

by_id = df.set_index('ID').to_dict(orient='index')
print(by_id[101])

The result maps each ID to the other values in its row, so by_id[101] might be {'Product': 'Notebook', 'Price': 4.5}. This form is convenient when your code needs to retrieve a record by key rather than loop through a list and search.

The index must be unique for this orientation. If two rows share an ID, pandas cannot represent both as separate values under the same dictionary key and raises an error. You can check for duplicates with df['ID'].duplicated().any(). If duplicates are legitimate, keep a list of row dictionaries or group the records by ID instead of silently treating the ID as unique.

Another option keeps the ID inside each row’s value:

records = df.to_dict(orient='records')
by_id = {row['ID']: row for row in records}

This is a normal Python dictionary comprehension. Be aware that if IDs repeat, later rows replace earlier rows with the same key. Check uniqueness first if losing a row would be a problem. Dictionary keys also need to be hashable; ordinary Excel text, numbers, and date values work as keys, while a list or another mutable Python object does not.

Convert more than one worksheet

To read every sheet in a workbook, pass sheet_name=None. Pandas returns a dictionary whose keys are sheet names and whose values are DataFrames. Convert each DataFrame separately to retain the workbook’s sheet-level organization:

sheets = pd.read_excel('sales.xlsx', sheet_name=None)
workbook_records = {
    name: frame.to_dict(orient='records')
    for name, frame in sheets.items()
}

Now workbook_records['January'] is the list of January row dictionaries, assuming that sheet exists. This is different from calling to_dict on one DataFrame: the outer dictionary comes from read_excel and represents sheets, while each inner list represents rows.

If you need only selected sheets, pass a list of names or positions, for example sheet_name=['January', 'February']. Pandas returns a mapping for the requested sheets, which you can process with the same comprehension. For a large workbook, selecting only the needed sheets and columns can reduce unnecessary work and make the resulting structure easier to manage.

Handle headers, columns, and row selection

Excel workbooks often contain title lines, notes, blank rows, or columns you do not want in the output. Set the read options before conversion when possible. For example, if the actual column headings are on the third worksheet row, use header=2, because row positions are counted from zero. If there are two introductory rows before the headings, you can also use skiprows=2 and let the next row serve as the header.

df = pd.read_excel(
    'sales.xlsx',
    sheet_name='January',
    skiprows=2,
    usecols=['ID', 'Product', 'Price']
)
records = df.to_dict(orient='records')

Column selection helps prevent unrelated worksheet data from appearing in every dictionary. You can also select columns after loading with df[['ID', 'Product']]. If a sheet has no header row, use header=None; pandas will assign numeric column labels unless you provide names with the names argument. Confirm the resulting column labels with print(df.columns) before building a mapping, since a misspelled or unexpected header causes a key or column error later.

Understand missing values and Excel data types

An Excel cell that looks blank usually becomes a missing-value marker in the DataFrame, commonly represented by NaN for many numeric columns. After conversion, these markers may appear in dictionary values rather than as Python’s None. If the receiving code requires a particular missing-value representation, normalize it explicitly. For example, df = df.astype(object).where(pd.notna(df), None) replaces pandas-detected missing values with None before you call to_dict. Check the result for your data types, especially if you rely on numeric calculations later.

Excel formatting and stored values are not always the same thing. A cell may display an ID as 00123 because of number formatting while its stored numeric value is 123. Reading it as a string cannot reliably recreate formatting that is not stored as text. If leading zeros are meaningful, store such identifiers as text in Excel or apply a documented formatting rule in Python. You can use the dtype argument for columns whose stored values should be interpreted as a particular type, but it does not recover display formatting.

Date-formatted cells are commonly read as date-like Python or pandas values, and numbers may be inferred as integers or floats depending on the column. If consistent types matter, inspect df.dtypes and specify suitable types or converters when reading. Mixed values in one Excel column can lead to a broad or unexpected type. Also remember that pandas reads data; it does not calculate Excel formulas. A formula cell’s available value depends on the workbook’s saved calculation results and reading engine, so refresh or calculate the workbook in Excel if current formula results are required.

Common conversion errors and practical checks

Several frequent problems are caused by assumptions about the workbook rather than by to_dict itself. Before relying on the output, check a small sample and confirm its shape:

  • Use df.shape to see the row and column counts, and df.head() to inspect the first rows.
  • Use df.columns.tolist() to confirm that the headings match the names used in your code.
  • Check missing and duplicate identifiers before creating a dictionary keyed by an ID.
  • Try one sheet and a small set of columns before processing a large workbook or every sheet.

A KeyError such as 'ID' often means the actual header differs from the expected name, perhaps because there is a title row or extra whitespace. Inspect the columns rather than guessing. An error about duplicate index values when using orient='index' usually means the chosen key is not unique. Use records or decide how duplicate rows should be grouped.

For very large workbooks, conversion creates Python objects for the output in addition to the DataFrame, so memory use can grow substantially. Read only necessary sheets and columns, and avoid creating a second full representation if you can process rows in batches or work directly with the DataFrame. The usual read_excel workflow loads a sheet into memory; it is not a streaming reader for arbitrarily large Excel files.

When to use pandas, openpyxl, or another format

Pandas is a good fit when the worksheet represents tabular data and you want to filter, clean, analyze, or export rows. Its DataFrame model makes it straightforward to select columns, handle missing data, and then choose a dictionary orientation. It is particularly useful when the next step expects records rather than cell coordinates.

Use openpyxl directly when your task depends on workbook-specific details such as cell formatting, formulas, merged cells, comments, or precise cell positions. Pandas is designed to turn tabular values into data structures, not to preserve a workbook’s visual layout as dictionaries. If the source can be changed, CSV may be simpler for a single plain table, though CSV does not carry Excel features such as multiple worksheets, formatting, or formulas. Choose the tool based on what information the next step needs, not simply on the file extension.

Frequently Asked Questions

Does pandas convert Excel directly to a dictionary?
Pandas first reads the sheet into a DataFrame with pd.read_excel(). Call to_dict() on that DataFrame to choose the desired dictionary structure.

How do I get a list of dictionaries from Excel?
Read the worksheet into df, then use df.to_dict(orient='records'). Each list item is a dictionary mapping column names to one row’s values.

How do I use an Excel ID column as dictionary keys?
For unique IDs, use df.set_index('ID').to_dict(orient='index'). Confirm the column has no duplicates first, or multiple records may not fit the mapping you intend.

How do I convert every sheet in an Excel file?
Call pd.read_excel('file.xlsx', sheet_name=None) to get a dictionary of DataFrames, then convert each DataFrame with a comprehension. Choose an orientation for the values, such as 'records'.

Why does my converted dictionary contain NaN?
Excel blanks are generally interpreted as missing values by pandas. If your application requires None instead, replace missing values in the DataFrame before calling to_dict, and verify the resulting types.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.