Import Excel Data into Python with Pandas

Knowing how to import Excel data into Python is useful for reporting, automation, finance, operations, and data analysis. Instead of copying values manually, a Python script can read a workbook, select the correct worksheet, clean columns, calculate results, and export a new file.

This guide uses Pandas for table-oriented work and openpyxl when you need direct control over Excel workbooks. You will learn installation, basic reading, sheet selection, column types, missing values, dates, large files, validation, and export.

Review the Python data types guide and the CSV files guide if tabular data is new to you.

Install the Required Packages

Pandas provides the DataFrame structure. openpyxl is commonly used as the engine for modern .xlsx files.

python -m pip install pandas openpyxl

The official pandas.read_excel documentation lists all supported options. The openpyxl documentation covers workbook-level operations.

Read Your First Excel File

import pandas as pd

sales = pd.read_excel("sales.xlsx")

print(sales.head())
print(sales.shape)
print(sales.columns)

head() displays the first rows. shape returns the number of rows and columns. columns shows the labels available for selection.

Use a Reliable File Path

Relative paths depend on the working directory. pathlib makes paths explicit and portable:

from pathlib import Path
import pandas as pd

input_file = Path("data") / "sales.xlsx"

if not input_file.exists():
    raise FileNotFoundError(f"File not found: {input_file}")

sales = pd.read_excel(input_file)

The Python pathlib guide explains file and folder operations.

Select a Worksheet

Use the sheet name or zero-based position:

sales = pd.read_excel(
    "company-report.xlsx",
    sheet_name="Sales",
)

Read every worksheet into a dictionary of DataFrames:

worksheets = pd.read_excel(
    "company-report.xlsx",
    sheet_name=None,
)

for name, table in worksheets.items():
    print(name, table.shape)

This is helpful when a workbook contains separate monthly or departmental tables.

Inspect Sheet Names Before Reading

import pandas as pd

workbook = pd.ExcelFile("company-report.xlsx")
print(workbook.sheet_names)

sales = pd.read_excel(workbook, sheet_name="Sales")

Inspecting names first avoids errors caused by spelling, capitalization, or hidden spaces.

Select Only the Columns You Need

sales = pd.read_excel(
    "sales.xlsx",
    usecols=["Date", "Product", "Quantity", "Unit Price"],
)

For a large workbook, selecting fewer columns reduces memory use and makes later processing clearer.

Skip Headers and Unnecessary Rows

Business spreadsheets often contain titles or notes before the actual table:

sales = pd.read_excel(
    "sales.xlsx",
    sheet_name="Report",
    skiprows=3,
)

If the table has no header row, provide names:

columns = ["date", "product", "quantity", "price"]

sales = pd.read_excel(
    "sales-no-header.xlsx",
    header=None,
    names=columns,
)

Understand Data Types

print(sales.dtypes)
print(sales.info())

Excel cells may contain mixed values. A numeric column can become text if one cell contains a label or invalid character.

sales["Quantity"] = pd.to_numeric(
    sales["Quantity"],
    errors="coerce",
)

sales["Unit Price"] = pd.to_numeric(
    sales["Unit Price"],
    errors="coerce",
)

errors="coerce" converts invalid values to missing values instead of stopping the program.

Parse Dates Correctly

sales = pd.read_excel(
    "sales.xlsx",
    parse_dates=["Date"],
)

print(sales["Date"].dt.year.value_counts())

When formats are inconsistent, convert after reading:

sales["Date"] = pd.to_datetime(
    sales["Date"],
    errors="coerce",
)

The Python datetime guide explains dates, time zones, and formatting.

Handle Missing Values

print(sales.isna().sum())

Choose a strategy based on the meaning of the data:

sales = sales.dropna(subset=["Product"])
sales["Quantity"] = sales["Quantity"].fillna(0)
sales["Unit Price"] = sales["Unit Price"].fillna(
    sales["Unit Price"].median()
)

Do not fill every missing value automatically. A blank field may represent unavailable information, an error, or a legitimate absence.

Clean Column Names and Text

sales.columns = (
    sales.columns
    .str.strip()
    .str.lower()
    .str.replace(" ", "_", regex=False)
)

sales["product"] = sales["product"].astype("string").str.strip()

Consistent column names reduce quoting and spelling errors later.

Calculate New Columns

sales["total"] = sales["quantity"] * sales["unit_price"]

summary = (
    sales.groupby("product", as_index=False)["total"]
    .sum()
    .sort_values("total", ascending=False)
)

print(summary.head(10))

For a full introduction to filtering, grouping, and DataFrames, continue with Pandas in Python for beginners.

Filter Excel Data

high_value = sales[sales["total"] >= 1000]

recent = sales[
    sales["date"].between("2026-01-01", "2026-12-31")
]

Boolean expressions create a mask that selects matching rows.

Read Excel with Explicit Types

Identifiers such as ZIP codes, account numbers, or product codes should often be strings because leading zeros matter:

customers = pd.read_excel(
    "customers.xlsx",
    dtype={
        "Customer ID": "string",
        "ZIP Code": "string",
    },
)

Without an explicit type, Excel or Pandas may interpret the values as numbers.

Export Results to Excel

summary.to_excel(
    "sales-summary.xlsx",
    index=False,
)

Write several DataFrames to different sheets:

with pd.ExcelWriter(
    "processed-report.xlsx",
    engine="openpyxl",
) as writer:
    sales.to_excel(writer, sheet_name="Clean Data", index=False)
    summary.to_excel(writer, sheet_name="Summary", index=False)

Use openpyxl for Direct Cell Access

Pandas is ideal for tables. openpyxl is useful when you need formulas, individual cells, dimensions, or workbook formatting.

from openpyxl import load_workbook

workbook = load_workbook("sales.xlsx", data_only=True)
worksheet = workbook["Sales"]

print(worksheet["A2"].value)

for row in worksheet.iter_rows(
    min_row=2,
    values_only=True,
):
    print(row)

data_only=True reads the cached result of formulas when available, not the formula text.

Handle Common Errors

from pathlib import Path
import pandas as pd

file_path = Path("sales.xlsx")

try:
    sales = pd.read_excel(file_path, sheet_name="Sales")
except FileNotFoundError:
    print(f"File not found: {file_path}")
except ValueError as error:
    print(f"Invalid worksheet or file content: {error}")
except PermissionError:
    print("Close the workbook in Excel and check permissions.")
except ImportError:
    print("Install the required Excel engine, such as openpyxl.")
else:
    print(sales.head())

Use Python logging when this process runs automatically.

Complete Cleaning Script

from pathlib import Path
import pandas as pd

INPUT = Path("data") / "sales.xlsx"
OUTPUT = Path("output") / "sales-summary.xlsx"


def load_sales(path: Path) -> pd.DataFrame:
    return pd.read_excel(
        path,
        sheet_name="Sales",
        usecols=["Date", "Product", "Quantity", "Unit Price"],
        parse_dates=["Date"],
    )


def clean_sales(data: pd.DataFrame) -> pd.DataFrame:
    cleaned = data.copy()
    cleaned.columns = [
        "date",
        "product",
        "quantity",
        "unit_price",
    ]
    cleaned["product"] = cleaned["product"].astype("string").str.strip()
    cleaned["quantity"] = pd.to_numeric(cleaned["quantity"], errors="coerce")
    cleaned["unit_price"] = pd.to_numeric(cleaned["unit_price"], errors="coerce")
    cleaned = cleaned.dropna(
        subset=["date", "product", "quantity", "unit_price"]
    )
    cleaned["total"] = cleaned["quantity"] * cleaned["unit_price"]
    return cleaned


def summarize(data: pd.DataFrame) -> pd.DataFrame:
    return (
        data.groupby("product", as_index=False)
        .agg(
            units=("quantity", "sum"),
            revenue=("total", "sum"),
        )
        .sort_values("revenue", ascending=False)
    )


def main() -> None:
    OUTPUT.parent.mkdir(parents=True, exist_ok=True)
    sales = clean_sales(load_sales(INPUT))
    report = summarize(sales)
    report.to_excel(OUTPUT, index=False)
    print(f"Report saved to {OUTPUT}")


if __name__ == "__main__":
    main()

The script separates loading, cleaning, summarizing, and saving. That structure makes each step easier to test with Pytest.

Common Mistakes

• Assuming the first worksheet is always correct.
• Using numeric types for identifiers with leading zeros.
• Ignoring mixed or missing values.
• Loading every column from a very large workbook.
• Overwriting the original file before validating the result.
• Expecting Pandas to preserve complex Excel formatting.
• Leaving the workbook open in another application during export.

Frequently Asked Questions

Does Python read .xls files?

Support depends on the engine and package versions. Modern workflows should prefer .xlsx when possible.

Should I use Pandas or openpyxl?

Use Pandas for table analysis and transformation. Use openpyxl for workbook structure, formatting, formulas, and individual cells.

Can I read only part of a worksheet?

Yes. Combine options such as usecols, skiprows, and nrows.

Can Python update an existing workbook?

Yes. openpyxl can load, edit, and save an existing .xlsx file. Create a backup before changing important workbooks.

Conclusion

Importing Excel data into Python becomes reliable when you inspect worksheet names, select only required columns, define important types, parse dates, and validate missing values. Pandas handles table operations efficiently, while openpyxl provides direct workbook control.

Begin with a copy of a small workbook. Once the cleaning rules are correct, move the script into an automated process with logging, tests, and a separate output folder.