August 9, 2022

How to Read an Excel File in Python (w/ 21 Code Examples)

Microsoft Excel is one of the most powerful spreadsheet software applications in the world, and it has become critical in all business processes. Companies across the world, both big and small, are using Microsoft Excel to store, organize, analyze, and visualize data.

As a data professional, when you combine Python with Excel, you create a unique data analysis bundle that unlocks the value of the enterprise data.

In this tutorial, we’re going to learn how to read and work with Excel files in Python.

After you finish this tutorial, you’ll understand the following:

  • Loading Excel spreadsheets into pandas DataFrames
  • Working with an Excel workbook with multiple spreadsheets
  • Combining multiple spreadsheets
  • Reading Excel files using the xlrd package

In this tutorial, we assume you know the fundamentals of pandas DataFrames. If you aren’t familiar with the pandas library, you might like to try our Pandas and NumPy Fundamentals – Dataquest.

Let’s dive in.

Reading Spreadsheets with Pandas

Technically, multiple packages allow us to work with Excel files in Python. However, in this tutorial, we’ll use pandas and xlrd libraries to interact with Excel workbooks. Essentially, you can think of a pandas DataFrame as a spreadsheet with rows and columns stored in Series objects. Traversability of Series as iterable objects allows us to grab specific data easily. Once we load an Excel workbook into a pandas DataFrame, we can perform any kind of data analysis on the data.

Before we proceed to the next step, let’s first download the following spreadsheet:

Sales Data Excel Workbook – xlsx ver.

The Excel workbook consists of two sheets that contain stationery sales data for 2020 and 2021.


NOTE

Although Excel spreadsheets can contain formula and also support formatting, pandas only imports Excel spreadsheets as flat files, and it doesn’t support spreadsheet formatting.


To import the Excel spreadsheet into a pandas DataFrame, first, we need to import the pandas package and then use the read_excel() method:

import pandas as pd
df = pd.read_excel('sales_data.xlsx')

display(df)
OrderDate Region Rep Item Units Unit Cost Total Shipped
0 2020-01-06 East Jones Pencil 95 1.99 189.05 True
1 2020-02-09 Central Jardine Pencil 36 4.99 179.64 True
2 2020-03-15 West Sorvino Pencil 56 2.99 167.44 True
3 2020-04-01 East Jones Binder 60 4.99 299.40 False
4 2020-05-05 Central Jardine Pencil 90 4.99 449.10 True
5 2020-06-08 East Jones Binder 60 8.99 539.40 True
6 2020-07-12 East Howard Binder 29 1.99 57.71 False
7 2020-08-15 East Jones Pencil 35 4.99 174.65 True
8 2020-09-01 Central Smith Desk 32 125.00 250.00 True
9 2020-10-05 Central Morgan Binder 28 8.99 251.72 True
10 2020-11-08 East Mike Pen 15 19.99 299.85 False
11 2020-12-12 Central Smith Pencil 67 1.29 86.43 False


If you want to load only a limited number of rows into the DataFrame, you can specify the number of rows using the nrows argument:

df = pd.read_excel('sales_data.xlsx', nrows=5)
display(df)
OrderDate Region Rep Item Units Unit Cost Total Shipped
0 2020-01-06 East Jones Pencil 95 1.99 189.05 True
1 2020-02-09 Central Jardine Pencil 36 4.99 179.64 True
2 2020-03-15 West Sorvino Pencil 56 2.99 167.44 True
3 2020-04-01 East Jones Binder 60 4.99 299.40 False
4 2020-05-05 Central Jardine Pencil 90 4.99 449.10 True


Skipping a specific number of rows from the begining of a spreadsheet or skipping over a list of particular rows is available through the skiprows argument, as follows:

df = pd.read_excel('sales_data.xlsx', skiprows=range(5))
display(df)
2020-05-05 00:00:00 Central Jardine Pencil 90 4.99 449.1 True
0 2020-06-08 East Jones Binder 60 8.99 539.40 True
1 2020-07-12 East Howard Binder 29 1.99 57.71 False
2 2020-08-15 East Jones Pencil 35 4.99 174.65 True
3 2020-09-01 Central Smith Desk 32 125.00 250.00 True
4 2020-10-05 Central Morgan Binder 28 8.99 251.72 True
5 2020-11-08 East Mike Pen 15 19.99 299.85 False
6 2020-12-12 Central Smith Pencil 67 1.29 86.43 False


The code above skips the first five rows and returns the rest of the data. Instead, the following code returns all the rows except for those with the mentioned indices:

df = pd.read_excel('sales_data.xlsx', skiprows=[1, 4,7,10])
display(df)
OrderDate Region Rep Item Units Unit Cost Total Shipped
0 2020-02-09 Central Jardine Pencil 36 4.99 179.64 True
1 2020-03-15 West Sorvino Pencil 56 2.99 167.44 True
2 2020-05-05 Central Jardine Pencil 90 4.99 449.10 True
3 2020-06-08 East Jones Binder 60 8.99 539.40 True
4 2020-08-15 East Jones Pencil 35 4.99 174.65 True
5 2020-09-01 Central Smith Desk 32 125.00 250.00 True
6 2020-11-08 East Mike Pen 15 19.99 299.85 False
7 2020-12-12 Central Smith Pencil 67 1.29 86.43 False


Another useful argument is usecols, which allows us to select spreadsheet columns with their letters, names, or positional numbers. Let’s see how it works:

df = pd.read_excel('sales_data.xlsx', usecols='A:C,G')
display(df)
OrderDate Region Rep Total
0 2020-01-06 East Jones 189.05
1 2020-02-09 Central Jardine 179.64
2 2020-03-15 West Sorvino 167.44
3 2020-04-01 East Jones 299.40
4 2020-05-05 Central Jardine 449.10
5 2020-06-08 East Jones 539.40
6 2020-07-12 East Howard 57.71
7 2020-08-15 East Jones 174.65
8 2020-09-01 Central Smith 250.00
9 2020-10-05 Central Morgan 251.72
10 2020-11-08 East Mike 299.85
11 2020-12-12 Central Smith 86.43


In the code above, the string assigned to the usecols argument contains a range of columns with : plus column G separated by a comma. Also, we’re able to provide a list of column names and assign it to the usecols argument, as follows:

df = pd.read_excel('sales_data.xlsx', usecols=['OrderDate', 'Region', 'Rep', 'Total'])
display(df)
OrderDate Region Rep Total
0 2020-01-06 East Jones 189.05
1 2020-02-09 Central Jardine 179.64
2 2020-03-15 West Sorvino 167.44
3 2020-04-01 East Jones 299.40
4 2020-05-05 Central Jardine 449.10
5 2020-06-08 East Jones 539.40
6 2020-07-12 East Howard 57.71
7 2020-08-15 East Jones 174.65
8 2020-09-01 Central Smith 250.00
9 2020-10-05 Central Morgan 251.72
10 2020-11-08 East Mike 299.85
11 2020-12-12 Central Smith 86.43


The usecols argument accepts a list of column numbers, too. The following code shows how we can pick up specific columns using their indices:

df = pd.read_excel('sales_data.xlsx', usecols=[0, 1, 2, 6])
display(df)
OrderDate Region Rep Total
0 2020-01-06 East Jones 189.05
1 2020-02-09 Central Jardine 179.64
2 2020-03-15 West Sorvino 167.44
3 2020-04-01 East Jones 299.40
4 2020-05-05 Central Jardine 449.10
5 2020-06-08 East Jones 539.40
6 2020-07-12 East Howard 57.71
7 2020-08-15 East Jones 174.65
8 2020-09-01 Central Smith 250.00
9 2020-10-05 Central Morgan 251.72
10 2020-11-08 East Mike 299.85
11 2020-12-12 Central Smith 86.43


Working with Multiple Spreadsheets

Excel files or workbooks usually contain more than one spreadsheet. The pandas library allows us to load data from a specific sheet or combine multiple spreadsheets into a single DataFrame. In this section, we’ll explore how to use these valuable capabilities.

By default, the read_excel() method reads the first Excel sheet with the index 0. However, we can choose the other sheets by assigning a particular sheet name, sheet index, or even a list of sheet names or indices to the sheet_name argument. Let’s try it:

df = pd.read_excel('sales_data.xlsx', sheet_name='2021')
display(df)
OrderDate Region Rep Item Units Unit Cost Total Shipped
0 2021-01-15 Central Gill Binder 46 8.99 413.54 True
1 2021-02-01 Central Smith Binder 87 15.00 1305.00 True
2 2021-03-07 West Sorvino Binder 27 19.99 139.93 True
3 2021-04-10 Central Andrews Pencil 66 1.99 131.34 False
4 2021-05-14 Central Gill Pencil 53 1.29 68.37 False
5 2021-06-17 Central Tom Desk 15 125.00 625.00 True
6 2021-07-04 East Jones Pen Set 62 4.99 309.38 True
7 2021-08-07 Central Tom Pen Set 42 23.95 1005.90 True
8 2021-09-10 Central Gill Pencil 47 1.29 9.03 True
9 2021-10-14 West Thompson Binder 57 19.99 1139.43 False
10 2021-11-17 Central Jardine Binder 11 4.99 54.89 False
11 2021-12-04 Central Jardine Binder 94 19.99 1879.06 False


The code above reads the second spreadsheet in the workbook, whose name is 2021. As mentioned before, we also can assign a sheet position number (zero-indexed) to the sheet_name argument. Let’s see how it works:

df = pd.read_excel('sales_data.xlsx', sheet_name=1)
display(df)
OrderDate Region Rep Item Units Unit Cost Total Shipped
0 2021-01-15 Central Gill Binder 46 8.99 413.54 True
1 2021-02-01 Central Smith Binder 87 15.00 1305.00 True
2 2021-03-07 West Sorvino Binder 27 19.99 139.93 True
3 2021-04-10 Central Andrews Pencil 66 1.99 131.34 False
4 2021-05-14 Central Gill Pencil 53 1.29 68.37 False
5 2021-06-17 Central Tom Desk 15 125.00 625.00 True
6 2021-07-04 East Jones Pen Set 62 4.99 309.38 True
7 2021-08-07 Central Tom Pen Set 42 23.95 1005.90 True
8 2021-09-10 Central Gill Pencil 47 1.29 9.03 True
9 2021-10-14 West Thompson Binder 57 19.99 1139.43 False
10 2021-11-17 Central Jardine Binder 11 4.99 54.89 False
11 2021-12-04 Central Jardine Binder 94 19.99 1879.06 False


As you can see, both statements take in either the actual sheet name or sheet index to return the same result.

Sometimes, we want to import all the spreadsheets stored in an Excel file into pandas DataFrames simultaneously. The good news is that the read_excel() method provides this feature for us. In order to do this, we can assign a list of sheet names or their indices to the sheet_name argument. But there is a much easier way to do the same: to assign None to the sheet_name argument. Let’s try it:

all_sheets = pd.read_excel('sales_data.xlsx', sheet_name=None)

Before exploring the data stored in the all_sheets variable, let’s check its data type:

type(all_sheets)
dict

As you can see, the variable is a dictionary. Now, let’s reveal what is stored in this dictionary:

for key, value in all_sheets.items():
    print(key, type(value))
2020 <class 'pandas.core.frame.DataFrame'>
2021 <class 'pandas.core.frame.DataFrame'>

The code above shows that the dictionary’s keys are the Excel workbook sheet names, and its values are pandas DataFrames for each spreadsheet. To print out the content of the dictionary, we can use the following code:

for key, value in all_sheets.items():
    print(key)
    display(value)
2020
OrderDate Region Rep Item Units Unit Cost Total Shipped
0 2020-01-06 East Jones Pencil 95 1.99 189.05 True
1 2020-02-09 Central Jardine Pencil 36 4.99 179.64 True
2 2020-03-15 West Sorvino Pencil 56 2.99 167.44 True
3 2020-04-01 East Jones Binder 60 4.99 299.40 False
4 2020-05-05 Central Jardine Pencil 90 4.99 449.10 True
5 2020-06-08 East Jones Binder 60 8.99 539.40 True
6 2020-07-12 East Howard Binder 29 1.99 57.71 False
7 2020-08-15 East Jones Pencil 35 4.99 174.65 True
8 2020-09-01 Central Smith Desk 32 125.00 250.00 True
9 2020-10-05 Central Morgan Binder 28 8.99 251.72 True
10 2020-11-08 East Mike Pen 15 19.99 299.85 False
11 2020-12-12 Central Smith Pencil 67 1.29 86.43 False


2021
OrderDate Region Rep Item Units Unit Cost Total Shipped
0 2021-01-15 Central Gill Binder 46 8.99 413.54 True
1 2021-02-01 Central Smith Binder 87 15.00 1305.00 True
2 2021-03-07 West Sorvino Binder 27 19.99 139.93 True
3 2021-04-10 Central Andrews Pencil 66 1.99 131.34 False
4 2021-05-14 Central Gill Pencil 53 1.29 68.37 False
5 2021-06-17 Central Tom Desk 15 125.00 625.00 True
6 2021-07-04 East Jones Pen Set 62 4.99 309.38 True
7 2021-08-07 Central Tom Pen Set 42 23.95 1005.90 True
8 2021-09-10 Central Gill Pencil 47 1.29 9.03 True
9 2021-10-14 West Thompson Binder 57 19.99 1139.43 False
10 2021-11-17 Central Jardine Binder 11 4.99 54.89 False
11 2021-12-04 Central Jardine Binder 94 19.99 1879.06 False


Combining Multiple Excel Spreadsheets into a Single Pandas DataFrame

Having one DataFrame per sheet allows us to have different columns or content in different sheets.

But what if we prefer to store all the spreadsheets’ data in a single DataFrame? In this tutorial, the workbook spreadsheets have the same columns, so we can combine them with the concat() method of pandas.

If you run the code below, you’ll see that the two DataFrames stored in the dictionary are concatenated:

combined_df = pd.concat(all_sheets.values(), ignore_index=True)
display(combined_df)
OrderDate Region Rep Item Units Unit Cost Total Shipped
0 2020-01-06 East Jones Pencil 95 1.99 189.05 True
1 2020-02-09 Central Jardine Pencil 36 4.99 179.64 True
2 2020-03-15 West Sorvino Pencil 56 2.99 167.44 True
3 2020-04-01 East Jones Binder 60 4.99 299.40 False
4 2020-05-05 Central Jardine Pencil 90 4.99 449.10 True
5 2020-06-08 East Jones Binder 60 8.99 539.40 True
6 2020-07-12 East Howard Binder 29 1.99 57.71 False
7 2020-08-15 East Jones Pencil 35 4.99 174.65 True
8 2020-09-01 Central Smith Desk 32 125.00 250.00 True
9 2020-10-05 Central Morgan Binder 28 8.99 251.72 True
10 2020-11-08 East Mike Pen 15 19.99 299.85 False
11 2020-12-12 Central Smith Pencil 67 1.29 86.43 False
12 2021-01-15 Central Gill Binder 46 8.99 413.54 True
13 2021-02-01 Central Smith Binder 87 15.00 1305.00 True
14 2021-03-07 West Sorvino Binder 27 19.99 139.93 True
15 2021-04-10 Central Andrews Pencil 66 1.99 131.34 False
16 2021-05-14 Central Gill Pencil 53 1.29 68.37 False
17 2021-06-17 Central Tom Desk 15 125.00 625.00 True
18 2021-07-04 East Jones Pen Set 62 4.99 309.38 True
19 2021-08-07 Central Tom Pen Set 42 23.95 1005.90 True
20 2021-09-10 Central Gill Pencil 47 1.29 9.03 True
21 2021-10-14 West Thompson Binder 57 19.99 1139.43 False
22 2021-11-17 Central Jardine Binder 11 4.99 54.89 False
23 2021-12-04 Central Jardine Binder 94 19.99 1879.06 False


Now the data stored in the combined_df DataFrame is ready for further processing or visualization. In the following piece of code, we’re going to create a simple bar chart that shows the total sales amount made by each representative. Let’s run it and see the output plot:

total_sales_amount = combined_df.groupby('Rep').Total.sum()
total_sales_amount.plot.bar(figsize=(10, 6))

Output

Reading Excel Files Using xlrd

Although importing data into a pandas DataFrame is much more common, another helpful package for reading Excel files in Python is xlrd. In this section, we’re going to scratch the surface of how to read Excel spreadsheets using this package.


NOTE

The xlrd package doesn’t support xlsx files due to a potential security vulnerability. So, we use the xls version of the sales data. You can download the xls version from the link below:
Sales Data Excel Workbook – xls ver.


Let’s see how it works:

import xlrd
excel_workbook = xlrd.open_workbook('sales_data.xls')

Above, the first line imports the xlrd package, then the open_workbook method reads the sales_data.xls file.

We can also open an individual sheet containing the actual data. There are two ways to do so: opening a sheet by index or by name. Let’s open the first sheet by index and the second one by name:

excel_worksheet_2020 = excel_workbook.sheet_by_index(0)
excel_worksheet_2021 = excel_workbook.sheet_by_name('2021')

Now, let’s see how we can print a cell value. The xlrd package provides a method called cell_value() that takes in two arguments: the cell’s row index and column index. Let’s explore it:

print(excel_worksheet_2020.cell_value(1, 3))
Pencil

We can see that the cell_value function returned the value of the cell at row index 1 (the 2nd row) and column index 3 (the 4th column).
Excel

The xlrd package provides two helpful properties: nrows and ncols, returning the number of nonempty spreadsheet’s rows and columns respectively:

print('Columns#:', excel_worksheet_2020.ncols)
print('Rows#:', excel_worksheet_2020.nrows)
Columns#: 8
Rows#: 13

Knowing the number of nonempty rows and columns in a spreadsheet helps us with iterating over the data using nested for loops. This makes all the Excel sheet data accessible via the cell_value() method.

Conclusion

This tutorial discussed how to load Excel spreadsheets into pandas DataFrames, work with multiple Excel sheets, and combine them into a single pandas DataFrame. We also explored the main aspects of the xlrd package as one of the simplest tools for accessing the Excel spreadsheets data.

Mehdi Lotfinejad

About the author

Mehdi Lotfinejad

Mehdi is a Senior Data Engineer and Team Lead at ADA. He is a professional trainer who loves writing data analytics tutorials.

Learn data skills for free

Headshot Headshot

Join 1M+ learners

Try free courses