How to Read Excel Files in Pandas: Simple Guide

Use the pandas.read_excel() function to load Excel files into a DataFrame. Provide the file path as the first argument, and optionally specify the sheet name with sheet_name.

Syntax

The basic syntax to read an Excel file in pandas is:

• pandas.read_excel(io, sheet_name=0, ...)

Where:

• io: The file path or Excel file object.
• sheet_name: Name or index of the sheet to read (default is the first sheet).

python

import pandas as pd

df = pd.read_excel('file.xlsx', sheet_name='Sheet1')

Example

This example reads an Excel file named data.xlsx and prints the first 5 rows of the sheet named Sales.

python

import pandas as pd

# Read the 'Sales' sheet from data.xlsx
sales_data = pd.read_excel('data.xlsx', sheet_name='Sales')

# Show first 5 rows
print(sales_data.head())

Output

   Product  Quantity  Price
0  Apples        10    1.50
1  Bananas       15    0.80
2  Oranges       12    1.20
3  Pears          8    1.00
4  Grapes        20    2.00

Common Pitfalls

Common mistakes when reading Excel files include:

• Not specifying the correct sheet_name if the file has multiple sheets.
• Using a wrong file path or filename causing a FileNotFoundError.
• Not installing the required engine like openpyxl for .xlsx files.

Always check the file path and install dependencies with pip install openpyxl if needed.

python

import pandas as pd

# Wrong way: missing sheet_name when multiple sheets exist
# df = pd.read_excel('data.xlsx')  # May read wrong sheet

# Correct way: specify sheet_name
# df = pd.read_excel('data.xlsx', sheet_name='Sales')

Quick Reference

Parameter	Description	Default
io	File path or Excel file object	Required
sheet_name	Sheet name or index to read	0 (first sheet)
header	Row number to use as column names	0
usecols	Columns to parse	None
skiprows	Rows to skip at start	None

Key Takeaways

Use pandas.read_excel() with the file path to load Excel data into a DataFrame.

Specify sheet_name to read a specific sheet when the file has multiple sheets.

Ensure the file path is correct to avoid errors.

Install openpyxl to read .xlsx files if not already installed.

Use parameters like header and usecols to customize data reading.