Reading Excel Files

Learn how to read Excel workbooks into Pandas DataFrames so you can work with spreadsheet data in Python, both in Google Colab and on your own machine.

Skill Level: Beginner
Prerequisites: Reading CSV Files
Estimated Time: 20 minutes

Story Time

You have already learned how to read CSV files into Pandas.

Now imagine a university sends you exam data in an Excel workbook instead. The file has one or more worksheets, and you want to analyze the data in Python rather than clicking through spreadsheets.

In this lesson, you will learn how to read Excel files into Pandas DataFrames so you can work with them just like CSV files.

What You’ll Learn

By the end of this lesson, you will be able to:

• Explain what an Excel file is.
• Read Excel files into Python using Pandas.
• Load specific worksheets from a workbook.
• Display the contents of an Excel file.
• Explain key differences between CSV and Excel files.

Why This Topic Matters

Many organizations store and share data in Microsoft Excel format.

Financial reports, research data, survey responses, and administrative records are often distributed as Excel workbooks. If you can only read CSV files, you will miss a large portion of real‑world datasets.

Being able to read Excel files with Pandas is an important practical skill for data analysis, even though most datasets in this guide will be provided as CSV for simplicity.[web:4]

What Is an Excel File?

An Excel workbook is a spreadsheet file that can contain one or more worksheets.

Compared to CSV files, Excel workbooks can include:

• Multiple sheets
• Formulas
• Formatting
• Charts
• Tables

For data analysis, you usually focus on the tabular data stored in the worksheets. Pandas can read that data and ignore most of the extra formatting.

Getting an Excel File into Google Colab

Just like with CSV files, you need to make the Excel file available in your Colab notebook.

Upload from your computer

1. In your Colab notebook, run:

   from google.colab import files
   uploaded = files.upload()

2. Use the file picker to upload StudentsPerformance.xlsx (or any Excel file you want to use).

3. After upload, the file will be available in the notebook’s working directory.

Then you can read it with:

import pandas as pd

df = pd.read_excel("StudentsPerformance.xlsx")

Using Google Drive (optional)

If the Excel file lives in Google Drive:

from google.colab import drive
drive.mount('/content/drive')

import pandas as pd

df = pd.read_excel("/content/drive/MyDrive/Data/StudentsPerformance.xlsx")

For this guide, uploading from your computer is usually enough.

Reading an Excel File with Pandas

Pandas provides the read_excel() function to load Excel files:

import pandas as pd

df = pd.read_excel("StudentsPerformance.xlsx")

Here:

• pd refers to the Pandas library.
• read_excel() reads the Excel file.
• df is a DataFrame containing the dataset.

Once loaded, you can use df just as you did with CSV data.

Reading a Specific Worksheet

If an Excel workbook has multiple sheets, you can specify which worksheet to load using the sheet_name parameter:

df = pd.read_excel(
    "StudentsPerformance.xlsx",
    sheet_name="Sheet1"
)

Replace "Sheet1" with the actual name of the worksheet you want to read. This lets you choose a single sheet rather than loading everything at once.

Viewing the Data

You can preview the DataFrame using the same functions you used for CSV files:

df.head()

shows the first five rows, and:

df.tail()

shows the last five rows. These functions behave the same regardless of whether the data came from CSV or Excel.

CSV vs Excel

Here is a simple comparison between CSV and Excel files:

CSV	Excel
Plain text	Spreadsheet workbook
One worksheet	Multiple worksheets
Smaller file size	Larger file size
Often faster to read	Slightly slower to read
No formatting	Supports formatting and formulas

Both formats are common in data analysis. CSV is simple and lightweight, while Excel is richer and often used in business and administrative settings.[web:4]

Worked Example

import pandas as pd

df = pd.read_excel("StudentsPerformance.xlsx")

df.head()

As you look at the output, observe:

• How many columns are present
• The names of each column
• A few sample records

Then compare what you see with the CSV version from the previous lesson. The structure should be similar, even though the source file format is different.[file:42]

Practice in the Notebook

Notebook

Open in Google Colab

Upload and read an Excel workbook, then preview it.

After completing the activity in Google Colab, mark it as complete below.

In Google Colab:

1. Upload the Excel version of the Students Performance dataset (if you have one), or any simple Excel file.
2. Import Pandas.
3. Load the dataset using pd.read_excel().
4. Display the first five rows with df.head().
5. Display the last five rows with df.tail().

Then answer:

• Does the output differ from the CSV version?
• Which file format would you choose for data analysis, and why?

You can write a short explanation in the notebook.

Independent Exercise

Suppose a university provides student records in an Excel workbook containing multiple worksheets (for example, different years or departments).

Research the sheet_name parameter, then:

• Choose one worksheet name.
• Load that worksheet using read_excel().
• Display its first five rows with head().

This will help you practice working with multi‑sheet workbooks.

Common Mistakes

Using the Wrong Function

Incorrect:

pd.read_csv("students.xlsx")

Correct:

pd.read_excel("students.xlsx")

CSV files use read_csv(), while Excel files use read_excel().

Incorrect Worksheet Name

pd.read_excel(
    "students.xlsx",
    sheet_name="Data"
)

If "Data" is not a valid worksheet name, Pandas will raise an error. Always check the sheet names inside the Excel file before using them.

Missing Excel Engine

Some environments require an additional library such as openpyxl to read Excel files.

If you see an error about an Excel engine when working locally, install it with:

pip install openpyxl

Google Colab already includes the required dependency in most cases, so you typically do not need to install anything.

Key Takeaways

In this lesson, you learned that:

• Pandas can read Excel files using read_excel().
• Excel workbooks can contain multiple worksheets.
• You can use sheet_name to load a specific worksheet.
• CSV and Excel files share tabular data but differ in structure and features.

Self Evaluation

Check Your Understanding

1. Which Pandas function is used to read an Excel file?

pd.read_csv()pd.read_excel()pd.load_excel()pd.open()

2. What is one key difference between CSV and Excel files?

CSV files can have multiple worksheets; Excel files cannotExcel files can have multiple worksheets; CSV files typically have oneCSV files support formulas; Excel files do notCSV files always include charts

3. How do you specify which worksheet to load from an Excel file?

Use the file_name parameterUse the sheet_name parameter in pd.read_excel()Use the worksheet parameter in pd.read_excel()It is not possible to choose a worksheet

4. What does df.head() do after you read an Excel file into a DataFrame?

Deletes the first five rowsDisplays the first five rowsDisplays only the column namesSaves the DataFrame to an Excel file

5. Which dependency might be needed to read Excel files in some Python environments?

pandasnumpyopenpyxlmatplotlib

Continue Your Journey

Next, you will focus on Understanding DataFrames, the core data structure you will use for most of your analysis in Pandas.