Load One or Multiple Sheets of Excel by Pandas sheet_name

We will show you how to use read_excel method of Pandas library to load:One excel sheet, Two Excel sheets annd All excel sheets

How to load multiple sheets in Excel using Python Pandas library

In this tutorial, we will show you how to use read_excel method of Pandas library to load:

  • One excel sheet
  • Two Excel sheets
  • All excel sheets

From the specified Workbook.

Loading one excel worksheet example

By default, Pandas read_excel loads the first worksheet (active or not) from the specified Excel file.

For our examples, we have the following worksheets in our “test_Excel.xlsx” workbook:

Pandas-sheets-sample

Python program below loads the default sheet (one sheet) only by using read_excel method.

Code:

Output:

Pandas-one-sheet-load

Loading two Sheets by sheet_name parameter example

As we know, read_excel method has many parameters and for the purpose of loading two sheets, our solution is using sheet_name.

By using sheet_name parameter, you may specify one or more sheets in the Workbook.

You may provide the sheet names as follows:

  • sheet_name=[0,1,2…]
  • sheet_name=[‘0,’Product Information’]
  • sheet_name=[‘Sales’,’Product Information’]

So, the index number of the sheet starting from 0 can be given. Also, you may also use sheet names or mix of both as well.

We will use the third way of loading two worksheets and display their data in the example below.

Python program:

Output:

Pandas-multiple-sheets

An important note: If sheet contains charts then it is not counted in the indexed-based position. We will show you in the example later.

Using sheet number and name example

Now we used sheet number (index starting at zero) and name in the example below to load two sheets from our workbook and then display data:

Output:

Pandas-excel-index

The index position 2 represents the “Employee” sheet in our sample Workbook.

Loading all sheets example

For loading all sheets from the Workbook, just specify sheet_name = None:

Output:

Pandas-excel-all-sheet

In our sample sheets, you can see we have three sheets and all are loaded in the same sequence.

As we also specified the index_col parameter, so this time row number (or column index) is not displayed.

What if we have a sheet with charts?

For this example, we are adding another sheet in between the above sample sheets. It contains a chart for the demo only:

Pandas-excel-charts

The date for the chart comes from “Sales” sheet.

As we fetch a program to get all sheets by Pandas read_excel, this is what we get the output:

Output:

Pandas-excel-charts-re